25 Replies Latest reply on Oct 15, 2018 7:36 PM by Adam Milton-Barker

    Small PI Server setup and retrieving data using JDBC

    Adam Milton-Barker

      Hi guys, I have installed the packages mentioned in the docs for a small server (All on one machine):

       

      • SQL Express
      • Pi AF Server / PI Data Archive
      • Pi OLEDB Enterprise
      • PI SQL DAS

       

      The following we did not install

      • Notifications
      • Analytics
      • Crawler

       

      Our aim is to setup a simple server with some test data and create software (Java/Linux) using the JDBC driver to collect data from the server. The documentation has not been too helpful and fairly confusing with out of date information / looping back to the same place also documentation takes many minutes to load, sometimes failing to load (You can see a number of errors in developer console).

       

      One issue so far is when testing the PI SQL Commander Lite tool, when looking at the Object Explorer I do not see what is shown in the documentation:

       

      Screen Shot 2018-10-08 at 11.01.20.png

      You can see above  that the names are totally different, however if I expand the directories it does look similar layout, but following the guide for queries returns no data. I was wondering if this was a bug or maybe I had missed a step along the way. If data is not provided do you know where we can access some test data to import into the database ?

       

      If anyone can provide simple steps to completing the server set up, or their experiences with developing Java apps with JDBC it would be very helpful, thanks in advance.

        • Re: Small PI Server setup and retrieving data using JDBC
          John Messinger

          Adam,

           

          What was the exact query that you tried executing - can you post it here? Are you copying an example query from the documentation and executing that? As you appear to be running queries against the PI OLEDB Enterprise provider this implies that there is an AF asset structure created on your system that is the target for your query. Does this asset hierarchy correspond with the assets you are querying?

           

          Regarding your issues with the documentation, what version of PI SQL Commander Lite are you using as opposed to the version you are referencing in the online documentation? Differences in the versions here may well account for the minor differences you see between what you have installed and what you see in the documentation you are using.

            • Re: Small PI Server setup and retrieving data using JDBC
              Adam Milton-Barker

              Hi thanks for the reply John, the installation I have is outlined specifically in the original post, that is the exact steps I have taken following the documentation. I am following the documentation PI OLEDB Enterprise (run sample queries). Firstly I do not see what is shown in the documentation, secondly the steps shown in the documentation do not work, when I execute a predefined query as shown in the documentation, no data is returned. We have no assets at the moment, I was following the documentation and I was under the impression that we had some test data in there as the documentation seemed to not mention adding any data.

               

              RE PI SQL Commander Lite I am using the one that is installed with the PI software, PI SQL Commander Lite 2017 R2.

               

              My questions are what is the suggested way to add data into the system, IE do you have a simulator program we can use for entering real time data, or is there any specific way to get data in there, and also if there is any test data already available as it seems to show in the documentation.

               

              The next question is having set up as in the information provided in the original post, would it be time to start to install the JDBC driver on Linux and begin coding our software.

               

              Sorry for the questions but I have spent that last number of days reviewing the documentation but not able to move forward as of yet, this is the first time working with PI also. Thanks again for the help.

                • Re: Small PI Server setup and retrieving data using JDBC
                  John Messinger

                  Hi Adam,

                   

                  Firstly it's important to note that a freshly installed PI system does not come with any pre-loaded data, as it is a real-time data system. A vanilla installation will have 10 test PI points (also known as tags) defined, and these points receive data generated by a couple of random data simulator interfaces installed as part of the system. As a real-time system, new data coming in will replace existing data which is typically stored in archives to become historical data. Your query using the PI OLEDB Classic provider against the piavg table is querying one of these simulation tags, which is why you were able to see a result returned.

                   

                  A vanilla installation of PI does not include any pre-defined asset hierarchies in the Asset Framework (PI AF). There are several example kits available that you can download and import into your PI AF instance - these can be found in the AF Community Library. These databases include configured analytics that when enabled will generate simulation data appropriate to the examples. There is also a sample database called NuGreen that comes with the PI SQL Commander tool - you will find this in the Query Compendium, and the actual xml file for import under the %PIHOME64%\SQL\SQL Commander\PI SQL Query Compendium\PI OLEDB Enterprise directory on the machine where PI SQL Commander is installed. All of the example queries in the Query Compendium under the 'PI OLEDB Enterprise' node can be executed against this database and will return results. Remember that these are examples of how to query the PI schemas, and ultimately you need to adapt these to your own databases and asset hierarchies.

                   

                  As for using the JDBC driver in Linux, you also need to first install and configure the PI SQL Data Access Server (PI SQL DAS) component on a Windows machine, as this is the gateway between the Linux box and PI, and instantiates the appropriate OLEDB Provider to actually execute your query. You've previously indicated that this is already installed. Regarding code samples, there are a couple of sample applications with source code that come with the Linux PI-JDBC driver package - see the Live Library documentation for information as to the exact location of these.

                  2 of 2 people found this helpful
                    • Re: Small PI Server setup and retrieving data using JDBC
                      Adam Milton-Barker

                      OK thank you, this was helpful, I am going to be writing a quick guide soon which I will upload to your community once complete. I had found the source code also they are located in the samples directory of the default installation location, you can execute from the bin folder, this was what I was referring to above where I mentioned GetSnap.

                       

                      I located the NuGreen XML file, however again am unable to find the documentation of how to set up / import this data, I have searched your community and Google, please could you link me to the specific document regarding this. The following is pretty much the information I have found directing how to import etc, no actual steps for first time users:

                       

                      "To create the databases and to import the data from the xml files, use the PI System Explorer."

                        • Re: Small PI Server setup and retrieving data using JDBC
                          John Messinger

                          Have a look at the Live Library documentation here for specific instructions on this process. Essentially, the easiest way to import a database from XML is to open PI System Explorer and click on the Database button on the toolbar to open the 'Select Database' dialog. Right mouse click anywhere in the blank space of the dialog and select 'Import from File' from the context menu. You can then browse to your xml file and select it. You should also refer to this page in the documentation for information on the options presented to you by the Import dialog. As you are working with a vanilla installation, you may be presented with a message stating that only the Configuration database exists and asking if you want to create e new database. Click No, and then when PSE opens the Configuration database, follow the procedure I just outlined to create/import the NuGreen database, or any of the example kit databases you may care to work with.

                           

                          The Live Library contains the most up to date and complete documentation on the products you are working with, and supports searching. This can be a better option than searching Google, as I don't know how well it is indexed by Google. Also, OSIsoft's YouTube Learning Channel contains many videos that are specifically task focused on things just like this.

                          1 of 1 people found this helpful
                            • Re: Small PI Server setup and retrieving data using JDBC
                              Adam Milton-Barker

                              OK great thank you for the help! I will keep you updated John.

                              • Re: Small PI Server setup and retrieving data using JDBC
                                Adam Milton-Barker

                                Hi how are you, just an update on this I now have NuGreen imported and predefined queries now work, so the final issue (I hope) is that using the validation steps mentioned in the documentation, authentication fails with the error that PI DAS does not exist, this was using the server name for both required names, I am reviewing your information again to see if I can resolve.

                                 

                                Path for validation script opt/pipc/jdbc/Samples/GetSnap/bin

                                 

                                For clarification:

                                I have reviewed my notes made during installation and tested with the only names I provided during set up which do not work:

                                 

                                Enter Data Access Server name (required): OUR_WINDOWS_SERVER_NAME

                                Enter PI Data Archive name (required): OUR_WINDOWS_SERVER_NAME

                                Validate PI SQL DAS certificate? [No]: n

                                Use trusted connection? [No]: Yes

                                Enter the port number for the connection [5461]:

                                Enter PI JDBC log level [0]:

                                Enter the PI tag name/name pattern [sinusoid]: sin%

                                 

                                 

                                Arguments:

                                Data Access Server name: OUR_WINDOWS_SERVER_NAME

                                PI Data Archive name: OUR_WINDOWS_SERVER_NAME

                                Validate PI SQL DAS certificate: No

                                Use trusted connection?: Yes

                                Port number: 5461

                                Log level: 0

                                PI tag name/name pattern: sin%

                                 

                                java.sql.SQLException: Connection failed. Unknown PI SQL DAS: OUR_WINDOWS_SERVER_NAME

                                 

                                  • Re: Small PI Server setup and retrieving data using JDBC
                                    John Messinger

                                    OK, so looking at the exception message raises two questions for me:

                                     

                                    1. Is TCP port 5461 open in the Windows Firewall on your server running the PI SQL DAS?

                                    2. Can the PI SQL DAS Windows server name be resolved from your Linux machine? Have you tried using the IP address instead of the server name?

                                      • Re: Small PI Server setup and retrieving data using JDBC
                                        Adam Milton-Barker

                                        Thanks John my first query is whether it is correct to use the server name, I have searched through my installation notes, PI System Explorer and SQL Commander, I am unable to find the actual name of the PI SQL DAS, according to my notes I did not name it so I assumed it would be the same as the hostname. As I mentioned in this case I used the server name, the only name I have noted that I created myself was a database name.

                                         

                                        I will also check the port but as I followed the guide during installation I am assuming the port is correct, I will confirm on this also after lunch, thanks again for your time and help.

                                          • Re: Small PI Server setup and retrieving data using JDBC
                                            John Messinger

                                            Yes, you should use the server name (or IP address) of the host server for the PI SQL DAS name, likewise for the PI Data Archive name. The PI Data Archive name should correspond to a name that is found in the AFSDK Known Servers Table as defined on the PI SQL DAS machine. This will typically be the name of the PI Data Archive server that you entered when prompted during installation of the PI SQL DAS software.

                                              • Re: Small PI Server setup and retrieving data using JDBC
                                                Adam Milton-Barker

                                                Thanks John, I had used the default entries all the way through, the only name I created according to my notes was a database. I am trying to find the AFSDK known servers table, section I guess we can access this through SQL Commander. I had also gone back through the entire server installation attempting to find where PI SQL DAS name is defined but the option did not appear, I have now installed all packages with exception to analytics also during this move, when rerunning the PI DAS installer there is not the option to modify as there was in the server installation so unable to locate the name using that. Really I was not expecting it to be so hard to locate the names, possibly a simple "view properties" button would be useful in the future.

                                                • Re: Small PI Server setup and retrieving data using JDBC
                                                  Adam Milton-Barker

                                                  I have searched all tables in PI SQL Commander and do not see a table related to Known Servers. From what I gather from your comments I am using this correctly but it is not working, I remember a comment yesterday related to this I will check it out and reply back, thanks John.

                                                    • Re: Small PI Server setup and retrieving data using JDBC
                                                      John Messinger

                                                      Sorry for the confusion there, Known Servers Table is a bit of a misnomer here - it's not a SQL table that you will find via PI SQL Commander. It's the list of known connections to PI Data Archives and PI AF servers held by the underlying AF SDK (PI AF Client) on the Windows machine. Because you have everything installed on a single Windows machine, the Data Archive Server name will most likely just be the name of the Windows server itself.

                                                       

                                                      Yes, it appears you are using it correctly. As has been mentioned by myself and Gregor, check that the PI SQL DAS service is running, the Windows server server name is resolvable from your Mac, and also that TCP port 5461 is open on the Windows box, as the error you are reporting seems to be more related to an underlying networking/connectivity issue.

                                                        • Re: Small PI Server setup and retrieving data using JDBC
                                                          gregor

                                                          I agree with John that the Knowns Servers Table will not be the issue here but just to allow you to check what's in the KST .. let me start with sharing some background first.

                                                           

                                                          These days the majority of PI Clients depends on AF SDK and hence the AFClient setup kit is included with PI Client setup kits. Earlier releases of the AFClient kit also contained the PI SDK but since a few releases back, PI SDK is not included anymore because dependencies have been removed.

                                                           

                                                          PI SQL Data Access Server (OLEDB) (please do not confuse with PI SQL Data Access Server (RTQP)) depends on PI OLEDB Enterprise for access to the Asset Framework and on PI OLEDB (Classic) for access to the PI Data Archive. PI OLOEDB (Classic) is build against PI SDK and that's why in your case PI SDK is installed on your Windows box. This allows you to use PISDKUtility.exe to lookup KST entries. Below screenshot lists a PI Data Archive Collective (gb-pida1) and a non-redundant PI Data Archive (PIServer).

                                                           

                                                           

                                                          When only AFClient is installed, one can check the KST using PI System Explorer (PSE) which ships with the AFClient through the menu [File] -> [Connections …]. Besides just PI Data Archive connections, there are also AF Servers showing as connections. E.g. the information in the type column allows to distinguish what kind of server connection is listed.

                                                           

                                                          2 of 2 people found this helpful
                                                            • Re: Small PI Server setup and retrieving data using JDBC
                                                              Adam Milton-Barker

                                                              Hi Greg/ John Messinger, thanks for the information and sorry for the delayed reply, we can verify PI SQL Data Access Server (OLE DB) service is running and port 5461 is open for incoming connections on the Windows box, in addition we checked telnet settings, however we are unable to telnet to the machine. I have an open support ticket that has run alongside this thread also so I will resolve the issue with support to avoid this question becoming too bloated. Thanks for the help so far guys, will update this thread soon.

                                      • Re: Small PI Server setup and retrieving data using JDBC
                                        Lal_Babu_Shaik

                                        Hi Adam

                                         

                                        Could you please run query against both databases and see if you are getting results using PI OLEDB Enterprise(AF) and PI OLEDB Provider(PI Data Archive)?

                                         

                                        Request you to refer below JDBC examples for connecting to PI using DAS.

                                         

                                        PI JDBC

                                         

                                        Thanks,

                                        Lal