Database writer

KNIME Analytics Platform
1

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?

Cheers,

Steve.

2

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?

Cheers,

Steve.

 

3

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

James

4

Splendid, I'll try that.

Cheers,

Steve.

5

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.