Custom DB Update & Delete

KNIME Extensions
1

Hi,

I’m looking to execute custom update/delete SQL statements (on an MS SQL database) different from the standard ones provided by the DB Update & DB Delete nodes. Two reasons for that:

  1. I’d like to make my update/delete statements more efficient by using WHERE clauses like “Key IN (1,2,3,4,5)” instead of executing multiple statements with “Key=1”, “Key=2”, “Key=3” etc like the standard DB Update/Delete node do.
  2. My table name starts with a number and encapsulating the name in brackets doesn’t seem to do the trick anymore in the new DB framework (it does work in with the legacy Database Update/Delete nodes). The new nodes gives the following error: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name. I’ve tried encapsulating the name of the table and/or schema with brackets or double quotes. Same problem with the DB Insert node.

It feels like I’m missing something obvious but going though the DB Extention Guide didn’t bring the needed answers. Any thoughts?

Thanks,
Kevin

2

For SQL commands that are not encapsulated in a DB node you might try to use the DB SQL Executor:

With regards to the special characters or numbers at the beginning of a table name I can think of two possibilities.

The new DB nodes seem to like this style

“default”.“_my_data_table”

Other than that some Big Data environments like this style with the accent grave:

DROP TABLE IF EXISTS `default`.`_my_data_table`;

2 Likes
3

Exactly what I needed - thanks!

2 Likes
4

Is there an other node that provides output data and/or variable back to Knime?
I’ve been playing around with the Parameterized DB Query Reader and UPDATE + OUTPUT statements but without success.