Learn about input tag and status mapping

Your input queries deliver data, which are stored in the following PI data archive columns:

• Timestamp

• Tag value

• Status

• Questionable (optional)

• Annotations (Optional)

To ensure that SQL data types are compatible with data types supported in PI Data Archive, cast the result columns to a compatible data type.

Note: The interface does not support Unicode. If a SELECT query returns a NVARCHAR (Unicode) result column, the interface logs an error message such as the following: Point - MyTag : Unsupported RDB column MyColumn type -9 . To avoid such errors, cast Unicode result columns to an equivalent non-Unicode data type.

• Timestamp - The result column must have a data type of DateTime, Date, or a compatible data type, depending on your RDBMS.

• Tag name - The result column must be a string type such as CHAR or VARCHAR .

• Tag value - For input points, the interface casts results from the RDBMS to compatible PI data types as follows:

  ODBC data type	RDBMS data type	Target PI point data type
  		Float	Integer	Digital	String
  Approximate (floating point)	SQL_NUMERIC SQL_DECIMAL SQL_REAL SQL_FLOAT SQL_DOUBLE	Cast to the corresponding floating-point type.	Cast to integer.	Cast to integer and interpreted as pointer to digital state set.	Converted from floating-point to string.
  Exact (integer)	SQL_TINYINT SQL_SMALLINT SQL_INTEGER SQL_BIGINT SQL_BIT	Cast to the particular floating-point type.	Cast to the particular integer type.	Interpreted as pointer to digital state set.	Converted from integer to string.
  Character	SQL_CHAR SQL_VARCHAR SQL_LONGVA RCHAR	Converted to double, then cast to the PI floating-point type.	Converted from string to integer and cast to integer PI data type.	Checked against digital state set.	Retrieved number of bytes copied.

• Status - The status field indicates the state of the target tag. To map the values in the RDBMS to valid PI status values, use PI ICU to configure the following settings on the rdbodbc -> Startup Parameters tab:

  • Successful - Status Range

  • Bad Input - Status Range

• The data type of the status column determines how the interface handles it, as follows.

  • String status values

  • If the status from the result set is NULL or falls in the specified success range, the tag status is set to 0 (Good) and the value is written to the tag. If the status falls in the bad input range, the corresponding system digital state is written to the tag. Evaluation of status strings is case-insensitive. If the status value does not reside in either range, the tag is assigned Bad Input status. The following figure shows example definitions in the PI System digital set table for success and bad input ranges.

    

  • Numeric status values

  • Numeric status columns are handled as follows:

    • Zero or NULL: Value is written to tag

    • Positive value: Bad Input status is written to tag

    • Negative value: Corresponding system digital state is written to tag

  • Digital PI tag

  • Any non-zero status indicates Bad Input.

  • The following figure shows database table rows containing status values and the corresponding PI tag statuses.