[resolved] Component to delete in DB.

One Star

[resolved] Component to delete in DB.

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.
Moderator

Re: [resolved] Component to delete in DB.

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Component to delete in DB.

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.
Seventeen Stars

Re: [resolved] Component to delete in DB.

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)
One Star

Re: [resolved] Component to delete in DB.

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.
Seventeen Stars

Re: [resolved] Component to delete in DB.

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!
One Star

Re: [resolved] Component to delete in DB.

I have this schema for tFixed:

What I must set?
Seventeen Stars

Re: [resolved] Component to delete in DB.

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

Re: [resolved] Component to delete in DB.

Ok, now I have this:


I need something more?
Thank you jlolling
Seventeen Stars

Re: [resolved] Component to delete in DB.

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

Re: [resolved] Component to delete in DB.

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.
Seventeen Stars

Re: [resolved] Component to delete in DB.

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.