1. Overview

PI JDBC PI JDBC provides data access to the OSISoft PI System through the use of SQL queries.


This guide includes:

  1. Terms and abbreviations used in the Pi JDBC guides.
  2. Information on PI JDBC.
  3. A description of the Edge One™ PI JDBC Module.
  4. Information on the Pi JDBC Module Data Structure.

To learn how to configure the Edge One™ Pi JDBC module, please refer to the PI JDBC Configuration Guide.

Terms and Abbreviations

Term Description
Certificate A validation and encryption tool, part of the HTTPS protocol, which secures and encrypts data going back and forth between a server and a client. It is issued by a trusted certification authority (CA).
Driver A computer program that operates or controls a particular type of device that is attached to a computer. A driver provides a software interface to hardware devices, enabling operating systems and other computer programs to access hardware functions without needing to know precise details about the hardware being used.
Endpoint The entity or entry point to a service, a process, or a queue, or topic destination.
Java A general purpose programming language that is class-based, object-oriented, and designed to have as few implementation dependencies as possible.
JDBC Java Database Connectivity is an application programming interface (API) for the programming language Java, which defines how a client may access a database.
JRE Java Runtime Environment (JRE) is a set of software tools for development of Java applications. It combines the Java Virtual Machine (JVM), platform core classes and supporting libraries.
OLE DB Object Linking and Embedding Database. An API designed by Microsoft that allows access to data from a variety of sources in a uniform manner.
PI Server A real-time data storage and distribution engine that powers the PI System and provides a comprehensive real-time and historical view into operations
Schema Structure of a database described in a formal language supported by the database management system (DBMS).
SQL Structured Query Language. SQL is the standard language to communicate with relational database systems.

About PI JDBC

PI Java Database Connectivity (JDBC) provides data access to OSISOft PI systems through the use of SQL queries.

JDBC is a an API that is built into every Java Runtime Engine (JRE) and defines how a client can access a database. For relational database access, Java applications use JDBC.

The Edge One™ PI JDBC Connector uses the PI SQL Data Access Server (PI SQL DAS) as a gateway, which provides secure network communication (HTTPS) and executes the queries. PI SQL Data Access Server is required for PI JDBC Connector.

Edge One™ PI JDBC Module

The Edge One™ PI JDBC module is supplied as a container that enables Edge One™ to perform read operations on PI Server databases accessible through a JDBC interface. Data collected from a PI Server can be processed locally by the optional Edge One™ Flows module, by a SmartPlug™ application or another containerized application. The processed data can then be sent to CloudPlugs IoT, or to a supported public cloud, data lake or analytics application.

The Edge One™ PI JDBC module publishes data values retrieved from PI Server databases into CloudPlugs channels making them available to other devices and applications subscribing to those channels.
All the communications with supervisory applications in the cloud are encrypted, event driven and asynchronous, resulting in substantial traffic savings.

A single Edge One™ deployment can operate multiple PI-JDBC projects to connect to multiple Pi servers and/or multiple Pi Server databases. The database endpoints, such as an OLE database, and schemas, such as a PI archive, are configured in each project to retrieve data from the appropriate locations.

Connections to the PI servers are secure and can be configured to require certificate validation and authentication of access credentials.

The Pi JBDC Connector includes the following driver versions:

  • PI JDBC 2010
  • PI JDBC 2012
  • PI JDBC 2014
  • PI JDBC 2016
  • PI JDBC 2018
  • PI JDBC 2019

Back to Top

Pi JDBC Module Data Structure

The Edge One™ Pi JDBC Connector Module uses the standard data representation of all Edge One™ modules.

Device data is stored in JSON, key-value pairs in which the key is the register address and the value is the value of the register.

Pi Server database schemas need to be associated with an Edge One™ Group in order for their data to become accessible to Edge One™.

  • Group Name, a string that represents the custom name that is entered for the group of devices.
  • Group ID, a number that will be used to identify the group inside the Edge One™ data structure.

The MQTT topics carrying Pi JDBC payloads have the following structure:


topic:  data/PID/GID

where,

	data  	indicates that this is a data topic.
	PID		a number or Project ID that identifies the Pi JDBC project generating data.
	GID		a number or Group ID as indicated above.

The data inside this topic is a JSON such as:

{
   "mod":"pi-jdbc",
   "vals":[
      {
         "q":68,
         "err":"this value is cached on the connector",
         "v":"-272.09302325581393",
         "k":"ffffffdc-ffff-ffff-beff-ffffffffffff",
         "ts":1582718694000
      },
      {
         "q":68,
         "err":"this value is cached on the connector",
         "v":"240.0",
         "k":"ffffffdb-ffff-ffff-f6ff-ffffffffffff",
         "ts":1582718694000
      }
   ],
   "g":"1",
   "pid":"pi2016-ole-ent",
   "ts":1582718867250
}

Data can be read locally or sent to a remote MQTT server or cloud such as CloudPlugs IoT using the Message Router

Back to Top

Still need help? Get in touch!
Last updated on 29th Apr 2020