AnsweredAssumed Answered

web service against PIOLEDB

Question asked by TomMcCarthyNeoPLM on May 5, 2016
Latest reply on May 6, 2016 by Marcos Vainer Loeff

Hi,

 

We are trying to write an .Net web service against the PI OLEDB connector, and will be utilizing AF.  We are trying to execute a query like below using the standard System.Data.OleDB.Oledbconnection and are experiencing connection issues as well as sql errors, as below.  Should we be using a different set of objects?  It seems that the SQL variant that PI uses is not working correctly with the standard OLEDB library.  The query executes correctly in PI SQL Commander.

 

Here is the query

 

select eh.path, eh.name, ea.name, pl.value, pl.time, pl.isgood, uom.abbreviation

from asset.elementhierarchy eh

     inner join elementattribute ea

     on ea.elementid = eh.elementid

     left outer join System.UnitOfMeasure.UOM UOM

     on UOM.ID = ea.defaultuomid

     CROSS APPLY Data.Plot( ea.ID, '2016-Apr-04', '2016-Apr-05', 300 ) pl

where eh.path = '\Johnson&Johnson\Janssen Geel\' and

eh.name = 'PiPoints' and

ea.name = '100090R011TIC04.PV' option( Force Order )

 

Here is the error.

 

Command to prepare: select eh.path, eh.name, ea.name, pl.value, pl.time, pl.isgood, uom.abbreviation from asset.elementhierarchy eh inner join elementattribute ea on ea.elementid = eh.elementid left outer join System.UnitOfMeasure.UOM UOM on UOM.ID = ea.defaultuomid CROSS APPLY Data.Plot( ea.ID, '2016-Apr-04', '2016-Apr-05', 300 ) pl where eh.path = '\Johnson&Johnson\Janssen Geel\' and eh.name = 'PiPoints' and ea.name = '100090R011TIC04.PV' option( Force Order )

05-May-2016 12:22:20.603 ERROR_RECORD PCSPool 12060 9 13176 Command ICommand Execute DB_E_ERRORSINCOMMAND - [SQL Parser] [Line 1] Syntax error near 'left'.

Outcomes