5 Replies Latest reply on Feb 25, 2011 6:30 PM by Lonnie Bowling

    ACE to execute a SQL query?

    Lonnie Bowling

      Hi Everyone,

       

      I have been presented with an interesting challenge and wanted to get some opinions.  My client needs to update PI tags from an Oracle database.  I know I could use the RDMS interface, but am considering ACE.  The issue is that the data from the database has to be processed with some fairly complex logic before updating the tags and doing this in VB.net would be much simple that creating some nasty SQL statements or stored procedures.  Could I just setup an ACE module and schedule it every 10 minutes, connect to the database, look for new data, process and update output tags?  Seems simple enough,  how would this approach compare to using the RDMS interface (I don't have any direct experience with it)?

       

      Thanks,

       

       

       

      Lonnie

        • Re: ACE to execute a SQL query?

          Yes, simple enough. Just use the ADO.NET objects to connect to the database and read the data. You can take a look a the "PI OLEDB and Beyond" webinar under the vCampus Auditorium; it contains the "Custom PI OLEDB Tester" project demonstrated during the webinar.

           

          This project uses the "generic OLE DB" data provider objects (e.g. OleDbCommand, OleDbConnection). You could also use those for Oracle (with the appropriate connection string), or use the Oracle-specfic objects provided in the .NET Framework (e.g. OracleCommand, OracleConnection).

           

          The webinar referenced above should give you a good quick start with ADO.NET, as well as the PI Application Development training course materials (see under the vCampus Training Center). I'm sure you'll find a lot of resources on the MSDN website and forums, but you could also post your ADO.NET questions in the "General .NET Development" forum, in case vCampus is favourite venue

           

          With all that said, why PI ACE rather than the off-the-shelf PI RDBMS Interface?

            • Re: ACE to execute a SQL query?
              Lonnie Bowling

              I have not ruled out PI RDBMS, but like the idea of being able to do some logic within .net.  I still have to look into the capabilities of that interface.

               

              Thanks for the information Steve!

               

              Lonnie

                • Re: ACE to execute a SQL query?
                  mhalhead

                  Lonnie,

                   

                  We have use ACE to read data from an SQL database and push the data into PI. I must admit that this is not my preferred method of doing this; I prefer to use the RDBMS interface to get the data into PI. With RDBMS you can do some pretty complex processing using SQL stored procedures; you could go nuts and write a CLR stored procedure or table view (i.e. .NET code that runs as a stored procedure or view on the SQL server). Using the stored procedure with a distributor strategy (see the RDBMS manual) is my preferred approach and has proven very reliable.

                   

                  ACE is a wonderful product but it must be the most abused OSI one. We've used it for everything short of making coffee.