Relational Database(rdbms via odbc) Interface

Inputs to PI via SELECT Clause – Detailed Description

For passing values in the direction from RDB to PI, users have to configure PI tags that define either a SELECT query or a Stored Procedure call (which returns data in the form of a result-set). The retrieved data is then sent to corresponding PI points according to the specified distribution strategy (see the Data Acquisition Strategies section). Before a description into the acquisition strategies details, a short discussion about how the interface handles NULLs and result-sets that contain more than one row.

NULL Columns

As NULLs can come in any column of the SELECT list, the interface applies the following rule before it sends such a row to PI:

• 
  If the timestamp column is NULL, the execution time is used.
  
• 
  If the status column is NULL and the value column IS NOT NULL, the value is valid.
  
• 
  When both, the value and the status are NULLs (or just the value is NULL) the No Data digital state is used to indicate the fact that the expected value is absent.
  
• 
  For GROUP and RxC strategies the /IGNORE_NULLS start-up parameter allows ignoring values, which are NULL.
  

For further details see section Evaluation of STATUS Field – Data Input.

Bulk Data Input

Location2 determines if the whole result-set (an array) of SELECTed rows will be sent to PI or whether the interface takes just the first row.

Location2	Bulk Option
0	Only the first row in the result-set is used.
1	The interface sends all rows of the selected result-set to PI.

Note: When Location2 = 1 (bulk read), it is advisable to sort the result-set by the timestamp column in the ASCcending order; only then the PI System can support exception reporting and properly assign the internal interface snapshot. The following example shows a suitable query:
SELECT Timestamp,Value,0 FROM Table WHERE Timestamp > ? ORDER BY Timestamp ASC; P1=TS

Data Acquisition Strategies

To interpret records obtained by a SELECT statement in a flexible way, different data acquisition strategies can be defined. An individual acquisition strategy is recognized according to the Location3 attribute of a given tag. The following table summarizes the Location3 options.

Location3	Data Acquisition Strategy
0	SQL query populates a Single PI Tag.
> 0	Selects the Tag Group mode. Location3 points to the column number of a multiple field query, where the indexed column contains data for this particular group-tag.
-1	Selects the Tag Distribution mode. The SQL statement must return a key (tagname or alias) to denote the particular point.
-2	Selects the RxC Distribution mode SELECT must return a result-set fitting to the following frame: [PI_TIMESTAMP1], PI_TAGNAME1, PI_VALUE1, [PI_STATUS1], PI_TAGNAME2, PI_VALUE2, [PI_STATUS2] …

SQL SELECT Statement for Single PI Tag

Option1: Fixed Position of Fields in SELECT Statement

To properly recognize the meaning of values read from a relational database, the following column sequence has to be kept:

SELECT [Timestamp,] Value, Status FROM Table …;

When used, the interface always expects the Timestamp field to be in the first position followed by the Value and Status columns. The interface detects the Timestamp field by checking the field-data-type against SQL_TIMESTAMP ODBC data-type marker. If a database does not support timestamps (like for instance the dBase IV), and the timestamp is expressed in the string data type (SQL_CHAR), the query has to use the CONVERT() scalar function (or the ANSI CAST() ) to get the required timestamp data type.

See section Timestamp Format for more details.

In this strategy, valid combinations (positions) of the Timestamp, Value and Status fields in the SELECT statement are:

• 
  SELECT Timestamp, Value, Status FROM Table…
  
• 
  SELECT Value, Status FROM Table…
  

Note: The mandatory STATUS column can be provided in the form of a constant expression (zero) if the database stores only the value; that is:
SELECT Value,0 FROM Table …
is a valid query.

Option 2: Arbitrary Position of Fields in a SELECT Statement – Aliases

If the RDB supports aliasing, the interface recognizes keywords, which help to translate the columns to the concept of Timestamp, Value, Status and Annotation. By naming (aliasing) the columns there is no need to stick to the fixed positions of columns (like described in previous section) any more. The corresponding keywords are the following:

PI_TIMESTAMP, PI_VALUE, PI_STATUS, PI_ANNOTATION

Consider the following query:

SELECT Timestamp AS PI_TIMESTAMP, Value AS PI_VALUE, Status AS PI_STATUS, Annotation AS PI_ANNOTATION FROM…

is an equivalent to:

SELECT Value AS PI_VALUE, Status AS PI_STATUS, Timestamp AS PI_TIMESTAMP, Annotation AS PI_ANNOTATION FROM …

Note: Since interface version 3.11, also the timestamp and status columns are optional in the aliased mode. The following statement is therefore accepted:
SELECT Value AS PI_VALUE FROM Table …;
Since interface version 3.15, the Annotation column can be specified. Its usage is optional and only supported in the Aliased mode. The following query shows how to input annotations to a PI Tag:
SELECT Timestamp AS PI_TIMESTAMP, Value AS PI_VALUE, Annotation AS PI_ANNOTATION FROM Table …;
Since interface version 3.15, the PI Tag can be of the data type Timestamp. Input into this data type is also only possible in the Aliased mode. The following query is valid:
SELECT Timestamp AS PI_TIMESTAMP, Timestamp AS PI_VALUE FROM Table…;

See these examples in Appendix B Examples:

• 
  Example 3.1 – Field Name Aliases
  
• 
  Example 1.6 – Single Input with PI Annotations