I'm trying to insert (or "append data ... to existing table (if any)") some calculated data into an existing table with the Database Writer Node. Now I stumbling across the first column in the database which is set to autoincrement (IS_AUTOINCREMENT=YES).
I'm getting errors like this:
WARN DatabaseWriterConnection Error while adding row #11 (Row90749_Row327), reason: Cannot insert explicit value for identity column in table 'artikel_tag' when IDENTITY_INSERT is set to OFF
Does this means that the Database Writer is trying to enter the Knime-Identity of the row into the DB?
Or is there any option I've missed to deal with the autoincrement issue?
the Database Writer node creates a sql statement based on all columns of the input table. Thus if your input table contains the auto increment column the node tries to insert a value into this column. If you filter the auto increment column prior the Database Writer it should work. All sql statements are also written to the KNIME log. If you want to examine a statement, go to View->Open KNIME log after executing the Database node.
Thanks for the response. Maybe I wasn't clear enough in my first post.
- I got a table with calculated data in KNIME
- I want to add this data to an existing table
- I therefore use the Database Writer Node (DBWN)
Now as fas as I understood your advice correctly, I should get rid of the identifier in the input table for the DBWN.
The produced SQL Statement (retrieved from the logfile) is:
DEBUG KNIME-Worker-4 DatabaseWriterConnection : Executing SQL statement as prepareStatement: INSERT INTO ar_artikel_tag (artikelTagId,artikelId,tag,erstellDatum) VALUES (?, ?, ?, ?)
but I don't want the writer to instert 4 values, since the first is an autoincrement which in turn causes the error from the DB if I try to insert the data. I only want the writer to insert the columns artikelId, tag, and erstellDatum.
Now what I am asking is: There is the "Row ID" column in the (KNIME)table (which I suppose to be a KNIME internal identifier): how can I prevent the writer from writing this column? (Column filter Node doesn't offer the option to filter the Row ID column.) Or is the writer trying to write 4 values, because its scan of the table (again from the logfile: DEBUG KNIME-Worker-4 DatabaseWriterConnection : Executing SQL statement as executeQuery: SELECT * FROM (SELECT * FROM ar_artikel_tag) tempTable_8890451922726493518 WHERE (1 = 0)) resultet in 4 values per row?
sorry about the confusion. You were right after all. KNIME currently retrieves all columns from the database table and inserts null for all columns that are not present in the KNIME data table. Unfortunately this makes the insertion of rows into a table with autoincrement columns not possible. We will have a look into the problem and try to fix it as soon as possible.
Sorry about that,
Tobias
P.s. I have deleted my previous post to prevent future confusions.
with KNIME 2.11 the node will behave as described in my initial comment if the new "Insert null for missing columns" option is deselected. If it is deselected the node will only insert values into database columns that are present in the KNIME table and leaves the filling of the missing columns to the database.
ok, thanks for the information. Since I am able to create my own tables with settings, I can go round the problem, but I think, the scenario with a table set to autoincrement is not a very odd one (and I suppose in many cases, one is limited considering rights of DBM).
I hope that KNIME will provide a workaround (maybe a node, where a customised SQL insert command can be specified to put the calculated KNIME data into a DB (specified by the node)).
Hi everyone!
I have a question similar to this topic. I saw that, apparently, the problem had no solution in KNIME at the time of the original post. However, since this topic is old, I thought that this feature may have been implemented in newer versions of KNIME.
I need to insert data into an Oracle table whose primary key is an auto-increment field (that is, it is not necessary to set a value at the time of record writing. In Oracle it is a ‘sequence’ type field). In Oracle SQL, I would use an 'INSERT INTO table (<pk_field>, <field_1>, …, <field_n>) VALUES (SEQUENCE.NEXTVAL, <value_1>, …, <value_n>) command. But in KNIME, I didn’t see how to do it using the ‘DB Insert’ or ‘DB Write’ components (I’m using version 4.0.1). Does anyone have a tip on how to do it with these or any other component in KNIME?
Thanks in advance!
I have a similar question but with a table that doesn’t have an autoincrement field - instead a field that is populated with a sequence. How can I can have the data read from an excel file and persisted using a sequence to populate the primary key field?