Work-around for Database Reserved Words

Problem

Many times I come across column names that are Reserved Words for the database server in the back. When using the KNIME database notes the Reserved Words make the fail to complete the insert, select, update or delete actions.

For SELECT actions (Database Reader node, etc.), you easily add the default escape characters for the specific database engine (e.g. ‘[‘ and ‘]’ for SQL Server) to the query. But this doesn’t work for other actions. The notes for INSERT, UPDATE and DELETE actions work with the columns of your dataset, not a manually crafted query/statement.

Solution / Work-around for UPDATE and DELETE

Simply rename the columns (Column Rename node), giving you trouble, adding a back-quote (`, together with ~ on US-keyboard) before and after the column name. Now the actions do complete succesfully.

This solution works for at least when using a SQL Server 2012 back-end and JTDS-1.3.1 connector in KNIME 3.1.2.

Note

I did not find a solution for the INSERT actions and still use JavaSnippets and handcrafted statements.  If anyone has additions or alternative solutions, please share!

Hello,

thanks for posting this issue and a solution. We will have a look at the column quoting in general to also quote columns that are db keywords. I wonder why the insertion isn't working with your workaround e.g. adding the quote characters around the problematic column names befor the Database Writer. What is the error message? Could you have a look into your KNIME log file after the insertion has failed to see how the INSERT statement loocks like. Simply go in KNIME to View->Open KNIME log and search (Ctrl-f) for "Executing SQL statement as prepareStatement". This should show you the problematic INSERT statement.

Bye

Tobias

Hello Tobias,

I gathered the logs for you... using a sample with three columns: Source, Schema (reserved) and Table (reserved). The workflow uses a table creator to build the dataset and the database writer to insert into the existing table.

Not escaped column names

Running the workflow without escaping the reserved words, it fails with:

DEBUG Database Writer 2:27 Table [TEST].dbo.[reserved names] exists

DEBUG Database Writer 2:27 Executing SQL statement as prepareStatement: INSERT INTO [TEST].dbo.[reserved names] (Source,Schema,Table) VALUES (?, ?, ?)

WARN Database Writer 2:27 Error while adding row #2 (Row0), reason: Incorrect syntax near the keyword 'Schema'.

WARN Database Writer 2:27 Errors "1" writing 1 rows.

INFO Database Writer 2:27 Database Writer 2:27 End execute (0 secs)

 

Escaped column names

Running the workflow with escaping the reserved words (using the ` char), it falis with:

DEBUG Database Writer      2:27       Checking if table [TEST].dbo.[reserved names] exists

DEBUG Database Writer      2:27       Execute query: SELECT * FROM (SELECT 1 as tmpcol FROM [TEST].dbo.[reserved names]) tempTable_8401526812293668290 WHERE (1 = 0)

DEBUG Database Writer      2:27       Table [TEST].dbo.[reserved names] exists

DEBUG Database Writer      2:27       reset

ERROR Database Writer      2:27       Execute failed: No. of columns in input table > in database; not existing columns: ["`schema`", "`table`"]

DEBUG Database Writer      2:27       Execute failed: No. of columns in input table > in database; not existing columns: ["`schema`", "`table`"]

java.lang.RuntimeException: No. of columns in input table > in database; not existing columns: ["`schema`", "`table`"]

 

Hope this helps,

A