Hello,
I've recently attempted retrieving some data from a PI Server using the PI-ODBC driver (v1.02). This driver is known to work with our version of PI from other custom applications and in MS Access, and is used in the definition of a system data source PISys in Windows 7's ODBC Data Source Administrator.
I have been able to establish a connection with the PI database using this data source in the Database Connector node by using sun.jdbc.odbc.JdbcOdbcDriver as the database driver and jdbc:odbc:PISys as the Database URL, however I am unable to pass SQL queries to PI using nodes such as Database Reader and Database Table Selector.
PI's implementation of SQL is anything but standard, an example of a query is included below:
SELECT * FROM PIMEAN WHERE TAG LIKE 'SOMETAG' AND TIMESTEP = "+15m" AND TIME >= DATE('11-Aug-2015 00:00:01') AND TIME <= DATE('15-Sep-2015 00:00:01')
In a custom Delphi application and in MS Access, this query works with the PI-ODBC driver and returns data as expected. But I receive two errors when attempting to run it in Database Reader and Database Table Selector nodes with the Database Connector supplying the connection.
Using the Database Reader node...
Execute failed: [OSI][PI-ODBC][PI]SQLGetConnectOption: fOption = 102
Using the Database Table Selector...
Execute failed: Error while validating SQL query: [OSI][PI-ODBC][PI]FROM Clause> < ( > is an invalid name
I suspect that this is a very similar issue to that experienced by lclough on this thread back in 2011: https://tech.knime.org/forum/knime-general/databse-querry-node-notworking-properly-issue-copypasting-from-nodes-field
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 tried activating and deactivating the "Run SQL query only during execute, skips configure" option in the Database Reader to no avail.
- Does anyone here have a suggestion that might fix my problem?
- Is there a way to get the SQL as specified to PI without being manipulated, such as into a subquery?
- Would I have better results with PI's JDBC driver loaded directly into KNIME?
Thank you for your help,
Matt