Executing SQL commands

Using CitectSCADA > Exchanging Data with Other Applications > Using Structured Query Language > Executing SQL commands

Executing SQL commands

SQL allows you to manipulate data in a non-procedural manner; you specify an operation in terms of what is to be done, not how to do it. SQL commands allow you to:

Create tables in the database.
Store information in tables.
Select exactly the information you need from your database.
Make changes to your data and to the structure of a table.
Combine and calculate data.

The SQLExec() function executes any SQL command that your SQL server supports. For example, to create a database table, you would execute the SQL "CREATE TABLE " command:

SQLExec (hSQL, "CREATE TABLE recipe ('Name' CHAR(16), 'Water' CHAR(8),
 'Sugar' CHAR(8), 'Flour' CHAR(8), 'Salt' CHAR(8), 'Yeast' CHAR(8), 'Milk' CHAR(8))");

To add records into the database table, use the "INSERT INTO" command. The command has the following syntax:

INSERT INTO <filename> [(<col_name>, . . .)] VALUES (<expr>, . . .)

This command adds the values for each field in the table, for example:

SQLExec(hSQL, "INSERT INTO recipe VALUES ('Bread', '10', '5', '7', '1', '1', '2')");

Column names are optional; however, if you omit column (field) names, the values are inserted into the fields in the same order as the values.

To read data from an SQL database, use the SQL "SELECT" command. You can use the "SELECT" command to read an entire set of records, or a row, from the table. You can then use the SQLGetField() function to read the data in each field, for example:

SQLExec(hSQL, "SELECT * FROM recipe WHERE NAME = 'Bread'");

If SQLNext(hSQL) = 0 Then

 PLC_Water = SQLGetField(hSQL, "WATER");

   PLC_Sugar = SQLGetField(hSQL, "SUGAR");

   PLC_Flour = SQLGetField(hSQL, "FLOUR");

   PLC_Salt = SQLGetField(hSQL, "SALT");

	 PLC_Yeast = SQLGetField(hSQL, "YEAST");

   PLC_Milk = SQLGetField(hSQL, "MILK");

END

To delete database records, use the SQL "DELETE" command. The command has the following syntax:

DELETE FROM <filename> [WHERE <conditions>]

This command deletes values from the table, for example:

SQLExec(hSQL, "DELETE FROM recipe WHERE NAME = 'Bread'");