Logic Guide - Using an ST Program to Call an SQL Query (ST Programs)

Using an ST Program to Call an SQL Query

You can code an ST program to retrieve information from the database via an SQL Query. The requested data is returned in a RESULTSET and can then be used in the ST Program's calculations.

Each Query returns a RESULTSET that has these variables:

.Valid—This is a Boolean variable that indicates whether the current row is Valid (True is valid; False is invalid).
.Idx—This is a DINT and is the index number of the current row.
.Size—This is a DINT and is the total number of rows in the RESULTSET.
.Value—This corresponds to the Type defined in the Query and represents the requested property of the current row.

The following methods can be used to navigate through the RESULTSET:

.First()—Go to first record in RESULTSET
.Last()—Go to last record in RESULTSET
.Next()—Go to the subsequent record in the RESULTSET
.Prev()—Go to the preceding record in the RESULTSET

NOTE: The SQL Query in an ST Program can only access data in the ClearSCADA database.

The syntax for calling an SQL Query in an ST program is:

TYPE
- <Name of Type for RESULTSET>:<STRUCT or DATABASE_OBJECT>
- <Member Name> : <Type>;
- <Member Name> : <Type>;
- <Method Name>: METHOD;
- <END_STRUCT or END_DATABASE_OBJECT>;
END_TYPE
PROGRAM <Program Name>
VAR
- <Var name> AT %S(SELECT <column 1>, <column 2>,<column 3> FROM <Database Table> WHERE <Filter>) :RESULTSET OF <Name of Type for RESULTSET>;
END_VAR

Where:

Type indicates the start of the type definition.
<Name of Type for RESULTSET> is the name of the user defined type that will contain the data returned by the SQL SELECT Query.
<STRUCT> declares the start of a Structure and <DATABASE_OBJECT> indicates the start of a database object structure. Structures are used when the ST Program only needs to reference one field of an item's data; DATABASE_OBJECT structures are used when the ST Program needs to reference more than one of a database item's fields. When declaring a DATABASE_OBJECT structure, the required database table has to be included in parenthesis after DATABASE_OBJECT, for example, DATABASE_OBJECT(CPointAlgManual).
<Member Name> : <Type>; declares the name and type of a data field that you want to use in your ST Program. For DATABASE_OBJECT structures, the name has to match the name of a column in the SQL Query. You can declare as many member names and types as required.
<Method Name> : METHOD; declares the name of a method that is available to the declared structure. For example, the DATABASE_OBJECT(CPointAlgManual) structure has an Current Value method that can be declared as: CurrentValueFormatted: METHOD; The method can then be called in the ST Program by using the syntax <Variable Name>.Value.CurrentValueFormatted(); where <Variable Name> is the name of the variable that is used to store the RESULTSET of the SQL Query.
<END_STRUCT or END_DATABASE_OBJECT>; ends the structure declaration.
END_TYPE ends the Type declaration.
PROGRAM <Program Name> declares the start of the ST program. The Program Name can be any name you require.
VAR defines the start of the variable declaration.
<Var name> AT %S declares the name of the variable used for the SQL Query and AT %S indicates that the variable will be used to contain data returned from an SQL SELECT Query. You can enter any name for the variable.
(SELECT <column 1>, <column 2>,<column 3> FROM <Database Table> WHERE <Filter>) defines the columns that are to be included in the SQL Query.
The column names have to match the names of columns in the selected database table (and the member names for the DATABASE_STRUCTURE, as these also have to match the columns in the selected database table).
NOTE: When you are using a DATABASE_OBJECT structure, the first column in your SQL Query has to be the Id column. This is because DATABASE_OBJECT structures have an Id field (although it is not included as one of the member names for the DATABASE_OBJECT structures).
The FROM <Database Table> part defines which table in the database the SQL Query is to search for the defined columns, and the WHERE <Filter> part defines any filtering. For example, SELECT CurrentValueFormatted FROM CPointAlgManual WHERE FullName LIKE 'Pumps.%') instructs the Query to search for the CurrentValueFormatted column in the CPointAlgManual table and to filter its results to include only those that are in the 'Pumps' Group or one of its children.
RESULTSET OF <Name of Type for RESULTSET> declares that the results of the Query will be returned into a RESULTSET of the defined type (the name of the type should match the name of the type that was defined at the start of the TYPE declaration).

When you have declared the Type, Members, Methods, and SQL Query as required, you can use the RESULTSET variables and methods in your ST Program. Typically, they are used within WHILE...DO functions.

NOTE: Do not enter the angle brackets < >. As with other syntax descriptions, you should not enter the angle brackets unless specifically instructed to do so.

Example 1:

The following example shows an ST Program that is used to search for internal analog points in a Group named 'Power Simulation'. When the program locates the internal points, it steps through each record and increases the corresponding point's current value by 1:

TYPE
- Point : DATABASE_OBJECT(CPointAlgManual)
  - CurrentValue : LREAL;
  - CurrentState : INT;
- END_DATABASE_OBJECT;
END_TYPE
PROGRAM SQLTestHanddressPts
VAR
- Pts AT %S(SELECT Id, CurrentValue, CurrentState FROM CPointAlgManual WHERE FullName LIKE 'Power Simulation.%') : RESULTSET OF Point;
END_VAR
- WHILE Pts.Valid DO
  - Pts.Value.CurrentValue := Pts.Value.CurrentValue + 1;
  - Pts.Next();
END_WHILE;
END_PROGRAM

Example 2:

The following example shows an ST Program that is used to search for advanced outstations. When the program locates the outstations, it steps through each record and resets the corresponding outstation's communication statistics:

TYPE
- Outstation : DATABASE_OBJECT( CAdvOutstation )
  - ResetCommsStatistics : METHOD;
- END_DATABASE_OBJECT;
END_TYPE
PROGRAM ResetAllOsCommsStats
- VAR
  - Os AT %S(SELECT Id FROM CAdvOutstation) : RESULTSET OF Outstation;
- END_VAR
- WHILE Os.Valid DO
  - Os.Value.ResetCommsStatistics();
  - Os.Next();
- END_WHILE;
END_PROGRAM>

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;