You can use the INSERT, UPDATE, and DELETE Data Manipulation Language (DML) statements in SQL queries within your ST programs. These statements are especially useful when creating, updating, or removing values in data tables.
ATTENTION: Although ClearSCADA supports the use of INSERT, UPDATE and DELETE with any of the database tables, we strongly recommend that you do not use these statements with any query that affects the configuration of the database on a regular basis. Frequent alterations to the configuration may cause slow performance and affect the running of your system. This does not affect data tables as they do not store configuration.
If you are using the INSERT, UPDATE and DELETE statements, they need to be declared in your SQL query. When the ST program runs, it will create a queue of the insertions, updates and deletions it needs to make and will only perform these actions once the program has executed.
To use the INSERT, UPDATE or DELETE statement, you need to include the statement keyword in your SQL query and use %D to declare that DML statements are being used. You can use multiple DML statements in the same ST program—when the program has executed, the insertions, updates and deletions are performed in the order in which they appear in the code.
If you wish to include parameters for an insertion, you can do so by using the ? character to represent the values and the WITH_PARAMS keyword to indicate that there are parameters (see Passing Multiple Parameters into an SQL Query in an ST Program).
Example:
The following example shows how an ST program can make use of the DML statements. In this example, the program:
- Deletes the values from a data table item called 'LogicDataTable'.
- Inserts an entry in the data table with STRING[64] and DINT type values. The values will be used for the StringField and NumberField fields for the new entry in the data table. In this case, the values are: 20 and 'First Record'.
- Updates the data table so that NumberField entries are increased by 5.
- Inserts another entry into the data table, again with STRING[64] and DINT type values. The values used for the entry are 10 and 'Second Record'.
- Deletes entries that have a specific NumberField value (in this case, 25).
The ST program code is as follows:
PROGRAM test
- VAR
- Num : DINT;
- Str : STRING[64];
- END_VAR
- VAR
- Init AT %D(DELETE FROM LogicDataTable );
- R1 AT %D(INSERT INTO LogicDataTable VALUES ( ?, ? ) ) WITH_PARAMS Str, Num;
- R2 AT %D(UPDATE LogicDataTable SET NumberField = NumberField + 5 );
- R3 AT %D(DELETE FROM LogicDataTable WHERE NumberField = ?) WITH_PARAMS Num;
- Init AT %D(DELETE FROM LogicDataTable );
- END_VAR
- Init.Execute();
- Num := 20;
- Str := 'First Record';
- R1.Execute();
- R2.Execute();
- Num := 10;
- Str := 'Second Record';
- R1.Execute();
- Num := 25;
- R3.Execute();
- END_PROGRAM
If you look at the program code, you will see that in the second VAR declaration there are 4 variables; 1 for each DML statement:
- The Init variable contains a query that instructs the program to delete data from the LogicDataTable object when the variable is called.
- The R1 variable contains a query that instructs the program to insert values into the LogicDataTable object when the variable is called - it will create a row with 2 values of a specific type (defined in the first VAR declaration).
- The R2 variable contains a query that instructs the program to update the NumberField fields in the LogicDataTable object so that they increase by 5.
- The R3 variable contains a query that instructs the program to delete entries from that LogicDataTable that have a specific NumberField value.
In the code following the second VAR declaration, you can see the order in which the methods will be called:
- When the program is executed, the Execute method will be called on the query in the Init variable: data will be removed from the LogicDataTable object.
- The Execute method will be called on the query in the R1 variable. This will create a new entry in the LogicDataTable that has 20 for the NumberField value and 'First Record' for the StringField value.
- The Execute method will be called on the query in the R2 variable. This will update the row in the LogicDataTable so that the row has 5 added to its NumberField value. So the row will have a NumberField of 25.
- The Execute method will be called on the query in the R1 variable again. This will create another new row, this time with the values 10 and 'Second Record'.
Finally, the Execute method will be called on the query in the R3 variable. This will delete the entries in the LogicDataTable that have 25 as their value in the NumberField field. So, the first row that was inserted by the program will be deleted.
Further Information
WITH_PARAMS: Passing Multiple Parameters into an SQL Query in an ST Program.