Database query node: problem with "create" command

Hello community!

I am trying to use the node Database Query but I am encountering several issues that I would like to share with you, and, hopefully, receive some suggestions:

  1. when you do

SELECT * FROM #table#

you need to include an alias at the end if you want to make it work. This is not specified in the node description! It would be great to specified it.

  1. I want to create a clustered index. In principle, in this node you can have multiple commands before the SELECT. In this case, I would do:

CREATE CLUSTERED INDEX ix_test ON #table# (variable_name);
SELECT * FROM #table# as A

Unfortunately, this does not work. The node does not recognize #table# (it says you do not have access or it does not exist yet). If you include an alias in the CREATE command, it crushes anyway because it is an illegal construct. How can I do this? Is there a chance to do it inside the Database Query node?

  1. I also tried to use CREATE TABLE before the SELECT statement, but the same issue happened. This nodes gets confused by the default #table# construct, and it does not allow you to use aliases to help him (only in the SELECT statement it works).

Happy to hear some experiences you had with this node!

Thanks for the help!

Most likely you will have to use the SQL Executor after an SQL DB Connector to get anything done that changes data on your SQL DB. Because the Database query basically creates a VIEW and after that you cannot append any manipulations via code.

In addition you might be able to try your luck with the DB Table selector that allows for:

Additional SQL commands (create, drop, set parameter, etc.) can be added before the actual SELECT statement and are executed together line-by-line during configure and execute. Note, those additional SQL commands need to be separated by semicolon.

So after this node you will again be stuck with a VIEW. From my experience if you want not to use the plenty of KNIME specific DB nodes but ‘plain SQL’ your best shot is the SQL Executor (I use it all the time).

1 Like

Hello mlauber71,

You are actually right. I did exactly that, but I was wondering if there is the chance to do some operation inside the node Database Query as well. In principle, in the node description there is written that is possible, but I still have to understand how…

You could try and use the new DB (Labs) nodes. They are still under development but they would be the future standard DB connection. Maybe you see if the examples give you an idea if whaT you want to do could be achieved with them.

Aloe you might construct a simple example (with SQLite) to show us what you want to do and where your problem is. Then someone might be able to weight in with ideas.

1 Like

Hello venturaz87,
with the KNIME 4.0 release the DB (Labs) nodes mlauber71 is referring to are no longer in labs but part of the KNIME standard installation. The previous database nodes are still available and marked with (legacy) in the node name.
The Database Query (legacy) node allowed you to execute any other sql statements prior the last statement which needed to be a select statement. However these statements need to be separated by “;<NEW LINE>”.
The new DB Query node no longer supports this option to better distinguish between SQL commands that select data and commands that alter the data. Unfortunately the node description does not reflect this which we will change with the next release. So in the new framework you will need to use a DB SQL Executor to execute the create cluster index prior using the DB Query node as described by mlauber71.
By the way, the reason for the table alias is that the #table# is actually replaced by the whole input SQL query.

1 Like