Dynamic SQL Generation

One Star

Dynamic SQL Generation

I have some SQL that looks like this:
SELECT
raw_data_long._Board_Name,
raw_data_long.School_Name,
raw_data_long.Question_No40,
raw_data_long.CT_Ans_1,
raw_data_long.CT_Ans_2,
raw_data_long.CT_Ans_3a,
raw_data_long.CT_Ans_3b,
raw_data_long.CT_Ans_3c,
raw_data_long.CT_Ans_4,
raw_data_long.CT_Ans_5,
raw_data_long.CT_Ans_6,
raw_data_long.CT_Ans_7,
raw_data_long.CT_Ans_8,
raw_data_long.CT_Ans_9,
raw_data_long.Question_No41,
raw_data_long.CT_Ans_11,
raw_data_long.CT_Ans_21,
raw_data_long.CT_Ans_3a1,
raw_data_long.CT_Ans_3b1,
raw_data_long.CT_Ans_3c1,
raw_data_long.CT_Ans_41,
raw_data_long.CT_Ans_51,
raw_data_long.CT_Ans_61,
raw_data_long.CT_Ans_71,
raw_data_long.CT_Ans_81,
raw_data_long.CT_Ans_91,
... It goes up to Question 50 following this pattern.
I would like to figure out a way to dynamically generate this in a DB Input Component. I have been playing with nesting ForEach components, but am not really getting the result I want (currently playing around with something like this):
String foo = "select raw_data_long._Board_Name, " +
" raw_data_long.School_Name, "
+ " raw_data_long.Question_No" + (String)globalMap.get("tForeach_1_CURRENT_VALUE")
+ ", raw_data_long.CT_Ans_" + (String)globalMap.get("tForeach_2_CURRENT_VALUE") + (String)globalMap.get("tForeach_3_CURRENT_VALUE")
+ ", ";
My current result looks like:
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Question_No40, raw_data_long.CT_Ans_1,
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Question_No40, raw_data_long.CT_Ans_11,
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Question_No40, raw_data_long.CT_Ans_12,
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Question_No40, raw_data_long.CT_Ans_2,
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Question_No40, raw_data_long.CT_Ans_21,
....
Ideally I would like the first foreach to run, then iterate through all the 2nd and 3rd foreach values rather than execute once for each loop. Can loops be configured in this way?
Is there a better idea on how to do this?
Thanks,
Luke
One Star

Re: Dynamic SQL Generation

Hi Luke
Create job as the following images.
Code in tJava
String tforeach_3 = "";
String temp =((String)globalMap.get("tForeach_1_CURRENT_VALUE"));
if
(!temp.equals("40")){
tforeach_3 =(Integer.valueOf(temp)-40)+"";
}
if(!temp.equals(context.temp))
context.result += " ,raw_data_long.Question_No" + temp
+ ", raw_data_long.CT_Ans_" + (String)globalMap.get("tForeach_2_CURRENT_VALUE") + tforeach_3;
else
context.result += " ,raw_data_long.CT_Ans_" + (String)globalMap.get("tForeach_2_CURRENT_VALUE") + tforeach_3 ;
context.temp = temp;
System.out.println(context.result);
System.out.println("");

Regards,
Pedro
One Star

Re: Dynamic SQL Generation

Perfect! Thanks for this! A lot of good tricks and techniques in this answer along with the solution to my issue. I love this tool :-)
One Star

Re: Dynamic SQL Generation

One last question on this. Is there a way to run this job to completion and then use the SQL that is built by it? Basically I want to use this SQL in a mySQL input component. Would this be a good place for a joblet?
One Star

Re: Dynamic SQL Generation

Hi
Yes. If you want to use tMysqlInput, type context.result in the query of it.
Regards,
Pedro
One Star

Re: Dynamic SQL Generation

Hello. So I have set up all my logic to generate a single SQL query and then run it through some components to execute it, transform the data and then update the DB. The issue I have having is my dynamic SQL look like this:
select raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Reg__Date ,raw_data_long.Question_No40 as Question_No, raw_data_long.CT_Ans_1 as CT_Ans_1 ,raw_data_long.CT_Ans_2 as CT_Ans_2 ,raw_data_long.CT_Ans_3a as CT_Ans_3a ,raw_data_long.CT_Ans_3b as CT_Ans_3b ,raw_data_long.CT_Ans_3c as CT_Ans_3c ,raw_data_long.CT_Ans_4 as CT_Ans_4 ,raw_data_long.CT_Ans_5 as CT_Ans_5 ,raw_data_long.CT_Ans_6 as CT_Ans_6 ,raw_data_long.CT_Ans_7 as CT_Ans_7 ,raw_data_long.CT_Ans_8 as CT_Ans_8 ,raw_data_long.CT_Ans_9 as CT_Ans_9 FROM raw_data_long
When I run the job I get this error:
Exception in component tMysqlInput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'raw_data_long' in field list
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
But if I don't use the dynamic SQL (context.result) and instead paste the SQL that context.result contains and run it, everything works fine. What is the difference between running the Input Component with contex.result as the query, or by putting what context.result contains?
Thanks,
Luke
One Star

Re: Dynamic SQL Generation

