insert data into table with autoincrement

Hi everyone,

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?

 

thanks for response

cheers

Michi

Hi Michi,

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.

Bye,

Tobias

Hi Tobias

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?

Hi, Michi

How about your problem, I am meeting the same problem.

Hello Michi,

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.

Hello Michi,

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.

Bye,

Tobias

Hello Tobias

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

 

cheers

Michi

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!

You can try DB SQL Executor node.

2 Likes

Thanks for your suggestion, @izaychik63!

Hi there @af_santana,

did you try DB Insert or DB Write node and not having that auto-increment column in your input table?

Br,
Ivan

Hi,

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?

Thanks

Hi,

The problem is solved, thanks!

2 Likes