7 Replies Latest reply on Dec 13, 2011 9:35 AM by MvanderVeeken

    Scripting MDB edits

    AlistairFrith

      Is it possible to edit the MDB from the command-line?

       

      I have looked in the PIConfig help and I can't see any tables that look like the MDB. I need to be able to change some properties from within a batch script so the Excel and SMT3 MDB Editor plugins are both unsuitable.

       

      Regards,

       

      --- Alistair.

       

       

        • Re: Scripting MDB edits
          andreas

          Unfortunately you do not have access to the MDB from PIConfig. You would need to write a little tool in PISDK to perform this

           

          Anyhow this seems more like a techsupport question.

            • Re: Scripting MDB edits
              AlistairFrith

              Yea, I suppose it is. Trying through ipisql.exe now but that is not recognising pimodule..piproperties, although th PI OLEDB Tester tool does. Wierd.

               

              I'll ask Techsupport.

               

              --- Alistair

                • Re: Scripting MDB edits

                  ipisql has to do with the old PI SQL Subsystem and, by extension, to the (deprecated) PI ODBC Driver. These do not have knowledge about PI Module Database.

                   

                  PI OLEDB Tester, on the other, uses the classic PI OLEDB Provider under the hood, which does expose the PI Module Database tables. PI OLEDB queries or PI SDK commands are your best options here.

                   

                  Either way, you might want to look into making use of the PI Asset Framework (AF) instead of MDB... this is the preferred (and the long-term) approach and is available through the PI OLEDB Enterprise provider.

                    • Re: Scripting MDB edits

                      PISDK or PI OLEDB within PowerShell or IronPython, etc. may be a particular way of doing this. (AFSDK if dealing with AF)

                       

                      You could call scripts from within your batch script. For instance, powershell .\scriptname.ps1 or ipy scriptname.py. Of course, you could even go directly with either one but it's always a choice.

                       

                      Example in PowerShell. In this case we change the name and also some numeric attribute in the MDB, using PISDK.

                       

                      [reflection.assembly]::loadwithpartialname("OSIsoft.PISDK") | out-null
                      $pisdk = new-object PISDK.PISDKClass;

                      $mdb = $pisdk.Servers.DefaultServer.PIModuleDB.PIModules
                      $myModule = $mdb.Item('quickTest')


                      foreach( $currentModule in $myModule.PIModules ){
                        $itsProperies = $currentModule.PIProperties
                        $itsProperies.Item('Name').Value += ' some extra text'
                        $itsProperies.Item('SomeNumeric').Value *= 1.50
                      }

                       

                      As Steve Pilon mentioned eventually you'll want to use AF SDK. Luckily for you, you can still use PowerShell or IronPython.

                       

                      In IronPython's case you'll use something like this. In this case, we're only printing stuff but you'll change that operateOnAttributes function if you wanted to modify them.

                       

                      importclr,sys
                      clr.AddReferenceByPartialName('OSIsoft.AFSDK')
                      fromOSIsoftimport AF

                      srv = AF.PISystems().DefaultPISystem
                      db = srv.Databases.DefaultDatabase
                       
                      defoperateOnAttributes( attribute ):
                          format ='@%s\t%s ==> %s\n'
                          path, name = attribute.GetPath().split('|')
                          path =':'.join( path.split('\\')[-2:] )
                          value = attribute.GetValue().ToString()
                          sys.stdout.write( format % (path, name, value) )

                      for element in db.Elements:
                          map( operateOnAttributes, (attrb for attrb in element.Attributes ) )
                      srv.Disconnect()

                       

                      In PowerShell's case I have 'function execute-pioledb ( [string]$query, [string]$server = $defaultPIserver )' that executes PIOLEDB queries via ADODB on the command line. This I have put in the profile (%USERPROFILE%\My Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1) so that I can use it whenever I'm in a session. It's kinda useful -specially when piping the results of select statements.

                       

                      Regards,
                      Luis Moux

                    • Re: Scripting MDB edits
                      andreas

                      For PI OLEDB you should take a look at PIPC\OLEDB\Tutorials\MDB-BDB Tutorial - MyPISQL.exe will help you there (should have thought about that earlier)