Relational Database(rdbms via odbc) Interface

Source Tag

Output points control the flow of data from the PI Data Archive to any outside destination, such as a PLC or a third-party database. The UniInt based interfaces (including RDBMSPI) do use an indirect method for outputting values. That is, there are always two points involved – the SourceTag and the output tag. The output tag is actually an intermediary through which the SourceTag's snapshot is sent out. The rule is that whenever a value of the SourceTag changes, the interface outputs the value and, consequently, the output tag receives a copy of this event.

That means that outputs are normally not scheduled via scan classes (executed periodically). Nevertheless, outputting data to RDB on a periodical basis is possible. The interface does not namely mandate that the SQL statements for input points must be SELECTs. Input points can execute INSERTs, UPDATEs, DELETEs – SQL statements that send values to RDB (see section Output from PI for examples).

For outputs triggered by the SourceTag, the trigger tag (SourceTag) can be associated with any point source, including the point source of the interface it works with (referenced through the /ps start-up parameter). Also, the point type of the trigger tag does not need to be the same as the point type of the output tag. The default data type transformation is implemented.

As mentioned in previous paragraphs, an output is triggered when a new value is sent to the snapshot of a SourceTag. If no error is indicated (during the interface's output operation) then this value is finally copied to the output point. If the output operation is unsuccessful (e.g. any ODBC run time error occurred), then an appropriate digital state (Bad Output) is written to the output point.

Note: In case of an ODBC call failure, the output tag will receive the status Bad Output.

Unused Attributes

The interface does not use the following tag attributes.

• 
  Conversion factor
  
• 
  Filter code
  
• 
  Square root code
  
• 
  Total code
  
• 
  UserInt1,UserInt2
  
• 
  UserReal1,UserReal2
  

8. SQL Statements

As outlined in the previous sections, SQL statements are defined in ASCII files, or can be specified directly within the Extended Descriptor of a PI tag. Both options are equivalent. ASCII files are located in the directory pointed to by the /SQL=path keyword (found among the interface start-up parameters). Names of these files are arbitrary; the recommended form is filename.SQL. The ASCII SQL file is bound to a given point via the Instrument Tag attribute. In case the Instrument Tag field is empty, the interface looks for a SQL statement definition in the Extended Descriptor – searching for the keyword /SQL. If no statement definition is found, the point is accepted, but marked inactive. Such a tag would only receive data via Tag Distribution or Tag Group strategies. Example: SQL statement definition in Extended Descriptor:

/SQL= "SELECT Timestamp,Value,0 FROM Table WHERE Timestamp > ? ORDER BY Timestamp;" P1=TS

Note: The entire statement(s) definition text in the Extended Descriptor has to be surrounded by double-quotes (" ") and the semicolon ';' marking the end of a particular query is mandatory.

The same SQL statement defined in an ASCII file: SQL_in_ASCII.SQL

SELECT Timestamp,Value,0 FROM Table WHERE Timestamp > ? ORDER BY Timestamp;

InstrumentTag: SQL_in_ASCII.SQL

ExtendedDescriptor: P1=TS

Note: Both ASCII file and Extended Descriptor definitions can contain a sequence of SQL commands separated by semicolons ';'. When the interface works in the ODBC AUTOCOMMIT mode (default setting), each SQL statement gets committed immediately after the execution. Transaction can be enforced by the /TRANSACT keyword in the Extended Descriptor of a given tag; see section Explicit Transactions for more details.

Prepared Execution

Once SQL statement(s) have been accepted by the interface (during the interface startup or after a point creation/edit), the corresponding ODBC statement handles are internally allocated and prepared. These prepared statements are then executed whenever the related tag gets scanned/triggered. This setup is most efficient when statements are executed repeatedly with only different parameter values supplied. On the other hand, some ODBC drivers are limited on the number of concurrently prepared ODBC statements (see the section Database Specifics), therefore, the interface allows for the direct execution mode as described in the next paragraph.

Note: Prepared Execution is the default behavior. It was the only option in previous versions of this interface (prior to version 3.0.6)

Direct Execution

The interface will use the direct ODBC Execution (will call the SQLExecDirect() function) when the start-up parameter /EXECDIRECT is specified. In this mode, the interface allocates, binds, executes and frees the ODBC statement(s) each time the given tag is examined. Direct execution has the advantage of not running into the concurrently prepared statement limitation known for some ODBC drivers. Another situation where the direct execution is useful, are complex stored procedures, because the direct execution allows dynamic binding and effectively examining different result-sets these stored procedures can generate. A disadvantage is slightly increased CPU consumption; nevertheless, this constraint doesn't seem to be that important today.

Language Requirements, ODBC API Conformance

The level of API conformance of the ODBC driver used is checked at the interface startup. The interface requires the ODBC driver to be at least of Level 1 API conformance (SQL_ODBC_API_CONFORMANCE) and SQL statements should comply with the MINIMUM Grammar (SQL_ODBC_SQL_CONFORMANCE). The information about the supported conformance level (both API and Grammar) is written into the interface specific log-file (in debug level 1, section ODBC General Info:) immediately after the interface starts.

The following SQL statements are supported:

• 
  SELECT …
  
• 
  INSERT …
  
• 
  UPDATE …
  
• 
  DELETE …
  

Additionally, the interface allows for calling stored procedures:

{CALL StoredProcedureName( [parameter list])}

If the syntax of an SQL statement is invalid, or the semantics do not comply with any of the interface specific rules / data retrieval strategies (for instance, an appropriate SELECT statement construction is not recognized for an input point), the tag is refused immediately before the first statement execution. The related error message is written into the log-file and the SQL statement(s) (of the tag) are not processed.

Note: It is highly recommended to test a new query for the interface with the MS Query tool (such a query is then more likely to be accepted by the interface). Current versions of MS Query also support placeholders ('?'), so even complex queries can be graphically produced and tested before handed over to the RDBMSPI Interface.

Note: The interface exhibits the ODBC 3.x behavior; that is, it sets the SQL_OV_ODBC3 environment attribute after it starts. Some ODBC drivers appear to have problems with this and the interface cannot connect then. The following error might appear:
SQLConnect [C][01000]: [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr() ODBC function description

Should this error appear, check if the latest MDAC version is installed and also consult the ODBC driver documentation in regards to ODBC 3.x and ODBC 2.x behavior.