topic Re: Dynamic SQL for deleting rows in Talend Studio

Dynamic SQL for deleting rows

HeinrichL — Thu, 18 Nov 2021 13:56:57 GMT

Hello everyone,

I'm struggeling with designing a dynamic SQL for deleting rows.

I have a tOracleInput component for selecting the rows I want to delete. My select is:

"select distinct DEL_ID,DEL_TAB,DEL_FIELD from <..> where DEL_KZ Is NUll order by DEL_TAB,DEL_FIELD,DEL_ID;"

For deleting I wanted to use TOracleRow with the delete statement:

"delete from input_row.del_tab where input_row.del_field = input_row.DEL_ID;"

I connected both components with iterate.

Del_Tab and Del_Field can be different so I want it dynamical. Do i have to use other components oder other connections? Should I save del_tab & del_field in a context variable? By starting the job I get the exception:

ORA-00933: SQL command not properly ended

Thx for helping!

Re: Dynamic SQL for deleting rows

Anonymous — Fri, 19 Nov 2021 07:24:21 GMT

Hello,

You could set context variables for that job in tOracleRow component.

Try this syntax:

"DELETE FROM "+context.del_tab WHERE Del_Field = "+context.yourvariable

Let us know if it helps.

Best regards

Sabrina

Re: Dynamic SQL for deleting rows

HeinrichL — Fri, 19 Nov 2021 08:01:22 GMT

But in which component do I set these context variables? Do I have to use a tJavaRow after the tOracleInput with: contex.<..> = input_row.<..> and how do I have to connect these 3 components that it'll work afterwards? With tJavaRow I can't use the iterate-connection.

Thx for your answer!

Lisa

Re: Dynamic SQL for deleting rows

Anonymous — Fri, 19 Nov 2021 09:53:03 GMT

Hello,

I think there are a few problems with this query that you're not aware of:

"delete from input_row.del_tab where input_row.del_field = input_row.DEL_ID;"

input_row is supported for tJavaRow component.

It should look more like:

"delete from " + row1.del_tab + " where " + row1.del_field +" = " + row1.DEL_ID+ ";"

This will work OK for a few records.

For millions of records you probably want to use the "Use PreparedStatement" option under advanced settings.

This you would require to group the tables + fields, and introduce tFlowToIterate .

"select distinct DEL_TAB,DEL_FIELD from <..> where DEL_KZ Is NUll " ->tFlowToIterate

Your current input would become:

"select distinct DEL_ID,DEL_TAB,DEL_FIELD from <..> where DEL_KZ Is NUll AND WHERE DEL_TAB = '" + ((String)globalMap.get("table")) + "' AND DEL_FIELD = '" + ((String)globalMap.get("field")) +"' order by DEL_TAB,DEL_FIELD,DEL_ID;"

Then your query in the Row could become:

"delete from " + ((String)globalMap.get("table"))+ " where " + ((String)globalMap.get("field")) +" = ? ;"

Here the parameters will be passed under Advanced Settings:

This would make the query execution faster but the statement will be prepared at the beginning of the subjob so you need iteration / loops.

Re: Dynamic SQL for deleting rows

HeinrichL — Fri, 19 Nov 2021 10:50:22 GMT

Thx for the detailed answer! I try that later and let u know if it worked.

Re: Dynamic SQL for deleting rows

HeinrichL — Mon, 22 Nov 2021 10:59:08 GMT

What do you mean by saying I need iteration/loops?

I configured my components like you said.

Here is the input:

Here is the tDBRow:

For this I added a parameter:

But I get an error:

java.sql.sqlsyntaxerrorexception ora-00933 sql command not properly ended

Can you help me with that @Balazs Gunics ?