Select statement with a variable in the where clause.
I want to compare data coming from an XML file to data in a DB.
I do not want preload all the records from the DB as there could be several million.
So I would like to set the job up as the attached image shows.
A records ISBN number and other values are taken from the XML file.
The ISBN is sent to the tMysqlRow where is is used in the where clause of an SQL statement.
The values selected are sent to the tJavaRow component where they are compared to values coming directly from the tXMLMap.
I asume that there should be an input schema and an output schema in the tMysqlRow (input ISBN and output the values selected) but it is not there.
I am unable to link the tMysqlRow to the tJavaRow (arrow drawn in red).
Is Talend able to do this?
Thanks
I don't think Talend will let you split and rejoin the output from a map component. I'm not sure of the technical reasons why this is, but as you've seen you cannot make the connection from the MySQL component to the tJava.
If I understand your requirements correctly, I think you will need to do another read of the input XML file, just taking the ISBN number and passing this through to the MySQL component. Then join the output from the first read with the output from the second read. See screenshot.
JB
tMysqlRow is not intended to return data but execute a SQL action for each incoming row; the outgoing row will be identical to the incoming row.
A better approach is tFileInputXML --> tXMLMap --main--> tMap -->
tmySqlInput --lookup-->
In the tMap, change the lookup model to "Reload at each row (cached)" and use the globalMap key in the tmySqlInput SQL. Read scenario 6 of the tMap help for a detailed example of how this works.
Maybe alevy is right re: tMysqlRow is not intended to return data
Anyway, I intended to use a tMySQLInput, just not thinking :-).
Here's something that might do the trick. Think this is the same as janhess is suggesting.
I'm getting the IDs from the XML file, denormalizing into a comma separated string, then setting a globarVar to this value.
Then, set the SQL query in the tMySQLInput as something like:
"select id, order_date from orders where id in ("+((String)globalMap.get("K1"))+")"
Where K1 is the globalVar.
This seems to work OK, although you might not want to use this if you had very large numbers of IDs returned from the XML file. There's probably better ways to this if that's the case.
alevy wrote: tMysqlRow is not intended to return data but execute a SQL action for each incoming row; the outgoing row will be identical to the incoming row.
That is just plain wrong, there is even an option to propagate the query's record set in the t***Row components.
True but the option to propagate the query's record set is intended more to return the results of stored procedures; it requires an existing Object column in the incoming schema to t***Row that will be populated with the record set and then the actual return value desired needs to be extracted using tParseRecordSet.
I stand by my previous suggestion that tMap lookup each row is the easiest way to go.
tMysqlRow is not intended to return data but execute a SQL action for each incoming row; the outgoing row will be identical to the incoming row. A better approach is tFileInputXML --> tXMLMap --main--> tMap --> tmySqlInput --lookup--> In the tMap, change the lookup model to "Reload at each row (cached)" and use the globalMap key in the tmySqlInput SQL. Read scenario 6 of the tMap help for a detailed example of how this works.
Thanks for pointing to Scenario 6. It was very helpful.
