Learn about query execution

Before a query is executed, the interface checks its syntax. If the syntax is invalid, the interface logs a Tag refused message describing the problem. To ensure that the syntax of a query is valid, test the query using a third-party ODBC query tool (for instance, the Microsoft ODBC Test application).

The queries executed by your tags can contain single or multiple SQL statements, each terminated by a semicolon. Each statement is committed after execution (AUTOCOMMIT) unless you enable transaction semantics for the tag by specifying the /TRANSACT keyword in the ExDesc tag attribute. If all statements succeed, the transaction is committed. If any statement fails, all statements are rolled back.

The following SQL statements are permitted:

• SELECT

• INSERT

• UPDATE

• DELETE

• {CALL}

  Note: Proprietary language constructions such as T-SQL and PL/SQL are not guaranteed to work. For example, the MS SQL Server's T-SQL is supported for use with the MS SQL ODBC driver, but similar constructions fail with an Oracle ODBC driver. If you require logic to control the flow of execution, use stored procedures.

To optimize performance, the interface executes SQL queries using prepared execution. SQL queries are compiled the first time they are executed, and the compiled queries are used for subsequent executions. Some ODBC drivers limit the number of prepared statements permitted. If your ODBC driver enforces a maximum, you can disable prepared execution using PI ICU: Go to the rdbodb > Optional Parameters tab and check Direct SQL execution.

If the query fails the first time it is executed, the tag is removed from processing. For output tags, if the query fails, the output tag is assigned the Bad Output state.

Queries can execute previously-defined stored procedures. Stored procedure calls can use placeholders (input parameters) in their argument lists. To call a stored procedure, use the following syntax:

{CALL procedure-name[([parameter][,[parameter]]…)]}

Output parameters are not supported. Stored procedures are therefore mainly useful for complex actions that cannot be expressed using the SQL syntax that the interface supports. For an example of the use of stored procedures, see the Learn about SQL examples for PI Interface for RDBMS section in this document.

To detect whether a query succeeded, check the following variables:

• @query_success: TRUE if query succeeded (all values written to PI Data Archive)

• @query_failure: TRUE if query failed

You can define an IF statement that conditionally issues a single SQL statement depending on the state of the variable.

For example, when a query succeeds:

SELECT timestamp, value, 0 FROM table1
WHERE timestamp > ?
ORDER BY timestamp ASC;
IF @query_success
INSERT INTO table2 (timestamp, tagname, query_execution_message)
VALUES (?, ?, 'Query Succeeded);

When a query fails:

SELECT timestamp, value, 0 FROM table1
WHERE timestamp > ?
ORDER BY timestamp ASC;
IF @query_failure
INSERT INTO table2 (timestamp, tagname, error_message)
VALUES (?, ?, 'Query failed');

The interface does not support an ELSE clause for the IF statement.

Note: The query following the IF statement does not execute if the query fails (@query_failure).

To determine whether PI tags were successfully updated by a query, check the value of either of the following variables:

• @write_success: TRUE if query succeeded (all values written to PI Data Archive)

• @write_failure: TRUE if it failed. These variables are set when the first query is executed.

You can define an IF statement that conditionally issues a single SQL statement depending on the state of the variable.

For example, to determine whether rows in the RDBMS have been successfully written to tags and can therefore be deleted from the RDBMS, use the following logic:

SELECT timestamp, value, 0 FROM table1 WHERE timestamp > ? ORDER BY timestamp;
IF @write_success DELETE FROM table1 WHERE timestamp <= ?;

To determine whether rows in the RDBMS have not been successfully written to tags, use the following logic:

SELECT timestamp, value, 0 FROM table1 WHERE timestamp > ? ORDER BY timestamp;
IF @write_failure DELETE FROM table1 WHERE timestamp <= ?;

To determine whether tag distribution succeeded, you must use the @rows_dropped variable. For details, see Verifying successful data distribution.

Note: The query following the IF statement does not execute if the query fails (@write_failure).