I tried removing the table name and I get the following value in context.result before I run it in the Input:
select _Board_Name, School_Name, Reg__Date , Question_No40 as Question_No, CT_Ans_1 as CT_Ans_1 ,CT_Ans_2 as CT_Ans_2 ,CT_Ans_3a as CT_Ans_3a ,CT_Ans_3b as CT_Ans_3b ,CT_Ans_3c as CT_Ans_3c ,CT_Ans_4 as CT_Ans_4 ,CT_Ans_5 as CT_Ans_5 ,CT_Ans_6 as CT_Ans_6 ,CT_Ans_7 as CT_Ans_7 ,CT_Ans_8 as CT_Ans_8 ,CT_Ans_9 as CT_Ans_9 FROM raw_data_long
But the result is:
Exception in component tMysqlInput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '_Board_Name' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
If I run the query in the DB manually, it works fine. Not sure whats doing on here.
Luke
One Star

Re: Dynamic SQL Generation

OK, I fixed it. Not sure why, but if I appended the table name in the component, it works fine.
context.result + " from raw_data_long"
One Star

Re: Dynamic SQL Generation

Actually I am still getting this issue.
context.result contains the following before the mySQL input that uses it:
select _Board_Name, School_Name, Reg__Date , Question_No40 as Question_No, CT_Ans_1 as CT_Ans_1 ,CT_Ans_2 as CT_Ans_2 ,CT_Ans_3a as CT_Ans_3a ,CT_Ans_3b as CT_Ans_3b ,CT_Ans_3c as CT_Ans_3c ,CT_Ans_4 as CT_Ans_4 ,CT_Ans_5 as CT_Ans_5 ,CT_Ans_6 as CT_Ans_6 ,CT_Ans_7 as CT_Ans_7 ,CT_Ans_8 as CT_Ans_8 ,CT_Ans_9 as CT_Ans_9 FROM raw_data_long
When I run this report against the DB manually I get results back.
But when the component runs it in the job I get the following error:
select _Board_Name, School_Name, Reg__Date , Question_No40 as Question_No, CT_Ans_1 as CT_Ans_1 ,CT_Ans_2 as CT_Ans_2 ,CT_Ans_3a as CT_Ans_3a ,CT_Ans_3b as CT_Ans_3b ,CT_Ans_3c as CT_Ans_3c ,CT_Ans_4 as CT_Ans_4 ,CT_Ans_5 as CT_Ans_5 ,CT_Ans_6 as CT_Ans_6 ,CT_Ans_7 as CT_Ans_7 ,CT_Ans_8 as CT_Ans_8 ,CT_Ans_9 as CT_Ans_9 FROM raw_data_long
Any ideas as to why this would happen?
Luke
One Star

Re: Dynamic SQL Generation

When I add the table name my context.variable has the following SQL in it:
SELECT raw_data_long._Board_Name, raw_data_long.School_Name, raw_data_long.Reg__Date,raw_data_long.Question_No40 as Question_No, CT_Ans_1 as CT_Ans_1 ,raw_data_long.CT_Ans_2 as CT_Ans_2 ,raw_data_long.CT_Ans_3a as CT_Ans_3a ,raw_data_long.CT_Ans_3b as CT_Ans_3b ,raw_data_long.CT_Ans_3c as CT_Ans_3c ,raw_data_long.CT_Ans_4 as CT_Ans_4 ,raw_data_long.CT_Ans_5 as CT_Ans_5 ,raw_data_long.CT_Ans_6 as CT_Ans_6 ,raw_data_long.CT_Ans_7 as CT_Ans_7 ,raw_data_long.CT_Ans_8 as CT_Ans_8 ,raw_data_long.CT_Ans_9 as CT_Ans_9 FROM raw_data_long
When the job runs I get:
Exception in component tMysqlInput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'raw_data_long' in field list
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
Not only does this SQL work in the DB directly, but if I put this into the mySQLInput query it works. However when I put the context variable in the query box, I get error. I can't see what the difference is,
One Star

Re: Dynamic SQL Generation

Hi
Try to add ' ' around table name.
FROM 'raw_data_long'

Regards,
Pedro
One Star

Re: Dynamic SQL Generation

That didn't help.
What did help was making the query look like this:
"select " + context.result + " from raw_data_long"
I don't get errors any more, but the columns are returning null.
One Star

Re: Dynamic SQL Generation

Hi
Please send me an email and attach this export job.
I will test it for you. Because it works fine at my local machine.
Regards,
Pedro
One Star

Re: Dynamic SQL Generation

Email sent. I might have fixed it by doing all the SQL manipulation in the tJava components. But let me know what you think, I will post all the results here for anyone else with similar issues.
Thanks!
Luke
One Star

Re: Dynamic SQL Generation

Hello all,
I have a t_MSSQLInput_5 Name: JournalNew Component that contains a query, this query shall be replaced by a dynamic query at job execution.
I generate a SQL Statement in a t_Java component. (see code below)
The statement gets stored in a Global Variable tMSSqlInput_5_QUERY. To control it, the variable tMSSqlInput_5_QUERY is used as output in a tMsgBox. EVERYTHING OK, the variable tMSSqlInput_5_QUERY contains the new dynamic query
But tMSSqlInput_5 executes the original (old) query instead the new dynamic one
Now my problem: I do not manage to feed my t_MSSQLInput_5 Name: JournalNew Component with this new generated query.
Please explain. How to replace an existing old query with the dynamic generated query.
I would like to do that in the tJava component.here the existing code
String buff = ((String)globalMap.get("Journal_MaxID"));
String buff2 = "SELECT  dbo.journal.TabIndex,        dbo.journal.ConnectionID FROM dbo.journal WHERE  dbo.journal.TabIndex > ";
String buff3 =  buff2 + buff;
globalMap.put("tMSSqlInput_5_QUERY",(String)buff3);