Multiple SQL Statements in SQL Template => Duplicate local variable

[McDon]

I am using Talend Studio

Talend Cloud Data Management Platform

Version: 7.3.1

Build id: 20210818_1049-patch

I'm having trouble adding multiple SQL statements to a SQL Template.

The code I'm trying to write was generating Java errors. So I have resorted to your example code to see if it is me.

The sample code in your

documentation

produces the same scenario, so I will use that to ask for help on this problem.

The error I get is:

Duplicate local variable tempSQLSentence_tSQLTemplate_1_1

The Java code that is generated is

 

As you can see, the variable "tempSQLSentence_tSQLTemplate_1_1" is being declared 3 times in this code block.

Possibly the error is simply that the variable is being declared each time, when it should be declared only the first time.

Alternatively, the variable suffix number must be incremented.

I've kept the example flow very simple so we may focus on the actual issue. 

However, I have the same problem is more complex flows.

The SQL Template code I'm using is from your documentation.

[Anonymous]

Hello,

There seems to be an issue when there are multiple variable instances with the same name.

Does it work with single statement?

#sql sentence

DROP TABLE temp_<%=__TABLE_NAME__ %>;

Best regards

Sabrina

[McDon]

It does work with a single statement. But only because of the variable name increment. It is also worth noting I noticed something else. When using multiple SQL Templates, each with 1 sql statement, inside 1 component; The variable name that is shown in my code increments - i think this is where the bug is. _1_1 _1_2 _1_3 To me it would seem like the first number should increment for each different SQL template inside the component. Then within each SQL template, the second number should increment.

[Anonymous]

Hello,

SQL templates are definitely made for ELT. Are you performing ELT on your Snowflake?

Best regards

Sabrina

[McDon]

Yes. Per my flow, I, using an ELT component. Is that what you were asking me?

[Anonymous]

Hello,

There raises a support case 00242656 about:Multiple SQL Statements in SQL Template => Duplicate local variable

Our support engineers are making an investigation on it and then come back to you as soon as possible.

Best regards

Sabrina

[McDon · Accepted solution]

Talend fixed this in the Dec 2021 patch.

I have confirmed it

[Anonymous]

Hello,

Thanks for your feedback and let us know this fixed patch.

Best regards

Sabrina