Parameterized Database Query node giving incorrect syntax error

database
#1

Hello,

I have 3 nodes: Microsoft SQL Server Connection, Database Table Selector, Parameterized Database Query

First 2 nodes work fine and I am able to see the results. But Parameterized Database Query node throwing the folloing error:
ERROR Parameterized Database Query 0:2570 Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘)’.

SQL Query in Parameterized Database Query node is: SELECT * FROM #table#

image
Any help would be really appreciated!!

Regards,
Nithin

0 Likes

#2

Hi @nithinth7,
What is the table name that the placeholder “#table#” is substituted for? Does it maybe contain invalid characters? Have you tried or could you try the query with another database system, e.g. a simple local SQLite database? The error seems to come from Microsoft’s SQL driver, not from KNIME itself.
Kind regards
Alexander

0 Likes

#3

@AlexanderFillbrunn
the #table# was actually another big query. The query replacing #table# is working fine when executing with knime database reader node, but throwing error when using with parameterized database query as #table#. So I am using the actual big query directly in the parameterized database query and it is working. No #table# involved in the query.

0 Likes

#4

@nithinth7 oh, I see. I am glad it works now. Can I close this topic then?

0 Likes

#5

@AlexanderFillbrunn
The workflow is working for me, after the change. But I still don’t know why it is throwing error when using #table# as a place holder for another query.

0 Likes

#6

@nithinth7 You want the placeholder to be replaced by a subquery? Maybe KNIME wraps it in quotes and that’s why it complains when it encounters a strange “table name” with parantheses?

0 Likes

#7

Hello nithinth7,
the node creates a query with the input query as subquery such as
SELECT * FROM (SELECT * FROM “inputTable”) as “table”.

Some databases such as Microsoft SQL Server do not support subqueries without a table alias. So if you change your statement to the default statement which uses a table alias e.g. SELECT * FROM #table# AS “table” it should execute fine.
Bye
Tobias

2 Likes