A Data Table, named ‘MyDataTable’, is to store values of the points that are located within an ‘Internal Points’ Group in the ClearSCADA database. For each point, the Data Table is to store these values: the point’s name, value, and the time that the point data was last updated. The Data Table is used to take a 'snapshot' of the point data at a moment in time, so that users can view the point data from when the snapshot was made, and compare it with the current 'live' data.

The Data Table is suitably configured in ClearSCADA:

A Structured Text Program is added to the ClearSCADA database. The ST program editor is used to specify the code for populating the Data Table with the relevant records. This particular ST program uses this code:

TYPE

(* The database object structure used to store the results of the result set *)
PtValues : DATABASE_OBJECT(CDBPoint)

Name: STRING;
CurrentValueFormatted: STRING;
CurrentTime: DATE_AND_TIME;

END_DATABASE_OBJECT;

END_TYPE

 

PROGRAM PopulateMyDataTable
VAR

(* SQL query to retrieve point values - this MUST include the ID column *)
Points AT %S(SELECT ID,Name,CurrentValueFormatted,CurrentTime FROM CDBPoint WHERE FullName LIKE 'Internal.%') :RESULTSET OF PtValues;

END_VAR

 

VAR

PtName : STRING;
PtVal : STRING;
PtTime : DATE_AND_TIME;

END_VAR

 

VAR

(* SQL to delete old values from the Data Table *)
Del AT %D(DELETE FROM MyDataTable );

(* SQL to insert current values into the Data Table *)
R1 AT %D(INSERT INTO MyDataTable VALUES ( ?, ?, ? ) ) WITH_PARAMS PtName, PtVal, PtTime;

END_VAR

 

(* Delete old values from the Data Table *)
Del.Execute();

(* Get the first record in the result set *)
Points.First();

(* While there is a valid record in the result set... *)
WHILE Points.Valid DO

(* ...transfer record from result set to INSERT query parameter variables... *)
PtName := Points.Value.Name;
PtVal := Points.Value.CurrentValueFormatted;
PtTime := Points.Value.CurrentTime;

(* ...then execute the INSERT query to insert that record's values into the Data Table *)
R1.Execute();

(* Get the next record from the result set *)
Points.Next();

END_WHILE;

END_PROGRAM

As can be seen from the above code:

• A DATABASE_OBJECT structure is used to declare and access the direct variables that are used in the program.
• The program uses an SQL query to retrieve the required point values from the database and store those values in a RESULTSET. (Remember that when using a DATABASE_OBJECT structure, the column names in the SQL must include the ID column first, followed by the member names from the DATABASE_OBJECT definition. (All DATABASE_OBJECT structures have an ID 'member’ that is implicit and is not included in the actual DATABASE_OBJECT definition.).)
• The internal variables that are used to transfer the records from the RESULTSET into the Data Table are declared.
• Whenever the ST program is executed, SQL first deletes the old values from the Data Table and then populates that table with the latest values from the relevant records. (The .First() and .Next() methods are used to cycle through the records in the RESULTSET, in order to populate the table.)