[resolved] Component to delete in DB.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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)

[Anonymous]

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.

[Anonymous]

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!

[Anonymous]

I have this schema for tFixed:

What I must set?

[Anonymous]

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

[Anonymous]

Ok, now I have this:


I need something more?
Thank you jlolling

[Anonymous]

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