Learn about SQL examples for PI Interface for RDBMS

As detailed in the Learn how to specify SQL queries section, and particularly in the Learn about query execution topic, the SQL language that you use is determined by the underlying SQL database and the ODBC driver. SELECT, INSERT, UPDATE, DELETE and {CALL()} SQL statements are used to transfer data between Data Archive and an RDBMS interface.

You can combine the above listed statements, but there are certain restrictions depending on their use with input and output points, as detailed in the Learn how to specify SQL queries section.

You can also write whatever subqueries are supported by the underlying database. (You may want to refer to Microsoft ODBC Programmer's Reference.) There are no restrictions to writing subqueries within PI Interface for RDBMS.

The example suite below shows various approaches for transferring data between Data Archive and an RDBMS interface. Examples are illustrative and are not installed as part of the interface. To implement the examples, perform the following:

1. Store the SQL queries in a text file in a directory that is configured for SQL files on the PI ICU rdbodbc > Startup Parameters tab; alternatively, define the SQL queries in the ExtendedDescriptor of a PI tag.

2. Create the PI points, setting the example-specific attributes as described in the example.

3. For all points in these examples, the following settings apply:
   

   Point attribute	Description
   Location1	Interface instance ID
   Location2	0: Processes only the first row 1: Processes all rows
   Location3	Data distribution strategy: 0: Single -1: Tag Distribution -2: RxC (Row x Column)
   Location4	Scan class number
   Location5	0: Exception reporting is enabled 1: Exception reporting is turned off 2: Add events @ the same timestamp 3: For tag distribution and RxC - process only newer data 4: Enables the full sync mode
   InstrumentTag	Name of the query file
   PointSource	Matches point source configured for interface instance
   SourcePoint	For output tags - name of the tag that triggers the execution if its snapshot value changed

4. Create the RDBMS table as specified and populate the table with sample data.

   As shown in the examples, the names of the SQL query files, RDBMS tables, and table columns may be whatever you want.

   Note: In some examples, the semantic meaning of columns in the SELECT list is predetermined and the interface assumes that the first column is a timestamp, that the second is a value, and that the third is status. If every column in the SELECT list is aliased using the set of the "PI_" keywords, the meaning of individual columns is determined according to these keywords.