topic Re: Multiple SQL Statements in SQL Template => Duplicate local variable in Talend Studio

Multiple SQL Statements in SQL Template => Duplicate local variable

McDon — Fri, 15 Nov 2024 23:31:52 GMT

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.

Re: Multiple SQL Statements in SQL Template => Duplicate local variable

Anonymous — Fri, 12 Nov 2021 08:41:29 GMT

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

Re: Multiple SQL Statements in SQL Template => Duplicate local variable

McDon — Fri, 12 Nov 2021 12:22:15 GMT

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.

Re: Multiple SQL Statements in SQL Template => Duplicate local variable

Anonymous — Mon, 15 Nov 2021 03:48:36 GMT

Hello,

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

Best regards

Sabrina

Re: Multiple SQL Statements in SQL Template => Duplicate local variable

McDon — Mon, 15 Nov 2021 11:03:38 GMT

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

Re: Multiple SQL Statements in SQL Template => Duplicate local variable

Anonymous — Tue, 16 Nov 2021 04:19:18 GMT

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

Re: Multiple SQL Statements in SQL Template => Duplicate local variable

McDon — Tue, 25 Jan 2022 03:03:28 GMT

Talend fixed this in the Dec 2021 patch.

I have confirmed it

Re: Multiple SQL Statements in SQL Template => Duplicate local variable

Anonymous — Tue, 25 Jan 2022 06:38:43 GMT

Hello,

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

Best regards

Sabrina