topic Re: Iterating through values in table for single Context Variable in Talend Studio

Iterating through values in table for single Context Variable

Anonymous — Sat, 25 Mar 2017 20:18:48 GMT

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!

Re: Iterating through values in table for single Context Variable

Anonymous — Sun, 26 Mar 2017 04:09:52 GMT

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.

Re: Iterating through values in table for single Context Variable

Anonymous — Sun, 26 Mar 2017 22:04:49 GMT

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.

Re: Iterating through values in table for single Context Variable

Anonymous — Sun, 26 Mar 2017 22:12:08 GMT

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 🙂