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

[jensej]

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.                                                                            
 

[Anonymous · Accepted solution]

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. 

[jensej]

@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.

                                   

[Anonymous]

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.

[Anonymous]

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).

[jensej]

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

[Anonymous]

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.

[jensej]

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

[Anonymous]

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

[jensej]

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