You can use SQL Queries in your ST programs to access the ClearSCADA database.
NOTE: SQL Queries can only be used in ST programs to access the ClearSCADA database. They cannot be used to access other databases.
ClearSCADA includes an extension to the IEC-1131 standard that allows you to use Structured Query Language (SQL) to query the ClearSCADA database. When you use an SQL Query in an ST Program, the SQL Query has to request data for the values specified in the STRUCT data structure or DATABASE_OBJECT structure (to which the values from the resultset are passed). This means that the data types specified in your STRUCT or DATABASE_OBJECT structures have to match the data types of the columns in your SQL query. When using DATABASE_OBJECT structures, the names of the values in the DATABASE_OBJECT also have to match the names of the columns in the SQL query.
When you use an SQL query to provide values for a STRUCT data structure, the values from the SQL query's resultset can only be read. STRUCT data structures cannot be used for writing to the database or calling methods. The names of the values defined in the STRUCT data structure can be anything you choose, but the data types of the values have to match the data types of the columns in your SQL query.
You can use an SQL query and a DATABASE_OBJECT structure to read the values in the resultset, write new values to the database objects represented by the rows in the resultset, and access the methods for the objects represented by the rows in the resultset. This is possible as each row of values returned for a DATABASE_OBJECT structure represent the values of a specific item in the database.
The values and value types of the DATABASE_OBJECT structure have to match the column names and value types defined in the SQL query (including the Id column which is automatically added to DATABASE_OBJECT structures).
Typically, you will use DATABASE_OBJECT structures with SQL queries more often than STRUCT data structures, as DATABASE_OBJECT structures allow for greater interaction with the database.
NOTE: When you include SQL queries in ST programs, and use braces '{' and '}' within SQL statements, you should use the '^' escape character before each brace.
For example:
rsPoints AT %S(SELECT ID, CurrentValueFormatted, CurrentTime FROM CSIMPLEPOINT WHERE CURRENTTIME = ^{ OPC 'D' ^}): RESULTSET OF Point;
Further Information
STRUCT data structures: Using Structured Data Types to Group Internal Values.
DATABASE_OBJECT structures: Using Database Object Structures to Access Direct Variables.
Using an ST Program to Call an SQL Query.
Inserting, Updating, and Deleting Values by Using SQL in an ST Program.