Read: How to Customize IPISQL for PI SQL Client (JDBC)

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

This document provides a walkthrough of how to make custom changes to the IPISQL command line utility included in the installation of the PI SQL Client (JDBC). 

 

The purpose of this is to use the example Java class provided to understand the basics of building a JDBC connection, including connection string building. Making a change to the utility requires a very basic level of Java and PI SQL knowledge, making it a good teaching example for beginners. Going through this example will also showcase the usefulness of this utility for a Java developer trying to get started with the PI JDBC Driver.

 

Note: This document is designed to assist Java developers in making programatic calls against the PI SQL Client (JDBC).

  • The IPISQL utility should be used as is for general connection testing
  • PI SQL Commander should be used for general query writing
  • A third party application such as DBVizualizer should be used for and testing

 

The specific changes we will make to the 64-bit version of IPISQL in this tutorial will be:

  • Retrieve the query so that it returns the snapshot values of the attributes on Element B-210

 

Before getting started, confirm that the existing IPISQL utility can connect to your PI SQL DAS (RTQP Engine) service on your AF Server to retrieve the version information (the default RTQP query). This will confirm that the Java Runtime Environment (JRE) is installed, the PI SQL Client (JDBC) can communicate with the PI SQL DAS (RTQP), and the PI SQL DAS (RTQP) can communicate with the AF Server and PIFD SQL database.

 

Steps to customize IPISQL:

  1. Using PI SQL Commander on any machine with PI SQL Client (OLEDB) installed (if the JDBC machine is Linux, then this will need to be done on a separate machine)
    • Connect to the AF Server and Database
    • Develop and execute the new query to ensure that everything is correct before moving to the PI JDBC Driver
      • SELECT TOP 100 Element, Name as Attribute, TimeStamp, Value
        FROM [Master].[Element].[Attribute]
        WHERE [Element] = 'B-210'
  2. Navigate to %pihome64%\SQL\SQL Client\JDBC\Tools\IPISQL\src and make a copy of the IPISQL.java and give a new name, such as IPISQLCustom.java
  3. Open this new file in notepad or a Java IDE (such as Eclipse or NetBeans) and make the necessary changes:
    • Rename the class to match the name of the file (public class IPISQLCustom), and replace the DEFAULT_QUERY value with our new query
  4. Compile IPISQLCustom.java into IPISQLCustom.class placing it in the ..\bin directory (we will use javac but you're welcome to use your JDE)
    • C:\Program Files\PIPC\SQL\SQL Client\JDBC\Tools\IPISQL\src>"C:\Program Files\Java\jdk1.8.0_141\bin\javac.exe" IPISQLCustom.java -d ..\bin
    • Confirm that the new class file exists in the bin directory
  5. Run the new version of the utility, passing it the same parameters from before, and observe the new output

Attachments

    Outcomes