SQL Writer Node / SQL INSERT with variable field lengths.

KNIME Analytics Platform
1

Dear KNIME Community,

I am hoping that you are able to help me out with a challenge with the database nodes.

I am trying to use KNIME to do following steps:

  1. Read various files,
  2. define columns (by algorithm),
  3. load the result (=table) into a SQL database.

The challenge: The number of SQL database fields for different output tables vary and their length&data types are predefined (e.g. table 2: "nvarchar (3), datetime, nvarchar (10), nvarchar (12), datetime", whereas the next table could consist "nvarchar (4), nvarchar (20)")

Unfortunately, I do not see any solution to provide the correct length&data type to a variable number of SQL fields. My attempt to abuse the Parameterized Database Query to run an SQL INSERT / REPLACE command provided by a variable was also not successful. Nevertheless, I am successful to create empty sql tables with the Database SQL Executor node.

Does anybody have a solution for me?

Bonus point: It would be great if the solution would also be able to update database rows in one step (see REPLACE command).

Thank you in advance!
linkm
 

PS: Thank you for KNIME @developers!

2

Hi,

you could create your own create table statement using existing KNIME nodes e.g. String Manipulation, Java Snippet etc. and then pass this create statement on to the SQL Executor nodes which allows you to execute any DDL statements. The Database Writer node itself just executes an insert statement if the table already exists an thus should work with changing input columns per execution.

Replace/merge is not supported with the current DB nodes but we are planning to support in the future.

Bye

Tobias

3

Thank you, Tobias. That was very helpful and works for me.

Do you also have any hint for me, what kind of data format I have to provide to the Database Writer node if I want to write dates or datetimes to an SQL Server (SQLite in my case)?

I am looking forward to use the planned replace/merge functionality in the future.