11 Replies Latest reply on May 6, 2016 6:20 PM by Marcos Vainer Loeff

    web service against PIOLEDB

    TomMcCarthyNeoPLM

      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'.

        • Re: web service against PIOLEDB
          Marcos Vainer Loeff

          Hi Tom,

           

          As you seem to be starting the development of this web service, I would like to know first:

           

          1) Have you considered using PI Web API instead?

          2) Since you are developing in C#, have you considered using PI AF SDK?

           

          According to the scenario you have described, if you are starting a project from scratch I don't see any major reason of choosing PI OLEDB to be used to create a custom web service instead one of the options above. If you have already a project and you just want to maintain it, this is another story!

            • Re: web service against PIOLEDB
              TomMcCarthyNeoPLM

              HI Marcos,

               

               

               

              We have an existing Web Service API / Interface that we are trying to adapt

              for this purpose.  OLEDB is a technology our developers understand.

               

               

               

               

               

               

               

              Thomas W. McCarthy | Integration Manager

               

              *Neo PLM * | 555 Long Wharf Drive | New Haven | CT | 06511 | USA

               

              *T1 203 292 1835 x112 | M1 203 923 3579 *

               

              thomas.mccarthy@neoplm.com <thomas.mccarthy@neoplm.com>

               

               

               

               

               

              From: Marcos Vainer Loeff

              Sent: Thursday, May 5, 2016 1:24 PM

              To: Tom McCarthy <thomas.mccarthy@neoplm.com>

              Subject: Re:  - web service against PIOLEDB

               

               

              PI Square <https://pisquare.osisoft.com/?et=watches.email.thread>

               

               

              web service against PIOLEDB

               

              reply from Marcos Vainer Loeff

              <https://pisquare.osisoft.com/people/mloeff?et=watches.email.thread> in *PI

              Developers Club* - View the full discussion

              <https://pisquare.osisoft.com/message/65309?et=watches.email.thread#comment-65309>

              • Re: web service against PIOLEDB
                TomMcCarthyNeoPLM

                HI Marcos,

                 

                 

                 

                We have an existing Web Service API / Interface that we are trying to adapt

                for this purpose.  OLEDB is a technology our developers understand.

                 

                 

                 

                Can you please provide guidance, in this regard?

                 

                 

                 

                Thanks

                 

                 

                 

                Tom

                 

                 

                 

                Thomas W. McCarthy | Integration Manager

                 

                *Neo PLM * | 555 Long Wharf Drive | New Haven | CT | 06511 | USA

                 

                *T1 203 292 1835 x112 | M1 203 923 3579 *

                 

                thomas.mccarthy@neoplm.com <thomas.mccarthy@neoplm.com>

                 

                 

                 

                 

                 

                From: Marcos Vainer Loeff [mailto:pisquare@osisoft.com

                <pisquare@osisoft.com>]

                Sent: Thursday, May 5, 2016 1:24 PM

                To: Tom McCarthy <thomas.mccarthy@neoplm.com>

                Subject: Re:  - web service against PIOLEDB

                 

                 

                PI Square <https://pisquare.osisoft.com/?et=watches.email.thread>

                 

                 

                web service against PIOLEDB

                 

                reply from Marcos Vainer Loeff

                <https://pisquare.osisoft.com/people/mloeff?et=watches.email.thread> in *PI

                Developers Club* - View the full discussion

                <https://pisquare.osisoft.com/message/65309?et=watches.email.thread#comment-65309>

                  • Re: web service against PIOLEDB
                    Marcos Vainer Loeff

                    Sure, which PI OLEDB Enterprise version are you using?

                     

                    2012 or 2016?

                     

                    PI OLEDB Enterprise 2016 was just released!

                     

                    I will try to reproduce your issue!

                      • Re: web service against PIOLEDB
                        TomMcCarthyNeoPLM

                        2016…

                         

                         

                         

                        Thanks…

                         

                         

                         

                        Thomas W. McCarthy | Integration Manager

                         

                        *Neo PLM * | 555 Long Wharf Drive | New Haven | CT | 06511 | USA

                         

                        *T1 203 292 1835 x112 | M1 203 923 3579 *

                         

                        thomas.mccarthy@neoplm.com <thomas.mccarthy@neoplm.com>

                         

                         

                         

                         

                         

                        From: Marcos Vainer Loeff

                        Sent: Thursday, May 5, 2016 3:07 PM

                        To: Tom McCarthy <thomas.mccarthy@neoplm.com>

                        Subject: Re:  - web service against PIOLEDB

                         

                         

                        PI Square <https://pisquare.osisoft.com/?et=watches.email.thread>

                         

                         

                        web service against PIOLEDB

                         

                        reply from Marcos Vainer Loeff

                        <https://pisquare.osisoft.com/people/mloeff?et=watches.email.thread> in *PI

                        Developers Club* - View the full discussion

                        <https://pisquare.osisoft.com/message/65312?et=watches.email.thread#comment-65312>

                          • Re: web service against PIOLEDB
                            Marcos Vainer Loeff

                            Also, could you please provide your C# code? You just need to send the minimum code snippet for us to reproduce your issue.

                              • Re: web service against PIOLEDB
                                TomMcCarthyNeoPLM

                                Hi Marcos,

                                 

                                 

                                 

                                Here is the essence of it.

                                 

                                 

                                 

                                string 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 )";

                                 

                                 

                                 

                                            OleDbConnection connection = new OleDbConnection( $"Provider =

                                PIOLEDB; Data Source = OSI_PI2 ; Initial Catalog = NeoPLM; User ID =

                                piadmin; Password =; Log File = c:
                                temp
                                pioledb.log;" );

                                 

                                 

                                 

                                            OleDbDataAdapter oledbDataAdapter = new OleDbDataAdapter(

                                query, connection );

                                 

                                 

                                 

                                            DataSet dataResults = new DataSet();

                                 

                                 

                                 

                                         oledbDataAdapter.Fill(dataResults, "PI");

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                From: Marcos Vainer Loeff

                                Sent: Thursday, May 5, 2016 3:47 PM

                                To: Tom McCarthy <thomas.mccarthy@neoplm.com>

                                Subject: Re:  - web service against PIOLEDB

                                 

                                 

                                PI Square <https://pisquare.osisoft.com/?et=watches.email.thread>

                                 

                                 

                                web service against PIOLEDB

                                 

                                reply from Marcos Vainer Loeff

                                <https://pisquare.osisoft.com/people/mloeff?et=watches.email.thread> in *PI

                                Developers Club* - View the full discussion

                                <https://pisquare.osisoft.com/message/65313?et=watches.email.thread#comment-65313>

                                • Re: web service against PIOLEDB
                                  TomMcCarthyNeoPLM

                                  This is the full log.

                                   

                                   

                                   

                                  05-May-2016 12:36:10.004 PROVIDER_INIT PCSPool 12060 10 23744 Data Source

                                  IDBInitialize Initialize User ID = "piadmin"; Initial Catalog = "NeoPLM";

                                  Data Source = "OSI_PI2"; Window Handle = "0"; Prompt = "4"; Extended

                                  Properties = ""; Connect Timeout = "0"; Integrated Security = ""; Command

                                  Timeout = "-1"; Log Level = "0"; Log File = "c:\temp\pioledb.log"; Shorten

                                  Primary Keys = "0"; Always Return Rowset = "0"; Defer Execution = "0";

                                  Identifier Prefixes = "0"; Disable Server Selection = "0"; Time as Double =

                                  "0"; Optimization Log Limit = "100"; Session ID = "1"; Keep Default

                                  Ordering = "-1"; Session Pipelines = "1"; Current Time Precision = "0";

                                  Function Errors as NULL = "-1"; Cancel On Low Resources = "-1"; Show Hidden

                                  Metadata = "0"; Schema Name = ""; Default Timestep = "1h"; Integers as

                                  Value = "0"; Timestamp Interval Start = "0"; Support PI Wildcards = "-1";

                                  Time Zone = "Local"; Sync Calls = "0"; Connection Type = "PreferPrimary";

                                  Max Cache Size = "100000"; Server Log = "";

                                   

                                  05-May-2016 12:36:10.56 PROVIDER_INIT PCSPool 12060 10 23744 Data Source

                                  IDBInitialize Initialize User ID = piadmin; Init Catalog = NeoPLM; Data

                                  Source = OSI_PI2; Connection Timeout = 0; Command Timeout = -1; Log Level =

                                  0; Log File = c:\temp\pioledb.log; Shorten Primary Keys = False; Always

                                  Return Rowset = False; Defer Execution = False; Identifier Prefixes =

                                  False; Disable Server Selection = False; Time as Double = False;

                                  Optimization Log Limit = 100; Session ID = 1; Session Pipelines = 1;

                                  Integrated Security = ; Keep Default Ordering = True; Cancel On Low

                                  Resources = True; Evaluation Errors As Warnings = True; Default Timestep =

                                  1h; Integers as Value = False; Timestamp Interval Start = False; Support PI

                                  Wildcards = True; Time Zone = Local; Current Time Precision = 0; Show

                                  Hidden Metadata = False; Integrated Security =  ;Connection Type =

                                  PreferPrimary

                                   

                                  05-May-2016 12:36:10.56 PROVIDER_INIT PCSPool 12060 10 23744 Data Source

                                  IDBInitialize Initialize OLE DB Provider Version = 3.4.1.28; Server Version

                                  = 3.4.390.16; PI SDK Version = 1.4.4.486;

                                   

                                  05-May-2016 12:36:10.601 SQL_ENGINE_INFO_1 PCSPool 12060 10 23744 SQL

                                  Engine   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:36:10.602 ERROR_RECORD PCSPool 12060 10 23744 Command

                                  ICommand Execute DB_E_ERRORSINCOMMAND -   Syntax error

                                  near 'left'.

                                   

                                   

                                   

                                   

                                   

                                   

                                   

                                  From: Marcos Vainer Loeff

                                  Sent: Thursday, May 5, 2016 3:47 PM

                                  To: Tom McCarthy <thomas.mccarthy@neoplm.com>

                                  Subject: Re:  - web service against PIOLEDB

                                   

                                   

                                  PI Square <https://pisquare.osisoft.com/?et=watches.email.thread>

                                   

                                   

                                  web service against PIOLEDB

                                   

                                  reply from Marcos Vainer Loeff

                                  <https://pisquare.osisoft.com/people/mloeff?et=watches.email.thread> in *PI

                                  Developers Club* - View the full discussion

                                  <https://pisquare.osisoft.com/message/65313?et=watches.email.thread#comment-65313>

                                • Re: web service against PIOLEDB
                                  Marcos Vainer Loeff

                                  Hi Tom,

                                   

                                  The first thing to do is to test using the sample query provided by the PI SQL Commander. In my case:

                                   

                                  SELECT p.*
                                   FROM [AFSDKWorkshop].[Asset].[ElementHierarchy] eh
                                   INNER JOIN [AFSDKWorkshop].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID
                                   CROSS APPLY [AFSDKWorkshop].[Data].[Plot] 
                                   (
                                      ea.ID, -- ElementAttributeID
                                      N'*-1h', -- StartTime
                                      N'*', -- EndTime
                                      100 -- IntervalCount
                                   ) p
                                   WHERE eh.Path = N'\'
                                   OPTION (FORCE ORDER, EMBED ERRORS)
                                  

                                   

                                  I've used this SQL code on a C# Console Application and it worked fine:

                                   

                                     string query = "SELECT ea.*, p.* FROM [AFSDKWorkshop].[Asset].[ElementHierarchy] eh INNER JOIN [AFSDKWorkshop].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID CROSS APPLY [AFSDKWorkshop].[Data].[Plot] (   ea.ID,   N'*-1h',  N'*', 100) p WHERE eh.Path = N'\\Cities\\' OPTION (FORCE ORDER, EMBED ERRORS)";
                                              OleDbConnection cnn = new OleDbConnection("Provider=PIOLEDBENT.1; Data Source=MARC-PI2014; Integrated Security=SSPI;");
                                              OleDbCommand cmd = new OleDbCommand(query, cnn);
                                              try
                                              {
                                                  cnn.Open();
                                                  if (query.Substring(0, 6).ToUpper() == "SELECT")
                                                  {
                                                      OleDbDataAdapter dta = new OleDbDataAdapter(cmd);
                                                      DataSet ds = new DataSet();
                                                      dta.Fill(ds, "Values");
                                                      Console.WriteLine("OK Query");
                                                  }
                                                  else
                                                  {
                                                      cmd.ExecuteNonQuery();
                                                      Console.WriteLine("OK Non Query");
                                                  }
                                  
                                  
                                              }
                                              catch (OleDbException ex)
                                              {
                                                  Console.WriteLine(ex.Message);
                                              }
                                              catch (Exception ex)
                                              {
                                                  Console.WriteLine(ex.Message);
                                              }
                                              finally
                                              {
                                                  cnn.Close();
                                              }
                                          
                                  

                                   

                                  I guess now you should be able to make your app work fine.

                                   

                                  Please let me know the results!

                                  1 of 1 people found this helpful
                                    • Re: web service against PIOLEDB
                                      TomMcCarthyNeoPLM

                                      This is the query that I was using.  Please note I intend to put the neoplm

                                      in the connection string as a catalog.  Unless I am missing something, all

                                      I am doing beyond the basics is to

                                       

                                      Left outer join the EU table.  Perhaps you can test my query and maybe

                                      restructure it a bit to make it work.  It does work in SQL commander.

                                       

                                       

                                       

                                      Thanks…

                                       

                                       

                                       

                                      Tom

                                       

                                       

                                       

                                       

                                       

                                      use neoplm

                                       

                                       

                                       

                                       

                                       

                                      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)

                                       

                                       

                                       

                                       

                                       

                                       

                                       

                                       

                                       

                                      From: Marcos Vainer Loeff

                                      Sent: Thursday, May 5, 2016 4:42 PM

                                      To: Tom McCarthy <thomas.mccarthy@neoplm.com>

                                      Subject: Re:  - web service against PIOLEDB

                                       

                                       

                                      PI Square <https://pisquare.osisoft.com/?et=watches.email.thread>

                                       

                                       

                                      web service against PIOLEDB

                                       

                                      reply from Marcos Vainer Loeff

                                      <https://pisquare.osisoft.com/people/mloeff?et=watches.email.thread> in *PI

                                      Developers Club* - View the full discussion

                                      <https://pisquare.osisoft.com/message/65314?et=watches.email.thread#comment-65314>

                                        • Re: web service against PIOLEDB
                                          Marcos Vainer Loeff

                                          Hi Tom,

                                           

                                          Why don't you use the UOMAbbreviation SQL method? Here is an example:

                                           

                                          SELECT UOMAbbreviation(ea.defaultuomid) AS UOMAbbr, ea.*, p.*
                                           FROM [AFSDKWorkshop].[Asset].[ElementHierarchy] eh
                                           INNER JOIN [AFSDKWorkshop].[Asset].[ElementAttribute] ea 
                                           ON ea.ElementID = eh.ElementID
                                           CROSS APPLY [AFSDKWorkshop].[Data].[Plot] 
                                           (
                                              ea.ID, -- ElementAttributeID
                                              N'*-1h', -- StartTime
                                              N'*', -- EndTime
                                              100 -- IntervalCount
                                           ) p
                                           WHERE eh.Path = N'\Cities\' AND
                                           ea.Name='Temperature'
                                           OPTION (FORCE ORDER, EMBED ERRORS)
                                          

                                           

                                          I have tested in C# and it worked fine!

                                           

                                          Let me know the results!