Issue with DB Writer node (KNIME Labs) - job OK but DB empty

szawadski · October 18, 2018, 3:51pm

Hello,

I have an issue using the DB writer node included in the KNIME Labs/DB section.
I am trying to add records to a a table created in an Oracle DB.
The table has been created properly using the DB Table Creator node based on the KNIME table with my data.

The records seem to be added properly to the DB and the workflow finishes without error but when I look into the table, there is nothing.

MSO_OWNER is the DB on which I checked I have indeed the right to insert records (directly writing a SQL request).
TMA is the source DB that I am querying a bunch records.
I am just filtering the 1000 first records for test purposes.

Here is the output of the DB Writer node

Note the insert status is “-2” --> processed successfully

But the table remains empty.

I checked the log and I got something strange (all the question marks in the SQL request)
Is it normal?
Is this node working properly for you? What am I doing wrong?

2018-10-18 10:12:52,345 : DEBUG : KNIME-Worker-84 : ExecutionData : DB Writer : 0:492 : Create SQL statement as prepareStatement: INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
2018-10-18 10:12:52,348 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. PreparedStatement.executeBatch(): (100): INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ;
2018-10-18 10:12:52,356 : DEBUG : KNIME-Worker-84 : Buffer : DB Writer : 0:492 : Using table format org.knime.core.data.container.DefaultTableStoreFormat
2018-10-18 10:12:52,541 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. PreparedStatement.executeBatch(): (100): INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ;
2018-10-18 10:12:52,549 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. PreparedStatement.executeBatch(): (100): INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ;
2018-10-18 10:12:52,555 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. PreparedStatement.executeBatch(): (100): INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ;
2018-10-18 10:12:52,563 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. PreparedStatement.executeBatch(): (100): INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ;
2018-10-18 10:12:52,573 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. PreparedStatement.executeBatch(): (100): INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ;
2018-10-18 10:12:52,580 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. PreparedStatement.executeBatch(): (100): INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ;
2018-10-18 10:12:52,587 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. PreparedStatement.executeBatch(): (100): INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ;
2018-10-18 10:12:52,594 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. PreparedStatement.executeBatch(): (100): INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ;
2018-10-18 10:12:52,602 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. PreparedStatement.executeBatch(): (100): INSERT INTO "MSO_OWNER"."SORTED_EVENTS" ("ITEM_CODE", "EVENT_ID", "EVENT_STATUS_KEY", "DIRECTION", "EVENT_CREATION_DATE_YYYYMMDD", "EVENT_CREATION_TIME_HHMMSS", "RECOGNIZED_MAILBOX_KEY", "GU_LIST_ID", "UNIT_LOCATION", "DESTINATION_UNIT_LOCATION", "ROUND_KEY", "ROMA_ROUND", "BARCODED_PRODUCT_KEY") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ;
2018-10-18 10:12:52,607 : DEBUG : KNIME-Worker-84 : Buffer : DB Writer : 0:492 : Closing input stream on "C:\Users\u523705\AppData\Local\Temp\knime_IZ_EE v235552\knime_container_20181018_5675482825687774902.bin.gz", 0 remaining
2018-10-18 10:12:52,611 : INFO  : KNIME-Worker-84 : jdbc_audit : DB Writer : 0:492 : 164. Connection closed
2018-10-18 10:12:52,612 : INFO  : KNIME-Worker-84 : LocalNodeExecutionJob : DB Writer : 0:492 : DB Writer 0:492 End execute (0 secs)
2018-10-18 10:12:52,612 : DEBUG : KNIME-Worker-84 : WorkflowManager : DB Writer : 0:492 : DB Writer 0:492 doBeforePostExecution
2018-10-18 10:12:52,612 : DEBUG : KNIME-Worker-84 : NodeContainer : DB Writer : 0:492 : DB Writer 0:492 has new state: POSTEXECUTE
2018-10-18 10:12:52,612 : DEBUG : KNIME-Worker-84 : WorkflowManager : DB Writer : 0:492 : DB Writer 0:492 doAfterExecute - success
2018-10-18 10:12:52,612 : DEBUG : KNIME-Worker-84 : NodeContainer : DB Writer : 0:492 : DB Writer 0:492 has new state: EXECUTED

Regards,

Sébastien

szawadski · October 22, 2018, 7:17am

Some news from my problem: tested exactly the same configuration with the “old” official nodes (ie not the Knime Labs ones) and it works!
Anyone managed to use the Knime Labs DB writer (eventually with Oracle JDBC driver)?
@Iris, is there any reported bug concerning this node?

tobias.koetter · October 23, 2018, 2:34pm

Hi Sébastien,
thanks for reporting this issue. Have you selected the temporary table option in the DB Table Creator node? When I select this flag I can reproduce your problem. The reason for it is that Oracle by default seems to delete all rows that have been inserted into a global temporary table after a commit. To prevent this add the following line to the DB Table Creator Additional Options tab
ON COMMIT PRESERVE ROWS

I will open a ticket for this.
Bye
Tobias