Solution: IPISQL for PI SQL Client (JDBC)

Document created by kduffy on May 21, 2019Last modified by kduffy on May 21, 2019
Version 2Show Document
  • View in full screen mode

This document contains the answers to the Exercise: IPISQL for PI SQL Client (JDBC).


1. What was the full JDBC url being used in this case?

answer: jdbc:pisqlclient://AFSRV01:5464/NuGreen


The first question can be answered directly from the output of IPISQL. The URL that was built based on the parameters passed is the first thing outputted by the utility:


2. What was the query being executed in this case?

answer: SELECT * FROM System.Diagnostics.Version


For this question, the file needs to be opened into an editor of some kind (notepad, Eclipse, NetBeans, etc). 


By reading through the java file, the query may have been noticed right away, but if not then it should be findable by seeing where the query is opened:

try (PreparedStatement pStatement = connection.prepareStatement(query))

then back tracking to where this query variable was set:

if (query.isEmpty())

then back tracking further to see where this DEFAULT_QUERY variable was set:

private final static String DEFAULT_QUERY = "SELECT * FROM System.Diagnostics.Version";


3. What would the query become if we wanted to output the names of the first ten elements based on the 'Boiler' template?

answerSELECT TOP 10 Name
                FROM [Master].[Element].[Element]
                WHERE [Template] = 'Boiler'


This question should be answered by using PI SQL Commander (which requires the use of the PI SQL Client (OLEDB) endpoint on a Windows machine), by either going through the query compendium, or simply right clicking on the Element table within the Element schema and Master catalog of a PI SQL Client connection to the AF Server's NuGreen database, and selecting 'Execute Predefined Query':



In the resulting query editor tab, the SELECT portion of the statement can be changed to TOP 10 and to only output Name, then a WHERE clause can be added to restrict to only 'Boiler' templates:


Note: one of the purposes of this question was to further the idea that PI SQL Commander should still be the query development application of choice for Java programmers even though it does not support the JDBC endpoint. All of the PI SQL Client endpoints (OLEDB, ODBC, and JDBC) simply pass their queries to the same PI SQL DAS (RTQP) service, so anything that works for the OLEDB endpoint will work for the JDBC endpoint.


4. Implement the change in question 3 and recompile the IPISQL utility into IPISQLCustom. Demonstrate it working by rerunning the utility from the command prompt.



The steps to implement this are laid out in Read: How to Customize IPISQL for PI SQL Client (JDBC).


In this case, you'd want to set:

private final static String DEFAULT_QUERY = "SELECT TOP 10 Name FROM [Master].[Element].[Element] WHERE [Template] = 'Boiler'";


Then follow the steps to recompile the file into a .class file.


Once this is done, it can be executed from the command line showing output like this: