SQL Template usage

One Star

SQL Template usage

Hello,
I am using Talend Enterprise Big Data 5.4.1, but I think this question is related to the Studio in general.
I am trying to use SQL Templates and I am having troubles with templates containing multiple statements.
You can see on the screenshot an example where I tried to use a template from the documentation (Talend Studio user guide, Appendix C).
It causes following exception during job generation: Duplicate local variable tempSQLSentence_tSQLTemplate_1_1.
If I look at the generated code, it generates string variables containing the statements, but all are named the same for all statements in one template.
Is this a bug or am I doing something wrong (maybe wrong tSQLTemplate* component)?
In documentation, it is explicitely stated that a template can contain multiple statements.
As a second question, I am curious - in case the multiple statements in one template work - if it is possible to use SQL Templates as a method of executing "any" SQL script (put it into SQL template, then execute via the tSQLTemplate component) - e.g. without using any SQL Template variables etc.
Thanks
Jan
One Star

Re: SQL Template usage

Sorry, I was unable to post the attachement.
I am using:
-------------------------
tNetezzaConnection_1 --> tSQLTemplate_1 --> tNetezzaClose_1
-------------------------
The tSQLTemplate_1 contains one template named "test" with the following content:
-------------------------
#sql sentence
DROP TABLE temp_0;
<%
#loop
for(int i=1; i<10; i++){
%>
#sql sentence
DROP TABLE temp_<%=i %>;
<%
}
%>
#sql sentence
DROP TABLE temp_10;
-------------------------
The generated code causes error because of this (multiple occurrences of tempSQLSentence_tSQLTemplate_1_1):
-------------------------
String tempSQLSentence_tSQLTemplate_1_1 = "DROP TABLE temp_0";
globalMap.put("tSQLTemplate_1_QUERY",
tempSQLSentence_tSQLTemplate_1_1);
stmt_tSQLTemplate_1.addBatch(tempSQLSentence_tSQLTemplate_1_1);
for (int i = 1; i < 10; i++) {
String tempSQLSentence_tSQLTemplate_1_1 = "DROP TABLE temp_"
+ i + "";
globalMap.put("tSQLTemplate_1_QUERY",
tempSQLSentence_tSQLTemplate_1_1);
stmt_tSQLTemplate_1
.addBatch(tempSQLSentence_tSQLTemplate_1_1);
}
String tempSQLSentence_tSQLTemplate_1_1 = "DROP TABLE temp_10";
-------------------------
Six Stars

Re: SQL Template usage

Hi jan.fabian,
did you find solution to the multiple variable instances with the same name?
I have the same error: "Duplicate local variable tempSQLSentence_tSQLTemplate_1_1"
Thank you,
Lorenzo