Relational Database(rdbms via odbc) Interface

Example 3.4c – Tag Distribution with Auxiliary Column – rowRead

SQL Statement (file PI_DIST3.SQL)
SELECT time, tag, value, 0 AS status FROM T3_4c WHERE rowRead=0; UPDATE Tdata SET rowRead=1 WHERE rowRead=0;
Relevant PI Point Attributes
Tag	Instrument tag		Extended Descriptor	Location1		Location3		Location4
Tag1	PI_DIST3.SQL			1		-1		1
Tag2				1				1
RDBMS Table Design
Table T3_4c
tag		time			value		rowRead
Varchar(255) (MS SQL Server)		DateTime (MS SQL Server)			Real (MS SQL Server)		Integer (MS SQL Server)

Example 3.4d – Tag Distribution with Auxiliary Table Keeping Latest Snapshot

SQL Statement (file PI_DIST4.SQL)
SELECT T3_4data.time, T3_4data.tag, T3_4data.value, 0 AS status FROM T3_4data INNER JOIN T3_4snapshot ON T3_4data.tag=T3_4snapshot.tag WHERE T3_4data.time > T3_4snapshot.time; UPDATE T3_4snapshot SET time=(SELECT MaxTimeTag.maxTime FROM (SELECT DISTINCT (SELECT MAX(time) FROM T3_4data WHERE tag=TdataTmp.tag) As MaxTime, tag FROM T3_4data TdataTmp) MaxTimeTag INNER JOIN T3_4snapshot TsnapshotTmp ON MaxTimeTag.tag=TsnapshotTmp.tag WHERE T3_4snapshot.tag=MaxTimeTag.tag)
Relevant PI Point Attributes
Tag	Instrument tag		Extended Descriptor	Location1		Location3		Location4
Tag1	PI_DIST4.SQL			1		-1		1
Tag2				1				1
RDBMS Table Design
Table T3_4data
tag		time			value		status
Varchar(255) (MS SQL Server)		DateTime (MS SQL Server)			Real (MS SQL Server)		Integer (MS SQL Server)
Table T3_4snapshot
tag					time
Varchar(255) (MS SQL Server)					DateTime (MS SQL Server)

Explanation:

The T3_4snapshot table has to contain a list of all 'Target Points', and, at the very beginning, also the initial timestamps (the time column in T3_4snapshot cannot be NULL). The first statement (the SELECT) will thus deliver all the rows (from the T3_4data) theirs time is bigger than the time column of the T3_4snapshot. The UPDATE statement will then retrieve the most recent timestamps – MAX (time) from the T3_4data and will update the T3_4snapshot. During the next scan, the JOIN makes sure only the new entries (from the T3_4data) will be SELECTed.

Example 3.4e – Tag Distribution in Combination with /RBO and 'Time-Window'

SQL Statement (file PI_DIST5.SQL)
SELECT time, tag, value, 0 AS status FROM T3_4e WHERE time > GETDATE()-(1./24.);
Relevant PI Point Attributes
Tag	Instrument tag		Extended Descriptor	Location1		Location3		Location4
Tag1	PI_DIST5.SQL			1		-1		1
Tag2				1				1
RDBMS Table Design
Table T3_4e
tag		time			value		status
Varchar(255) (MS SQL Server)		DateTime (MS SQL Server)			Real (MS SQL Server)		Integer (MS SQL Server)

Explanation:

The time-window is created by the MS SQL function GETDATE() (returning the current time). The (1./24.) means one hour. The interface will thus have to have the /RBO start-up parameter specified to avoid duplicates in the PI Archive.