Solution: Making PI OLEDB Provider Queries - Writes

Document created by kduffy on Sep 26, 2018Last modified by kduffy on Nov 6, 2018
Version 3Show Document
  • View in full screen mode

This document contains the answers to the Exercise: Making PI OLEDB Provider Queries - Writes.


1. Confirm that the following tag does not exist: "ReactorOpZone"

FROM pipoint..pipoint2
WHERE tag = 'ReactorOpZone'


The first step before making the tag is to confirm that the tag does not already exist. As seen in the previous exercise (item 2), we can use [pipoint]..[pipoint2] (or [pipoint]..[classic]) to access the tag list, so simply querying this table for any tags with the name ReactorOpZone is sufficient:


There are 0 rows returned, so we know this tag does not yet exist.


2. Create a digital state set called OperatingZones that contain the following five values: "Free", "Load", "Reaction", "Unload", "Clean"

CREATE TABLE pids..OperatingZones (code Int32, offset Int32, name WString)


INSERT pids..OperatingZones (name) VALUES ('Free')
INSERT pids..OperatingZones (name) VALUES ('Load')
INSERT pids..OperatingZones (name) VALUES ('Reaction')
INSERT pids..OperatingZones (name) VALUES ('Unload')
INSERT pids..OperatingZones (name) VALUES ('Clean')


As seen in this document, each digital state set is a new table in the [pids] catalog. If we expand that catalog now, we can see the current list of tables, and therefore digital state sets:


To add a new table, we can open the query compendium's "3-Digital State Database Statements.sql" file and look at the first two queries:


Here, we're creating a new table with the correct column datatypes and filling in the new table with values. The PI OLEDB Provider will communicate with the PI Data Archive to create the digital state, and it will also fill in the offset and code columns on our behalf.


We'll execute the first query that creates the digital state set with the requested name:


If we refresh the pids catalog, we see that the new table's been created, but executing its predefined query shows that it's currently empty.

If we follow this up with five instances of the second query from the query compendium, where we enter our state names, it will fill in the table:

Note: Currently, PI SQL Commander can only handle one query at a time, and the provider can only handle one instance of VALUES per query, so each of these five will need to be executed one at a time.


Now when we execute the predefined query on this table, we see the five values, as well as their offsets and codes, which were automatically generated by the provider:


If we open PI System Management Tools, we can see that the digital state set exists everywhere, and not just within the scope of the PI OLEDB Provider:


3. Create the digital tag from step 1, pointing it to this new digital state set

INSERT pipoint..classic (tag, pointtypex, digitalset)
VALUES ('ReactorOpZone', 'digital', 'OperatingZones')


To create the tag, we can start with the point database's query compendium because we know we will be inserted something in a table here. The third query shows how to create a tag:


But it does not show how to create a digital tag and associate it with a digital state set. To add this to the query, we can look at the configuration of the built in digital tag, cdm158. If we query its information from the classic (or pipoint2) table and identify from the column list where the digital state set is pointed to (the column named digitalset), we can deduce what additional values and columns we'll need to specify in the insert statement, as well as what form we need to specify the point type in for the pointtypex column that we see in the query compendium's example query:


From this information, we can alter the compendium's query as follows to create the tag and associate it with the new digital state set:


Once we've run this query, we can search for the tag once again with the query from step 1, but it should appear in the results list:


And finally, we can get final confirmation of the tag's creation outside the scope of PI OLEDB Provider by looking at PI System Management Tools:


4. Backfill the data by writing the values from the chart

INSERT piarchive..picomp2 (tag, time, value) VALUES ('ReactorOpZone', '11/1/18 10:00', DIGCODE('Free', 'OperatingZones'))
INSERT piarchive..picomp2 (tag, time, value) VALUES ('ReactorOpZone', '11/1/18 14:00', DIGCODE('Load', 'OperatingZones'))
INSERT piarchive..picomp2 (tag, time, value) VALUES ('ReactorOpZone', '11/1/18 14:30', DIGCODE('Reaction', 'OperatingZones'))
INSERT piarchive..picomp2 (tag, time, value) VALUES ('ReactorOpZone', '11/4/18 04:00', DIGCODE('Unload', 'OperatingZones'))
INSERT piarchive..picomp2 (tag, time, value) VALUES ('ReactorOpZone', '11/4/18 04:20', DIGCODE('Clean', 'OperatingZones'))
INSERT piarchive..picomp2 (tag, time, value) VALUES ('ReactorOpZone', '11/4/18 06:00', DIGCODE('Free', 'OperatingZones'))
INSERT piarchive..picomp2 (tag, time, value) VALUES ('ReactorOpZone', '11/4/18 09:00', DIGCODE('Load', 'OperatingZones'))
INSERT piarchive..picomp2 (tag, time, value) VALUES ('ReactorOpZone', '11/4/18 10:00', DIGCODE('Reaction', 'OperatingZones'))


We can begin this step by looking at the "1-Archive Statements.sql" file, about 1/3 of the way down, to see example queries for inserting values into the archives, such as:

The picomp2 table holds all of the compressed values for a tag, so this is where we will insert the values. This example doesn't address the additional burden of writing to a specific digital state for the tag, but we can either use trial and error with the different tables to figure out the syntax (I strongly recommend doing something like this against the test tags and not any production tags you plan to write to), or we can keep scrolling through the query compendium until we see this example for cdm158:


Note: Be careful not to use the picomp table as this is for backward compatibility with the PI SQL Subsystem and PI ODBC Client; the schema is different and the values are written to the status column and not the value column. Mixing these two tables and their respective schemas can easily lead to errors:


We can edit the first query to write to our tag and digital state set, with our value and times listed from the chart as follows:

As noted earlier, the provider can only handle one instance of VALUES per query, and the commander can only handle one query per execution. So you'll need to execute each query one at a time to write each value.


5. Query this time range and confirm that the values were input correctly

SELECT tag, time, DIGSTRING(CAST(value as int32))
FROM [piarchive]..[picomp2]
WHERE tag = 'ReactorOpZone'
AND time BETWEEN '11/1/18' AND '11/5/18'


As seen in the previous exercise, the [piarchive]..[picomp2] table will list the compressed values for the tags, but since this is a digital tag, we'll need to follow the steps in this document to output the strings properly:


6. It's been decided that "Free" should be changed to "Empty", so the digital state needs to be changed.

UPDATE pids..OperatingZones SET name = 'Empty' WHERE name = 'Free'


This query can be written based on general knowledge of SQL to transform the insert statements written in step 2 into update statements, but this query can be found in the query compendium as well:

If we edit this statement to refer to our digital state set, old name, and new name, we have the following:


And we can confirm that the change took place by looking at the list of states in this digital set:



7. Execute the query in step 5 again; does anything need to be changed?



Since the values are stored in the archive files as digital state set numbers and offsets, the string is interpreted at runtime and is not stored anywhere. This means that you can change the digital state string and reexecute the query to see the new values: