AnsweredAssumed Answered

PI JDBC Driver doesn't return correct values

Question asked by John Messinger Champion on Jan 19, 2018
Latest reply on Jan 19, 2018 by vkaufmann

I've been doing some testing of a scenario in which we need to use the PI JDBC driver to retrieve data from the PI system. My test queries are straight out of the PI SQL Query Compendium from PI SQL Commander. What I have found is that the results returned by PI JDBC are different to those returned by running queries directly with the PI OLEDB Enterprise Provider, and different to the data returned by other tools such as PI DataLink, PSE or PI-SMT.

 

I have executed the following SQL query (almost straight out of PI SQL Commander's example queries):

 

SELECT eh.Path + eh.Name Element, ta.*
FROM NuGreen.Asset.ElementTemplate et
INNER JOIN NuGreen.Asset.Element e ON e.ElementTemplateID = et.ID
INNER JOIN NuGreen.Asset.ElementHierarchy eh ON eh.ElementID = e.ID
CROSS APPLY NuGreen.DataT.TransposeArchive_Boiler
(
eh.ElementID,
N't' /*StartTime*/,
N't+4h' /*EndTime*/
) ta
WHERE et.Name = N'Boiler'
OPTION (FORCE ORDER, EMBED ERRORS)

 

From the result set returned by PI SQL Commander, I see the following results:

 

 

I know that these are 'correct', as I can validate them against the Archive Editor in PI-SMT as well as PSE:

 

 

These values were also returned by PI DataLink using PICompDat():

 

 

What I see when running the same SQL query with the PI JDBC driver (using DbVisualizer) is the following:

 

 

Note that for the first Element (B-499) where the Process Feedrate attribute maps the Sinusoid tag, that both the timestamps and the values are slightly off when compared to the results of PI OLEDB Enterprise, PI-SMT, PSE and PI DataLink. The last value in that sequence (96.16613 at 3:45:10) is markedly different in the PI JDBC results (98.14247 at 3:31:20).

 

My test environment is set up as follows (both machines are in the same time zone btw):

 

(Server)

PI Data Archive 2017 R2

PI AF 2017 R2

PI SQL DAS 2016 R2

 

(Client)

PI JDBC 2017 R2 (Windows version)

PI AF Client 2017 R2

PI SMT 2017 R2

PI DataLink 2017

 

I haven't been able to find an explanation for this unseemly behaviour by the PI JDBC driver. There is an older thread Datalink Compressed Data vs JDBC comp2 values that discusses the same behaviour, but which hasn't been satisfactorily answered.

 

This behaviour is somewhat troubling, as the data returned by the PI JDBC driver is not what is actually recorded in the data archives. For an application that relies on the recorded data this inaccuracy poses a potentially significant issue.

 

Has anyone else observed this behaviour? Can anyone at OSIsoft explain the cause of these discrepancies in the data events retrieved by the JDBC driver?

 

Cheers,

John

 

EDIT:

The other strange behaviour I observed is that the PI JDBC driver returns more rows for this SQL query than does the PI OLEDB Enterprise Provider in SQL Commander:

 

(SQL Commander)

 

(PI JDBC)

 

Seems some of the rows are duplicated by the JDBC driver.

Outcomes