Learn about data type compatibility

The data type of your result columns must be compatible with the data types supported by the PI data archive. To ensure compatibility, use the ANSI CAST() function or the ODBC CONVERT() function.

The syntax for CONVERT() is as follows:

{Fn CONVERT(value_exp,data_type)}

Where value_exp is a column name, result of a scalar function, or a literal, and data_type specifies a valid ODBC data type.

For example, the following expression converts the output of the MS SQL Server GETDATE() function to a string:

{Fn CONVERT({Fn GETDATE()},SQL_CHAR)}

For details about CONVERT() , refer to the MSDN Library or your ODBC driver documentation. The syntax for CAST() is as follows:

CAST ( { expression | NULL } AS data_type [(length)] )

For example, the following expression converts the selected value to a string:

SELECT timestamp, CAST(value AS Varchar(255)), status FROM table1…

For details about CAST() , refer to your RDBMS vendor's SQL reference guide. For a full description of the ODBC-supported data types, see the ODBC Programmer's Reference.

To ignore null values for tags populated using the tag group and RxC distribution strategies, enable the Ignore Nulls option on the PI ICU rdbodbc > Optional Parameters tab.

When updating PI tags with data from the RDBMS, the interface handles NULLs as follows:

• Timestamp: If the timestamp is NULL, the current execution time is used.

• Value and Status: If the value column is not NULL, the value is valid, even if status is NULL. If both value and status are NULL, the No Data digital state is written to the tag, as shown in the following figure.

  

To ignore null values for tags populated using the tag group and RxC distribution strategies, enable the Ignore Nulls option on the PI ICU rdbodbc > Optional Parameters tab.