Read: How to Customize GetSnap and GetEASnap for PI JDBC Driver

Document created by kduffy on Sep 25, 2018Last modified by kduffy on Jul 2, 2019
Version 11Show Document
  • View in full screen mode

This document provides a walkthrough of how to make custom changes to the command line utilities GetSnap or GetEASnap utilities included in the installation of the PI JDBC Driver.

  • GetSnap uses the PI JDBC Driver to connect to a PI SQL Data Access Server (OLEDB), which then uses the PI OLEDB Provider to connect to the specified PI Data Archive to retrieve the snapshot value of the specified tag.
  • GetEASnap uses the PI JDBC Driver to connect to a PI SQL DAS (OLEDB), which then uses the PI OLEDB Enterprise to connect to the specified AF Server to retrieve the snapshot value of all attributes on the specified element.

 

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.

 

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

  • Retrieve interpolated values for the last 2 hours instead of snapshot values
  • Set the default timestep for interpolated queries to 30m so the query itself does not need to specify this

 

Notes about this environment:

  • The PI SQL DAS (OLEDB) service is installed locally to the PI JDBC Driver, so it can be referred to as "localhost"
  • The PI Data Archive name is PISRV01
  • The user account being passed to the PI SQL DAS (OLEDB) service is student01
    • Note: this is a domain account that the PI OLEDB Provider then passes to the PI Data Archive. This account has a mapping, and it is not a PI user for explicit logon
  • This is a development/test environment, so the PI SQL DAS certificate is not validated. If this were a production environment, it would be very important to validate this certificate.

 

Before getting started, confirm that the existing GetSnap utility can connect to your PI SQL DAS (OLEDB) and PI Data Archive to retrieve a value for sinusoid. This will confirm that the Java Runtime Environment (JRE) is installed, the PI JDBC Driver can communicate with the PI SQL DAS (OLEDB), the PI OLEDB Provider can communicate with the PI Data Archive via PI SDK, and the PI Snapshot Subsystem can return the value for sinusoid.

 

Steps to customize GetSnap:

  1. Using the PI OLEDB Provider user guide:
    • Determine the connection string parameter necessary to set the default timestep to 30m (page 15).
    • Determine the query necessary to return interpolated values of a tag for the last two hours (page 57-58).
  2. Using PI SQL Commander on the machine running the PI SQL DAS (OLEDB) service:
    • Connect to the PI Data Archive providing the connection parameter and setting the default timestep to 30 minutes
    • Execute the new query to ensure that everything is correct before moving to the PI JDBC Driver
  3. Navigate to %pihome%\JDBC\Samples\GetSnap\src and make a copy of the getSnap.java and give a new name, such as GetSnapCustom.java
  4. 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 GetSnapCustom)
    • Add the changes from step 1 to this java class. This includes the connection string edit as well as the query edits as seen below. Finally, the output processing needs to now include the timestamp
  5. Compile GetSnapCustom.java into GetSnapCustom.class placing it in the ..\bin directory (we will use javac but you're welcome to use your JDE)
    • C:\Program Files (x86)\PIPC\JDBC\Samples\GetSnap\src>"C:\Program Files\Java\jdk1.8.0_141\bin\javac.exe" GetSnapCustom.java -d ..\bin
    • Confirm that the new class file exists in the bin directory
  6. Run the new version of the utility, passing it the same parameters from before, and observe the new output

Attachments

    Outcomes