topic Re: How to run Multiple Teradata SQL Queries from a single component in Talend Studio

How to run Multiple Teradata SQL Queries from a single component

Anonymous — Wed, 02 Jul 2014 20:20:50 GMT

Hi
I have a specific requirement to run multiple sql queries from a tTeradataRow or tTeradataInput component.
everytime I run I get following exception.
Only an ET or null statement is legal after a DDL Statement.
I tried putting ETBT statements nothing worked.
I read there is a way of dealing this issue is stored procedure. But tTeradata has no SP component like oracle. Can any one please help me in this.
Thanks,
Amit

Re: How to run Multiple Teradata SQL Queries from a single component

Anonymous — Thu, 03 Jul 2014 04:28:03 GMT

Hi,
Have you already checked document TalendHelpCenter:Executing multiple SQL statements with one tMysqlRow component?
Best regards
Sabrina

Re: How to run Multiple Teradata SQL Queries from a single component

Anonymous — Thu, 03 Jul 2014 16:14:35 GMT

Thanks Sabrina,
But that does not work with teradata I am still getting the ET BT error message
for now I am just creating a table and then I am collecting stat on the fields of the newly created tables.
I checked JDBC Parameters for terdata I could not find any multi-statement or multi-query...
Thanks,
Amit

Re: How to run Multiple Teradata SQL Queries from a single component

Anonymous — Fri, 04 Jul 2014 09:06:02 GMT

Hi,
So far, there is no tTeradataSP component. You can open a feature jira issue on Talend Bug Tracker, our component developer will check it.
Best regards
Sabrina

Re: How to run Multiple Teradata SQL Queries from a single component

Anonymous — Mon, 01 Oct 2018 17:10:33 GMT

Hello,

I know this thread is a bit old, but wanted to give some updates on the same issue we are facing and our solution. We spent days researching and trying many different things, such as adding BT ET statements, changing JDBC parameters in our Teradata connection, changing auto-commit settings, etc. All with no luck!

Someone on my team opened a ticket in the Talend Bug Tracker and we were told that this is not a Talend bug, but rather a limitation with the Teradata JDBC driver (see ticket TDI-40898).

Here are our findings:

You CAN NOT execute multiple DDL queries (ex: create table, collect stats, drop table) within a single component.
You CAN execute multiple DML queries (ex: Delete, insert, update) within a single component.
You CAN NOT execute a mix of DDL and DML queries within a single component.

We have a requirement to execute a large numbers queries consecutively in our jobs, so breaking up our queries into separate tTeradataRow components to run 1 by 1 was very cumbersome and will not work for us.

I have therefore gotten creative and coded my own solution to this issue, which I hope others can copy and benefit from, as I found this very frustrating!

Here is how my solution works:

Step 1:
Query File: Our set of SQL queries is stored in a text file in a shared folder. All queries end with a semicolon in the file (except for the last query in the file). I read in the file using a tFileInputRaw component which will store the entire contents of the file as a long text string into a variable in the job. The variable that will store the string by default is row1.content.

Step 2:
Parse Queries: Next, I added a tJavaRow component with some custom Java code. You can see what the code does in my comments in the code.

Steps 3 - 4.
Loop: Loop through the variables and execute the teradata command for each one, passing in the query text of the current iteration into the tTeradataRow component.

And there you have it!

This has proven to work very well for us. It is a shame that I had to jump through hoops and take matters into my own hands to custom code this basic functionality. Although we were told that this is a Teradata JDBC driver limitation and not a Talend bug, I don't see why Talend wouldn't program it to work behind the scenes as I have programmed above.

Hope someone else finds this helpful!

Re: How to run Multiple Teradata SQL Queries from a single component

PranayVatsal — Fri, 26 Oct 2018 20:44:19 GMT

Thanks @ris.tan. it worked.

Re: How to run Multiple Teradata SQL Queries from a single component

Anonymous — Thu, 05 Sep 2019 10:20:25 GMT

Thanks @ris.tan - your guide has been very helpful and performed as expected.

quick simple question -

how can I print the query that is being transmitted from the tLoop to tDBRow?
for example, tLoop is passing the query that is in the first position in the array to tDBrow,
I would like at this point to print: "Currently executing...[and print the query here]"

thank you so much in advance!