Iterating through values in table for single Context Variable

Highlighted
One Star

Iterating through values in table for single Context Variable

Hi there, I'm quite new to Talend and have struggled a bit in finding a good approach for this. 
My use case is I'd like to iterate over a table of database names to set a context.database variable, so that when a tMySQLInput component runs it will run for `databaseA` then shoots data to a common target, then the database variable would be overwritten by the next in the list `databaseB` and so on.
I've experimented a bit with the tContextLoad and am able to get that to set a database name for a single entry - but am wondering how to iterate over multiple rows of the same key values from a table source. Not seeing a good way to get this to accept an iterated value. 
My source table for setting the variables would look like this: 
|key|value| 
|database|databaseA|
|database|databaseB|
Thanks for any help!
Sixteen Stars

Re: Iterating through values in table for single Context Variable

This is pretty straight forward. First of all, build a standalone job that will use context variables to set your database connection, carry out the query and load the data. You probably have this given your description. 
Next build another job which will query your db table with your db credentials. Connect a tMySQLInput (with the query) to a tFlowToIterate. Then drag your previous job into this new job. Connect your tFlowToIterate to that job using an "iterate" connector.
Now, if your row connecting your tMySQLInput to the tFlowToIterate is called "row1" and your column names are "dbname" and "dbpassword" (for example), you need to pass those to the job carrying out the work. In that job you should have created some context variables that correspond to those values being passed in. Go to the properties of the child job and set the context variables of the child job to the values returned by your query. To do this you use globalMap variables. These are created by the tFlowToIterate component. given the details of this example, your dbname value can be passed using the code....
((String)globalMap.get("row1.dbname"))
...and....
((String)globalMap.get("row1.dbpassword"))
An example of using tRunJob components (child jobs) can be found here (https://help.talend.com/search/all?query=tRunJob&content-lang=en).
In this example the inner job will be run for every row returned by the query returning db details.
One Star

Re: Iterating through values in table for single Context Variable

Thanks! I actually came to the same solution yesterday. Didn't realize the tFlowtoIterate component's purpose was setting global variables. Again, pretty new to this as I come from SSIS world so a lot to learn. Smiley Happy 
Sixteen Stars

Re: Iterating through values in table for single Context Variable

The tFlowToIterate is not really meant to set globalMap variables. It is meant to take a flow and iterate each row enabling the following subjob to run completely for each row. However, it achieves this by setting the row to globalMap variables. A useful trick to remember :-)