I am using KNIME 2.2.0 SDK on Eclipse 3.5.2/Java 1.6.0_20
I am having issues with the "Databse querry node". As far as as I understand this node, it simply modify anSQL querry by replacing #table# by (previous SQL querry).
I have tried many things and I keep getting errors about name ambiguously definned, and I cannot seem to use table aliases.
Also I cannot seem to paste anything into the text box of the SQL querry.
Finally it would be great if the error could be directly repported at the bottom of node properties dialog box, lik it is done for other nodes.
Can you please provide us with an SQL example that does not work in the Database Query node. I will then have a look into this issue.
I'm having a similar problem with the database readers/connectors etc.
My SQL query
SELECT time,value,status FROM PIcomp WHERE tag = "PHM.TRANS.TRS.KWMX.WM" AND time = DATE("*")
Throws up the following warnings
WARN DatabaseReaderConnection PreparedStatment not support by database: [OSI][PI-ODBC][PI]FROM Clause> < ( > is an invalid name
WARN Database Connector java.sql.SQLException: [OSI][PI-ODBC][PI]FROM Clause> < ( > is an invalid name
Looking in the KNIME log reveals
DEBUG main DatabaseReaderConnection : Executing SQL statement "SELECT * FROM (SELECT time,value,status FROM PIcomp WHERE tag = "PHM.TRANS.TRS.KWMX.WM" AND time = DATE("*")) table_28429928 WHERE 1 = 0"
So KNIME appears to be generating a subquery out of my original SQL, unfortunately the ODBC driver for the database I am connecting to doesn't support subqueries
"PI ODBC supports a subset of the Core level:
Data Definition Language (DDL): not supported
Data Manipulation Language (DML): simple SELECT, INSERT*
Expressions: simple expressions, AVG, COUNT, MAX, MIN, SUM.
Data types: VARCHAR, INTEGER, REAL, TIMESTAMP
*INSERT statement is only supported for the picomp table.
Subquery is not supported."
I've found the source code that creates this subquery in the eclipse editor. Is there some reason that KNIME requires the wrapper for the original query or is there a possibility that I could modify the code and create a new node that passes only the original query to the target database. I would prefer not to go down that path if I don't have to or if it won't work! Other suggestions for passing only the original query to the database would be appreciated!
Thanks for reporting this issue. The reason for using sub-queries is to have a way to restricted the returned number of rows of the SQL query at configure time, that is, the node only provides the structure of the table that will be returned after the node is executed. We figured out that several databases can't handle nested SQL statements together with aliases, and have therefore decided to provide an option to manually skip the configure step in the Database Reader -- this fix will be available with the next version.
Thank you Gabriel,
I'll look forward to trying out the result.
I had started to try and build a modified version of the database reader nodes but hadn't got far enough to try it out. The concept I intended to attempt was to simply parse the original SQL statement and modify (or add) the WHERE clause to return zero records to obtain the structure. I don't know if this would have worked but I had no alternative to try.