Exercise: Making PI OLEDB Provider Queries - Reads

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

Objective: The goal of this exercise is to practice the generation of PI OLEDB provider queries. Whether the application is using PI OLEDB Provider directly or using it through the PI ODBC Driver or PI JDBC Driver, the queries themselves are the same.



Using the following resources:

As well as these additional resources:


Problem Statement:

For each of the following scenarios, produce a PI OLEDB Provider query that outputs the requested data.


Note: Each of the following scenarios represents a real-world usage of PI system data, but the tags being used are the default tags in order for everyone to be able to execute the query. If a scenario is particularly applicable to your operation, I encourage you to swap out the default tags with your own tags to make the queries more realistic.


1. You're interested in seeing a running list of the recent phases that the meter has cycled through and for how long.

- Find the previous 10 values of CDM158, expressed as a string, as well as the timestamps of those values.


2. You have numerous tags coming in from one set of interfaces and you want to see just the tags that are on the high side of their range.

- Find the snapshot value of all tags with a pointsource of R whose current values are greater than their typical values.


3. At the end of every packaging cycle, the final weight is recorded in a tag. You'd like to see the total weight of the packaged product during a shift.

- Find the total of all recorded values for CDEP158 from yesterday for midnight to 8am, 8am to 4pm, and 4pm to midnight.


4. Your connection monitoring application makes a list of all connections made to the data archive.

- Find all connections made to the data archive in the last week

Hint: all new connections will be logged in the server message logs starting with "New Connection ID: <#>"


[If you don't use PI batch, either skip #5 or use Manually Create a PI Unit Batch to add a PI Batch and have a successful query]

5. You need to report the temperature min, max, and average of a reactor during a batch for reporting purposes

- Report the batchid; time range; and the min, max, and average temperatures during the time period of a batch of your choice (or Batch 1001 from the Manually Create a PI Unit Batch instructions)


Have questions? Ask the community

When you are ready, compare your answers with the Solution: Making PI OLEDB Provider Queries - Reads