Using JDBC to Access OSI PI from Ignition

Preface

This document outlines the steps necessary to utilize the OSIsoft PI JDBC driver to query data from PI in Ignition. This document assumes a familiarity with the PI architecture and software components. A great deal of information is available about PI in general through OSIsoft’s tech support website, youtube channel, etc.

Accessing PI through the JDBC interface allows you to access data through SQL, using many powerful query mechanisms. Other interfaces may be used to access data as well, such as OPC­HDA. Each interface provides its own style of access and functionality.

Connecting to OSI PI

Step 1 - Install all necessary components

In order to use the JDBC driver, you’ll need to make sure the JDBC redistributable is installed, along with other necessary components, such as PI SQL Data Access Server, and PI OLEDB Enterprise. OSIsoft has a number of documents available outlining how to install and verify the necessary components.

Step 2 - Load the JDBC Driver into Ignition

In order to use the JDBC driver in Ignition, a new Database Driver must be created:

     1. Log into Ignition’s Gateway Configuration

     2. Click on Databases>Drivers, and click on “Create new JDBC Driver”

     3. Fill in the pertinent details:

          a. Name: OSI PI

          b. Classname: com.osisoft.jdbc.Driver

          c. Jar files: Browse to PIJDBCDriver.jar, located under “PIPC\JDBC” (note: the location of your “PIPC” folder will depend on your installation). Select the jar file.

          d. Driver Type: Generic

          e. URL Format: jdbc:pisql://<das_node>/Data Source=<pi_server_name>; Integrated Security=SSPI;

          f. Default Translator: MSSQL

     4. Save the driver

Step 3 - Create a Datasource

Next, create the datasource that Ignition will use:

     1. In the Config section of the gateway, select Databases>Connections

     2. Select “Create new database connection”, and then select “OSI PI”

     3. Modify the connect url to point to the correct PI server instance.

          a. “<das_node>” is the server address, such as “localhost”.

          b. “<pi_server_name>” is the name given to the PI server during installation.

     4. Enter the Username and Password for a windows account that has been configured for access.

     5. Save the connection. If you refresh the database connection screen, you should see the status go to “Valid”. If not, check the Database Connection Status page for more information.

Note about security: OSIsoft recommends using Integrated Security (SSPI) for authentication. For more information about security, see the OSIsoft document Configuring PI Server Security available from the OSIsoft technical support website.

Step 4 - Verify Connectivity

Once the connection reports “Valid”, the easiest way to verify that everything is set up correctly is to open the designer, and attempt to run a query from the Database Query Browser (found under Tools). For example, to query the various PI products installed:

SELECT * FROM [pisystem].[piproductversion]

Developing Queries - The PI SQL Commander

Any query that can be run through the PI SQL Commander or the OLEDB interface should run without issue through the JDBC interface. The SQL Commander offers a number of useful features, including a compendium of useful queries (found under Help>Query Compendium). Once a query is developed and tested in the query commander, it can be brought over and used in Ignition against the PI datasource.

Note about data types: Many queries return values as “variants”. While this is fine most of the time, in some locations (such as in the Ignition Chart component) this can cause an error. To work around this, use the SQL “cast(expression as type)” function to coerce the data to the correct type. For example:

SELECT TOP 10 tag, time, cast(value as Float32) FROM

piarchive..picomp2 WHERE tag = 'sinusoid'

The cast function and data types are explained more in the PI OLEDB Enterprise User Guide, but in general, the most common types are: Int16, Int32, Int64, Float32, Float64, Boolean, String, DateTime.

Useful Documents

These documents can be found in OSIsoft Tech support portal:

PI JDBC 2010 R2 Administrator Guide

Configuring PI Server Security

PI OLEDB Enterprise User Guide