DB Writer: Data truncation

[volsebna]

Hello KNIME-Community,
I am about to create a workflow which should use DB-Writer to dynamically create the table structure, based on the KNIME data content (table input).
I had already a similar use-case, and node works in general. In this specific case, the problem is one column. The node creates column in DB with length varchar (255) instead of 4000 (based on data input).
Any way to solve this? Creating a table using DB table creator before using DB Writer node is not an option, since all columns are not known in advance and may change.
Thanks!

[takbb]

Hi @volsebna , I agree that the varchar(255) default does cause problems with the DB Writer, and it would be nice if the type mapping on that node allowed you to specify the data length. I’ve been there and it was frustrating.

Here is a possible way forward.

In the attached workflow, using an H2 database as an example, I am trying to insert the following data into a new db table:

The synopsis lengths show that the total size of the synopsis column exceeds 255 characters and sure enough, if we try to insert it using a DB Writer it fails:

Using the following method though, it is possible to put the data onto the database, and still have the table created dynamically:

Add a DB Table Creator to your workflow and click the “…” on the node and add a data port. The presence of a data port allows you to tick the “dynamic” option in the DB Table Creator:

You can now use “Dynamic Type Settings” and have it so that a String will always generate a varchar(4000) column instead of a varchar(255) column

You do this by choosing the Dynamic Type Settings tab, then below that the KNIME-Based SQL Type Mapping tab, then click Add, and choose String as the KNIME-Type, and then specify the SQL Type to choose. I suggest also turning off “Not Null” unless you know for sure you have no missing data:

Then on the DB Writer, make sure you are NOT removing the existing table, or this will undo your hard work!!

This does of course mean that all your strings will now be the varchar(4000) column specification on the database. You can specify specific data sizes based on column-names using Regex on the “Name-Based SQL Type Mapping” tab, but beyond that I don’t think there is a lot you can do dynamically, short of dynamically generating a SQL create table statement (which can be done, as I’ve done it, but it is more work!),.At least this should get the data into the database.

Demo - dynamic table creation with column size specification.knwf (113.4 KB)

[volsebna]

@takbb thanks it works perfectly!

[takbb]

Hi @volsebna , that’s great. Glad it helped

And thanks for validating the solution, which means others with a similar question will more easily find an answer quickly.