9 Replies Latest reply on Dec 11, 2018 11:19 AM by Adam Milton-Barker

    Queries from PI SQL Commander not working

    Adam Milton-Barker

      Hi guys I have completed the Web API program for now and working on a PI JDBC software, I have written code based around GetSnap which is working, however I wanted to show some examples of common queries to show some better examples, I have the Nugreen database installed and can successfully query data from there using the PI Web API software I created, however with PI JDBC using the following code I get the following error and cannot work out why as Nugreen database exists and is callable via Web API.

       

      SELECT Name, Description FROM NuGreen.Asset.Element WHERE Description LIKE ? (Using param N'Boiler%')
      

       

      [PIOLEDB] Catalog name 'NuGreen' is invalid.
        at com.osisoft.jdbc.StatementImpl.executeQueryInternal(StatementImpl.java:429)
        at com.osisoft.jdbc.StatementImpl.executeQuery(StatementImpl.java:408)
      

       

      In PI Web API, our path to anything Nugreen related was \\Nugreen\\Nugreen which is different to any of the documentation which specified to use \\Nugreen, I was wondering if there was a similar issue here, I have tried Nugreen.Nugreen with no luck. TIA.

        • Re: Queries from PI SQL Commander not working
          gregor

          Hi Adam,

           

          What version of PI JDBC for what platform are you using? The title of your question suggest you are using PI SQL Commander but I am uncertain if you may not be using a different client.

           

          What other PI SQL components do you have installed, what are their versions and are they installed remote to the PI JDBC client?

           

          PI SQL Commander is based Visual Studio Shell which is anything but a lightweight. For this reason, the PI SQL Team has replaced PI SQL Commander with PI SQL Commander Lite which is a WPF application with the same look and feel as SQL Commander but way more lightweight. However, bot products allow to establish a connection to recognized (installed) resources which refers PI SQL products available for the Windows platform. Under the assumption you are using PI JDBC for Windows you should be able to connect your AF Server which allows you to explore the schema. For existing tables, you could always right-click -> Execute predefined query to get a sample query. Also pretty useful is the Query Compendium available in PI SQL Commander and PI SQL Commander Lite.

            • Re: Queries from PI SQL Commander not working
              Adam Milton-Barker

              Sorry it is the latest version and it is Pi Commander Lite I retrieved the example queries from in Query Compendium and I am not using Windows currently the program is on Mac / Linux. I am accessing through a custom java application, using the query from GetSnap works fine but making any calls to Nugreen fails with the above errors. This is the same within PI SQL Commander Lite however retrieving info from Nugreen using the Java program I created using PI Web API works fine.

            • Re: Queries from PI SQL Commander not working
              vkaufmann

              Adam,

               

              Something is not adding up. The error you report would never be seen using PI SQL Commander since you cannot load the PI JDBC driver into PI SQL Commander. What errors if any do you get when running a query like this in PI SQL Commander? Note that the query above will not work directly in PI SQL Commander as it does not support the use of parameters.

               

              --Vince

                • Re: Queries from PI SQL Commander not working
                  Adam Milton-Barker

                  Hi sorry the query from PI SQL Commander is:

                   

                  SELECT Name, Description

                  FROM NuGreen.Asset.vElement

                  WHERE Description LIKE N'Boiler%'

                   

                  I added the parameterisation to fit in with preparedStatements and received the error above, additionally at the time I received the error directly in PI Commander that Nugreen did not exist the exact error I believe was Nugreen not valid, this was on my partners computer however when I  try it on Commander it works OK, but I do remember seeing it as well, I will have to update this question when it occurs again, however the actual issue is the error trying to retrieve through JDBC.

                   

                  I have now tried without prepared statements and the issue is still the same:

                   

                  java.sql.SQLException: [PIOLEDB] Catalog name 'NuGreen' is invalid.

                    at com.osisoft.jdbc.StatementImpl.executeQueryInternal(StatementImpl.java:429)

                    at com.osisoft.jdbc.StatementImpl.executeQuery(StatementImpl.java:408)

                   

                  We can see that the catalog does exists and as mentioned make calls using PI Web API works fine.

                   

                  Screenshot 2018-12-05 at 16.07.11.png