topic Re: [resolved] Pass parameters to SQL query and make query for each row. in Talend Studio

[resolved] Pass parameters to SQL query and make query for each row.

jensej — Wed, 24 Aug 2016 09:36:04 GMT

Hello There
Step 1.
I have a .txt file that Looks like this

10557;7311;2
10007;7311;2
10013;7311;4
10498;7311;2
10593;7311;4

Step 2.
From the .txt i want to fill this contexts.

Step 3.
Now i want to use the contexts in my SQL query like this.

WHERE ag.ArtikelNrLAG = context.par_Artikel and a.AdressNrADR = context.par_AdressNrADR

Step 4. My Problem is that i Need to make so many queries as i have rows in the text file and i dont know i how i can send row after row to my SQL. In this example i have 5 rows in my text file so i have to read the first row, fill the context and run the SQL. After that second row and so on.

I hope you understand what im trying to reach.

Re: [resolved] Pass parameters to SQL query and make query for each row.

Anonymous — Wed, 24 Aug 2016 09:51:55 GMT

Don't use context variables, use globalMap variables. Connect your tFileInputDelimited to a tFlowToIterate component. This will create your globalMap variables for you.
Lets say you have 3 columns called column1 (String), column2 (Integer) and column3 (String) from your file. If the "row" joining the two components is called "row1", then the names of the variables will be "row1.column1", "row1.column2" and "row1.column3". You would access them using the following code...
((String)globalMap.get("row1.column1"))
((Integer)globalMap.get("row1.column2"))
((String)globalMap.get("row1.column3"))
.....the types are important when using the variables.
Then all you need to do is connect your DB component to the tFlowToIterate using an "Iterate" link and use the variables above in your query.

Re: [resolved] Pass parameters to SQL query and make query for each row.

jensej — Wed, 24 Aug 2016 10:50:27 GMT

@rhall_2.0 Thanks for your tip
I hope i understand it everything correct.
This is how it Looks like now but i get one error when i try to run the Job.

Re: [resolved] Pass parameters to SQL query and make query for each row.

Anonymous — Wed, 24 Aug 2016 12:11:28 GMT

I should have said that when you use your globalMap variables in your SQL, you will need to append the value to the SQL String and not just refer to the variables. So for example....
"SELECT column1, column2, column3 FROM table"
....would be converted to below when adding filtering using a globalMap variable....
"SELECT column1, column2, column3 FROM table WHERE column1 = '" + ((String)globalMap.get("row1.column1")) + "'"
Notice how I am appending the value of the variable? I am closing the String just before it (....column1 = '"), then using a + symbol to concatenate that String with the new String. The "'" at the end is because String values in Oracle (for example) need to be enclose in single quotes. So when you are preparing your SQL String you need to keep in mind the formatting required by your DB. You will notice that there is a single quote before I append the globalMap variable.

Re: [resolved] Pass parameters to SQL query and make query for each row.

Anonymous — Wed, 24 Aug 2016 12:13:17 GMT

By the way, you don't need to use the tSetGlobalMap. The tFlowToIterate does that for in the way I described (eg "row1.column1" etc).

Re: [resolved] Pass parameters to SQL query and make query for each row.

jensej — Wed, 24 Aug 2016 15:15:29 GMT

I don't know what i do wrong, it's not working. I get 0 rows back.. i still think it's something wrong in the SQL query

Re: [resolved] Pass parameters to SQL query and make query for each row.

Anonymous — Wed, 24 Aug 2016 15:30:29 GMT

You are using "row1.column1" as your globalMap variable name. Are you sure this is correct? Remember, it is the name of the row (which looks like it is "row1" and the name of the column. Also, you do not need the tSetGlobalMap.

Re: [resolved] Pass parameters to SQL query and make query for each row.

jensej — Wed, 24 Aug 2016 15:38:53 GMT

Like this? Still doesnt give me any rows back..

Re: [resolved] Pass parameters to SQL query and make query for each row.

Anonymous — Wed, 24 Aug 2016 15:43:12 GMT

Click on the "row1" connector and show me the component tab. I think your globalMap variables are named incorrectly.

Re: [resolved] Pass parameters to SQL query and make query for each row.

jensej — Wed, 24 Aug 2016 15:45:36 GMT

Now it worked! I had to Change column1 to Column1. Thanks for your help!

Re: [resolved] Pass parameters to SQL query and make query for each row.

Anonymous — Wed, 24 Aug 2016 15:48:31 GMT

The variable names are case sensitive. Your column names start with a capital letter, but your globalMap variable names are lower case. Change the variable names to match exactly.
EDIT: I see you got there first 🙂

Re: [resolved] Pass parameters to SQL query and make query for each row.

Ankitamalhotra — Fri, 05 Jun 2020 20:13:23 GMT

Hi
I have the same requirement.
But while performing the same steps I am getting the syntax error.
Please help and could you please give some help on incremental load and also elaborate on using context variables and global variables, declaring them and using them in sql queries.
Thanks