10 Replies Latest reply on Jul 27, 2010 12:13 PM by andreas

    Using PIOLEDB in ACE

    cjrancur

      I have questions about using PIOLEDB within ACE.  Particularly, I'd like to know suggestions for dealing with memory issues associated with the unmanaged code behind OLEDB in vb.net.  With unmanaged code outside of ACE, I've learned to use the dispose method on the connection, command, dataadapter, and dataset objects when I'm done with them, to prevent memory leaks in the native part of the OLEdB calls. 

       

      Here's what puzzles me.  For PI SDK connections, I have heard recommendations that the connection should be made once, and only revived if it is lost for some reason.  However, PIOLEDB uses the PI SDK underneath PIOLEDB, and most ODBC/OLEDB recommendations are to connect and close rapidly, then dispose of objects no longer being used.  So how should connections to PIOLEDB be handled?

       

      Now add the ACE layer into the picture.  If PIOLEDB calls are made at each execution of an ACE module, won't there be a gradual memory leak if the objects are not disposed each time?  But if PIOLEDB relies on an SDK connection underneath, and an SDK connection should be left open, how should these apparently conflicting rules of thumb be reconciled in this case?

       

      Is it worth the trouble to rewrite an ACE exe that uses PIOLEDB, in order to perform similar operations with loops and with the SDK instead? 

       

      I have attached a code snippet from within an existing ACE exe I use.  Yesterday, I considered adding the cnn.close statement, shown in bold and dated 7/22/2010, in the Finally segment of the try/catch block within a using statement for the OLEDB connection.  Do you think addition of that statement is a good thing, a bad thing, or maybe it's irrelevant and unimportant?  Would you recommend a different structure for using OLEDB within ACE?  Should PIOLEDB be avoided within ACE?

       

       

       


      Using cnn As New OleDb.OleDbConnection()
         Dim cmd As OleDb.OleDbCommand = cnn.CreateCommand 

         Try
            cnn.ConnectionString = My.Settings.PIOLEDBConnectionString
            cnn.Open()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = strUpdateAttributes 

            '"UPDATE pipoint..vfclass " & "SET " & strSettings1 & strSettings2 & strSettings3 & strSettings4 & " " & "WHERE tag='" & TestResultsRow.Item "TagName").ToString & "' "
           
      'CJR 1/13/2010, Execute the OleDB UPDATE to PI values if the connection to the PI Historian is good, else try
           
      'to reconnect and send message to PI control server if PI Historian server is inaccessible.
           
      If cnn.State = ConnectionState.Open Then
              
      cmd.ExecuteNonQuery()
           
      Else
              
      cnn.Open() 
              
      'try once more to open connection to historian PI server

              
      If cnn.State = ConnectionState.Open Then
                 
      cmd.ExecuteNonQuery()
              
      Else
                 
      SendMessageToPI("PI Server "
                                  &
      My.Settings.PIHistorianServerName
                                  &
      " cannot be reached via PIOleDB. Tagname "
                                  & TestResultsRow.Item(
      "TagName").ToString
                                  &
      " has not yet been sent to PI.",
                                  
      "Update_PI_Attributes")
              
      End If
           
      End If

         Catch
      ex As Exception
           
      SendMessageToPI(ex.Message, "Update_PI_Attributes ex 1")
         Finally
           
      cnn.Close()
           
      'Added 7/22/2010
           
      cmd.Dispose()
         End Try
      End Using

       

        • Re: Using PIOLEDB in ACE
          cjrancur

          Sorry, I haven't figured out how to post code in a way that's legible.  Why do all those spaces enter the picture when I cut and paste from vb.net?  What procedure do you recommend for posting code on this forum?

            • Re: Using PIOLEDB in ACE
              cjrancur

              Now I recall what I have done in the past to post code legibly.  I've pasted into notepad from dotnet, and then into this forum.  Here's the code entered using that method.  What method do the rest of you use?

              Using cnn As New OleDb.OleDbConnection()
                              Dim cmd As OleDb.OleDbCommand = cnn.CreateCommand
                              Try
                                  cnn.ConnectionString = My.Settings.PIOLEDBConnectionString
                                  cnn.Open()
                                  cmd.CommandType = CommandType.Text
                                  cmd.CommandText = strUpdateAttributes '"UPDATE pipoint..vfclass " & "SET " & strSettings1 & strSettings2 & strSettings3 & strSettings4 & " " & "WHERE tag='" & TestResultsRow.Item("TagName").ToString & "' "

                                  'CJR 1/13/2010, Execute the OleDB UPDATE to PI values if the connection to the PI Historian is good, else try
                                  'to reconnect and send message to PI control server if PI Historian server is inaccessible.


                                  If cnn.State = ConnectionState.Open Then
                                      cmd.ExecuteNonQuery()
                                  Else
                                      cnn.Open() 'try once more to open connection to historian PI server
                                      If cnn.State = ConnectionState.Open Then
                                          cmd.ExecuteNonQuery()
                                      Else
                                          SendMessageToPI("PI Server " & My.Settings.PIHistorianServerName & " cannot be reached via PIOleDB. Tagname " & TestResultsRow.Item("TagName").ToString & " has not yet been sent to PI.", "Update_PI_Attributes")
                                      End If
                                  End If

                              Catch ex As Exception
                               
                                  SendMessageToPI(ex.Message, "Update_PI_Attributes ex 1")
                                 
                              Finally
                                  cnn.Close() 'Added 7/22/2010
                                  cmd.Dispose()
                              End Try

                          End Using

                • Re: Using PIOLEDB in ACE
                  mhalhead

                  Carrie,

                   

                  From a general programming point of view a database connection is considered an expensive and limited resource hence the recommendations to close the connection as quickly as possible. But it is very dependent on what you're doing; if your ACE module ran every second then opening and closing a database connection time would probably not work. A common construct is to use a pool of connections. One thing you must do is handle exceptions; if you aren't closing the connection everytime you must handle exception where the connection has failed; be careful not to simple create a new connection without cleaning up the old one otherwise you would have a nasty memory leak.

                   

                  The next part is a question. Why would you use PI-OLEDB in ACE rather than PI-SDK? My personal preference is to use the SDKs (AF, PINS, PI, ...) rather than things like PI-OLEDB. Don't get me wrong PI-OLEDB has its place but I don't believe that place is in software that could just as easily use the SDK. The PI SDK is not much more complex and the add complexity is definitely balanced by the additional performance, flexibility and power. As a note you will have to manage SDK connections as well.

                    • Re: Using PIOLEDB in ACE
                      hanyong

                      Hi Carrie,

                       

                      The reason for the problem with code formatting is because of the formatting tags behind that text. You can see these if you edit the HTML source for the code (look out for the "HTML" button when editing or writing a post), try it by editing your 1st post

                       

                      You can remove these formatting tags by editing the HTML. Pasting to notepad before pasting it onto the post will help to remove some of these formatting tags.

                       

                      On top of that, you can enclose your code with [coode]...[/coode] (double "o" is by design, to prevent the instruction from being executed...). This indicates for that part of the post, it is a code snippet. I've edited one of your post with this, so you can take a look at it.

                       

                      Michael Halhead

                      A common construct is to use a pool of connections.

                      For this, you can take a look at Resource Pooling, which is mentioned in the PI OLEDB User Manual in the "OLE DB Service Providers" section.

                       

                      I'm also interested to find out why you chose to use PI OLEDB for what you are doing in the ACE as well. Perhaps you can share this with us.

                        • Re: Using PIOLEDB in ACE
                          cjrancur

                          In answer to Han and Michael,

                           

                          Using PIOLEDB rather than PISDK was required by an IT manager less familiar with PI.  The manager required PIOLEDB because the rest of the program used ODBC to obtain data from relational databases.  His subordinate coded the orginal VB6 standalone executable. I provided subroutine examples for this group's use in writing data to PI in a standalone VB6 application.

                           

                          Later, this executable became part of my maintenance load, and I moved the code into ACE.  I have rewritten some, but not yet all of the previous PIOLEDB code to the SDK. I needed to utilize structured annotations in this ACE executable.  Structured annotations were not supported by PIOLEDB.

                           

                          My next step will be to weigh the advantages/disadvantages of finishing the rewrite from PIOLEDB to PISDK in this particular case.  The subroutine I posted was part of my rewrite from VB6 standalone to vb.net ACE. I carefully cleaned up connections and database objects in that subroutine.  But, elsewhere in the ACE code, I left one database connection open from the original code.  I did not know what it was used for. Last week, I got rid of that open connection. It was apparently not used for anything.  CPU usage on the ACE server dropped by 30-50%, and a series of pimsglog messages that I thought were normal, went away.

                          • Re: Using PIOLEDB in ACE
                            cjrancur

                            I tried editing my first post to identify the code snippet.  It does not look right.  What am I missing?

                              • Re: Using PIOLEDB in ACE
                                mhalhead

                                The tags you used are spelt incorrectly as Han mentioned, change coode to code; everyone intentionally misspells the tags to prevent the forum software interpreting it as an actual tag.

                                  • Re: Using PIOLEDB in ACE
                                    cjrancur

                                    I don't know XML, and haven't made the edits work yet.  I've tried <coode> and [coode] both, with code spelled correctly. I did not succeed in editing the first post.  Is there a  document somewhere, maybe in a white paper or an archived webinar, that shows how to paste code from the visual studio IDE into a vCampus posting? 

                                     

                                    I was able to remove the extra line feeds, one at a time.  But I don't understand how to use the "coode" suggestions.