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