4 Replies Latest reply on Jul 30, 2013 11:09 AM by rene

    Import Custom Database Objects via AFSDK

    dmparamonov@croc.ru

      Hi Team

       

      There is a problem with importing custom database objects (such as Views, Transpose Functions and Transpose Function Tables which are stored in the PI AF Configuration database) to another system via AFSDK. I have a program which exports AF Database to xml file via ExportXml function and imports it to another system via ImportXml function. But all my views and tables developed in PI SQL Commander are disappeared. I know that we have an opportunity to import custom database objects in PI SQL Commander using import wizard but it is a manual operation which seems to be not applicable. I need to do it by means of my program. I tried to export Configuration database to xml and to import it to another system via ImportXml function but such way does not work properly.

       

      Does anyone know the proper way of importing custom database objects not using PI SQL Commander?

       

      Thank you for your replies.

       

       

        • Re: Import Custom Database Objects via AFSDK
          xwang

          Hi,

           

          I am afraid that your views which developed from PI SQL Commander cannot be exported into the .xml file with the AF server contains.  Actually, the views are not stored in the AF server, but in the PI OLEDB server.  That is same as PI points values are not stored in AF server, but in PI server.  You cannot see any PI value stored in PIFD database, and you cannot see any view in PIFD database as well.  Therefore, the "Export" function for AF server could just export the PIFD contains.

           

          Xi Wang

           

          v-Campus team

           

           

            • Re: Import Custom Database Objects via AFSDK
              Bannikov

              Hello Xi,

               

              I suppose you're wrong, because there's no PI OLEDB server at all (as I know) - only PI OLEDB Enterprise Agent which hasn't storing features. All views and transposed function definitions are stored inside PIFD SQL database, in Configuration AF database (you can simply see it here).

                • Re: Import Custom Database Objects via AFSDK

                  Sergey is right. The Configuration database houses the PI SQL Commander configuration. You'll see the elements created in there, and for the view definitions etc you'll see them in the the Extended Properties of the element under the "Asset" or "data" ... branches of the hierarchy.

                   

                  The Configuration database export/import works fine for me:

                   
                  string exp = db.ExportXml(db.Elements, PIExportMode.StrongReferences & PIExportMode.NoUniqueID);
                  
                  ...
                  
                  db.Refresh();
                  int items = db.ImportXml(db.Elements, (PIImportMode.AllowCreate | PIImportMode.AllowUpdate | PIImportMode.AutoCheckIn), exp);
                  

                   
                  Remember if you are exporting from PISystem1 and importing to PISystem2 then the Child Elements of the "OSIsoft\PI SQL\Database Objects" Element will be an Element matching the name(s) of the AF Database. They need to exist on PISystem2 to show up in PI SQL Commander.

                    • Re: Import Custom Database Objects via AFSDK
                      rene

                      Hello all,

                       

                      Just for clarification: All custom database objects are saved in PIFD database. To successfully move such objects from one PI System to another the following two conditions have to be fulfilled:

                       

                      - catalog(s) with the same name must exist on the target system because the custom database objects reference the catalog name

                       

                      - the same is true for element templates which are referenced by transposed functions. Element templates with the same name must exist on the target system

                       

                      I just tested export/import on my system using PI System Explorer and it works. The way provided by Rhys was successfully tested in the past and should work too

                       

                      - open PI System Explorer and connect to the Configuration database

                       

                      - right click on the "Elements" node in PI System Explorer and select Export To File...

                       

                      - connect PI System Explorer to the target PI System

                       

                      - connect to the Configuration database

                       

                      - right click on the "Elements" node in PI System Explorer and select Import From File...

                       

                      Regards,

                       

                      Rene Otisk