Unable to query : Data source - Osisoft PI
anantk Mar 19, 2015 8:39 AMHi,
I am trying to query an Osisoft PI datasource through a Virtual Database using TEIID.
Now, queries to osisoft PI must always specify the time (timestamp or a set of timestamps) for picking values (since all timestamps would include years of data stored as frequently as 1 value per second).
On setting up the sourcemodel and deploying the vdb, the time column was automatically mapped as a timestamp type.
When I try to execute the following Query in the scrapbook:
select "tag", "value" from "pimin" where "time" = NOW()
I get the following Error:
TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 PI_Archive: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [{ts '2015-03-18 14:34:05.383'}] SQL: SELECT "piarchive"."pimin"."tag", "piarchive"."pimin"."value" FROM "piarchive"."pimin" WHERE "piarchive"."pimin"."time" = ?]
I get a similar error when I use the parsetimestamp function.
However, when I provide an inappropriate string as a timestamp, an empty resultset is generated.
Eg : select "tag", "value" from "pimin" where "time" = 'tjb'
would generate empty "tag" and "value" columns.
I am assuming that this is occurring because some internal comparison at the TEIID end sends a null timestamp query if an invalid string is given as timestamp. Since no value in the PI data source has null timestamps, empty columns are returned.
Additional information:
The osisoft PI Data Access Server comes with a PIJDBC driver which can be used to connect a JDBC compliant application (TEIID) to PI.
The connection string was specified, along with the username, password etc. while configuring the driver from the jbdevstudio end. The connection test was successful.
The PIJDBCDriver.jar did not initially contain the "METAINF/java.sql.Driver" file. Therefore I added the file containing the fully qualified path to the driver class(com.osisoft.jdbc.Driver).
Following that change, I was able to deploy the virtual database containing the source model linking to PI.
The connection string looks like:
jdbc:pisql://xxx.xxx.xxx.xxx:pppp/Data Source=xxx.xxx.xxx.xxx; Integrated Security=SSPI
TEIID Version: 8.4
JBDevStudio 7.1.1
Any help you could provide in the matter will be deeply appreciated.
Thanks