topic Re: [resolved] Component to delete in DB. in Talend Studio

[resolved] Component to delete in DB.

Anonymous — Tue, 30 Dec 2014 19:24:29 GMT

I'm sure that is very easy question, but I don't know what component is needed to delete in a table.
I'm trying with tPostgreSqlInput's component, and in the query:
delete from TABLE where TO_CHAR ( DATE, 'YYYYMM' ) = "+globalMap.get("period").toString()
But apperance this:
Exception in component tPostgresqlInput_2
org.postgresql.util.PSQLException: The query did not return any results.
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:236)
    at mdc.omel_0_1.omel.tPostgresqlInput_2Process(omel.java:1023)
    at mdc.omel_0_1.omel.tFileList_1Process(omel.java:885)
    at mdc.omel_0_1.omel.runJobInTOS(omel.java:3902)
    at mdc.omel_0_1.omel.main(omel.java:3684)
How I can delete in a table?
Thank you.

Re: [resolved] Component to delete in DB.

Anonymous — Wed, 31 Dec 2014 03:17:37 GMT

Hi,

where TO_CHAR ( DATE, 'YYYYMM' ) = "+globalMap.get("period").toString()

Did you put any value to a global variable in job? How can you get your period global variable? Could you please give us more information and description about your currrent job?

Best regards
Sabrina

Re: [resolved] Component to delete in DB.

Anonymous — Fri, 02 Jan 2015 07:27:27 GMT

Hi, I have this job, I get a value from filename and use for the query(delete), then I process the file:

I get my variableGlobal in a tJava, for use it in the query in the tPostgreSQLInput component like this:

The query is working, I can delete in the table, and I process the file but at finish I get an error in console.
Thank you so much.

Re: [resolved] Component to delete in DB.

Anonymous — Sat, 03 Jan 2015 11:55:02 GMT

The input component is not the right tool to execute update/insert/delete.
You can do this with the output components. Actually you do not have to write the SQL for your self. In the tPostgresqlOutput you can set as action on data Delete. This flow to this component must have key columns (all others will be ignored) and the component deletes as expected. I suggest for a single delete use this design:
tFixedFlowInput --> tPostgresqlOutput
(set here the key values)

Re: [resolved] Component to delete in DB.

Anonymous — Mon, 05 Jan 2015 09:05:49 GMT

Hi,
Now I have this:

But when I execute the job I have this problem:
Exception in component tPostgresqlOutput_2
java.lang.RuntimeException: For delete, Schema must have a key
    at mdc.omel_0_1.omel.tFixedFlowInput_1Process(omel.java:1528)
    at mdc.omel_0_1.omel.tJava_1Process(omel.java:1267)
    at mdc.omel_0_1.omel.tFileList_1Process(omel.java:1089)
    at mdc.omel_0_1.omel.runJobInTOS(omel.java:5015)
    at mdc.omel_0_1.omel.main(omel.java:4797)
disconnected
For tFixedFlowInput I have this:

For PostgresqlOut this:

How I can set the key values and what values?
Thank you.

Re: [resolved] Component to delete in DB.

Anonymous — Mon, 05 Jan 2015 10:07:43 GMT

Open the schema of the tFixedFlowInput and set the key flag to the appropriated columns. By the way you have not set any values in the tFixedFlowInput. This will become the next issue!

Re: [resolved] Component to delete in DB.

Anonymous — Mon, 05 Jan 2015 10:21:13 GMT

I have this schema for tFixed:

What I must set?

Re: [resolved] Component to delete in DB.

Anonymous — Mon, 05 Jan 2015 10:30:06 GMT

You have not set any column as key so far (the first checkbox-column in your picture).

Re: [resolved] Component to delete in DB.

Anonymous — Mon, 05 Jan 2015 11:08:15 GMT

Ok, now I have this:

I need something more?
Thank you jlolling

Re: [resolved] Component to delete in DB.

Anonymous — Mon, 05 Jan 2015 12:29:27 GMT

Nope thats it. All other columns will be ignored for the delete action.

Re: [resolved] Component to delete in DB.

Anonymous — Mon, 05 Jan 2015 12:36:40 GMT

Finally I used tPostgresqlrow, this component does not return any value, I put the query as had in tPostgresqlInput and done what I wanted.
Thank you very much.

Re: [resolved] Component to delete in DB.

Anonymous — Mon, 05 Jan 2015 13:08:32 GMT

You can use an advancement of this component. Instead of writing the where condition with the values in SQL you can use prepared statements. This will allow you to write the SQL without values instead set a ? for the value and in the advanced settings of the component set the actual values without any type conversion to string.