Database writer

I'm using a database writer - and it appears to create the tables in mysql and postgresql correctly.

What I'm not clear about is how I might then execute arbitrary SQL statements, e.g. to create indices etc?



Well, Database Writer failed [on second loop through] with error:

'Execute failed: Column "id" of type "StringCell" from input does not match type "VARCHAR" in database at position 16'

In the MySQL DB, field "id" was type "text".

When I changed the column "id" to type "varchar(10)" both in the back-end DB and the node configuration, the node ran fine.

"StringCell" should be compatible with "text" - so is this a bug?




Hi Steve,

You can use the Database Reader node to execute SQL other than SELECT statements.  However, if you want the node to execute without an error, you must add a 'dummy' SELECT at the end of the SQL to give the node something to return.  For example (lifted from a workflow where I do exactly what you are asking for!):


CREATE INDEX fps_maccsfp_idx ON rdk.fps USING gist(maccsfp);

SELECT count(*) FROM rdk.fps


Kind regards


Splendid, I'll try that.



With KNIME 2.10, we will release a generic SQL Executor nodes, among many other new cool Database Access and Manipulator nodes. Just stay tuned.