Using ODBC to interact with an OSIsoft PI database

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 

Hi Matt,

KNIME relies on subqueris to extract meta information from the database. So if the OSIsoft PI database does not support subqueries e.g. 'select * from (select col1, col2 from table1) limit 1' you can not use the KNIME database nodes for accessing it. The KNIME log (View-> Open KNIME log) contains all sql statements KNIME sends to the database if you want to inspect these.

The only workaround I see right now is to use the Java Snippet node. But you would need to write the Java code to create a connection to the database and execute your statement.

Bye,

Tobias