One Star

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

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.                                                                            
 
1 ACCEPTED SOLUTION

Accepted Solutions
Fifteen Stars

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

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. 
Rilhia Solutions
10 REPLIES
Fifteen Stars

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

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. 
Rilhia Solutions
One Star

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

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

                                   
Fifteen Stars

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

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.
Rilhia Solutions
Fifteen Stars

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

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).
Rilhia Solutions
One Star

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

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
Fifteen Stars

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

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.
Rilhia Solutions
One Star

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


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

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

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

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


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

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

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 :-)
Rilhia Solutions