Database writer error

KNIME Analytics Platform
1

I’m using the Database writer node to write to an Oracle database and I am getting a database error. I have a column that I have created where the value is actual a function call:

String guid = “LOWER(REGEXP_REPLACE(sys_guid(),’(…)(…)(…)(…)(…)’,’\1-\2-\3-\4-\5’))”;

I have this column designated as a string. The problem is the Database writer seems to be treating this column as an actual string and I think enclosing the value with single quotes. This is a primary key, I get an error when the second row gets inserted with a primary key violation. This is not what I want. I tried changing the type to numeric hoping the column value would be entered as is without quotes.
I have generated a sql insert with this value and the insert statement will work fine. Is there anyway to let the Writer know not to enclose the value with single quotes (if that is what is happening)?

2

I think what I need to do is call the function (or any other function such as TO_DATE() etc) first to get the actual string return value and then use that value in the column to be used for the column value before the call to the Writer. Unless there is another way, I will proceed with this…

3

Hello dfrey,
it is not possible to use a function when writing to a database using the DB Writer or DB Inserter node since the nodes will treat the function as a string and simply insert it into the database. If all the data you want to write into the table already exists in other db tables you could use a query (e.g. DB Table Selector node) to get all the columns you need, then append the column that is computed using a SQL function with the DB Query node and finally writing the result into the table using the DB Connection Table Writer.
Bye
Tobias

1 Like
4

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.

5

Hello @dfrey ,
we just release version 4.7 of the KNIME Analytics Platform which comes with the new DB Row Manipulator node. The node supports writing of individual DML statements using the rows of the input table as values.
For an overview of all the new features have a look at the What’s new page. For all the details go to the changelog.
Bye
Tobias

1 Like