SQLExec

Executes an SQL query on a database. With this function, you can execute any SQL query or command supported by the SQL database.

Note: All types of fields can be requested in statements, but SCADA has to convert values of the fields to MBCS 8-bit strings which is not always possible. For example either single byte database strings or numbers can be converted to MBCS 8-bit strings, multi-byte strings can be converted to MBCS (their proper presentation depends on correct setup of SCADA and OS), while blobs cannot be encoded at all.

Data obtained by this function is stored in the default recordset. The default recordset is always a connected recordset. Connected recordsets fetch only small portion of data when the query is executed, but later they have to fetch further portions when recordset functions are used. This kind of recordset needs open connections to DB, but they need little memory.

The SQLNext() function needs to be called after the SQLExec() function before you can access data in the first record.

Only one query can be active at a time, so there is no need to end one query before you execute another query; each time you call SQLExec(), the previous query (through a previous SQLExec() call) is automatically ended. It means that each query executed from SQLExec cleans the default recordset.

Queries are queued for execution and a result from one query overwrites the result from preceding one. Similarly, Citect SCADA automatically ends the latest query when it disconnects the database, even if you have not called SQLEnd(). However, the SQLEnd() function aids efficiency; SQLEnd() releases the memory that was allocated when the latest query was executed.

This function is a blocking function and should not be called from a foreground task.

Queries which are built on the basis of user data, for example inputed by users via graphics pages or forms, may be prone to SQL Injection attacks. In such case, try to limit the risk by using CiCode functions from parameterized queries group and refer to a professional advice in this matter.

Note: SECURITY BREACH VIA SQL INJECTION
- Validate all textbox entries using validation controls, regular expressions, code
- Use parameterized SQL or stored procedures
- Use a limited access account to connect to the database

Syntax

SQLExec(hGeneral, sSelect)

hGeneral:

The handle either to the DB connection object (returned from either SQLCreate() or SQLConnect() function) or to the query handle (returned from SQLQueryCreate()). When it is the connection handle and sSelect is an empty string, the operation is performed on the first query in that DB connection object. When it is the query handle, the operation is performed on that query through the DB object which is associated to it.

sSelect:

The SQL query to be sent to the SQL database.

Return Value

0 (zero) if successful, otherwise an error number is returned. (For details of the 307 error code, call the SQLErrMsg() function).

Related Functions

SQLCreate, SQLOpen, SQLClose, SQLDispose, SQLConnect, SQLDisconnect, SQLInfo, SQLSet, SQLAppend, SQLExec, SQLGetRecordset, SQLCall, SQLGetScalar, SQLEnd

Example

These examples assume that the following tables are setup in a SQL server (with the name configured in Windows Control Panel) and opened with the SQLConnect() function:

PEOPLE

PHONE

Each SQL string (sSQL) should be encased within the SQLExec function, for example:

SQLExec(hSQL, sSQL);
To add a record to a table:
sSQL = "INSERT INTO PEOPLE (SURNAME, FIRSTNAME, OCCUPATION, DEPARTMENT)
             VALUES('ALLEN','MATTHEW','PROGRAMMER','SCADA')";

This SQL command changes the PEOPLE table to:

PEOPLE

To remove records from a table:

sSQL = "DELETE FROM (PEOPLE, PHONE) WHERE SURNAME='MARTIAN'";
SQLBeginTran(hSQL);
SQLExec(hSQL,sSQL);
IF (Message("Alert", "Do you really want to DELETE MARTIAN", 33) = 0) THEN
    SQLCommit(hSQL);
ELSE
    SQLRollback(hSQL);
END

Assuming that OK was clicked on the Message Box, the tables change to:

PEOPLE

PHONE

To change a record:

sSQL = "UPDATE PEOPLE SET OCCUPATION='SUPPORT' WHERE FIRSTNAME='LARRY'";

This SQL command changes the PEOPLE table to:

PEOPLE

To select a group of records from a table:

sSQL = "SELECT SURNAME FROM PEOPLE WHERE OCCUPATION='ENGINEER'";

This SQL command will return the following table back to Citect SCADA. The table can then be accessed by the SQLNext() function and the SQLGetField() functions.

CITECT TABLE for hSQL

You can also select data using a much more complete SQL string, for example:

sSQL = "SELECT (SURNAME, OCCUPATION, NUMBER) FROM (PEOPLE, PHONE)
WHERE DEPARTMENT='SCADA' AND PEOPLE.SURNAME = PHONE.SURNAME";

This SQL command retrieves the following table:

To extract information from a table:

STRING sInfo[3][10]
int i = 0;
WHILE ((SQLNext(hSQL) = 0) and (i < 10)) DO
    sInfo[0][i] = SQLGetField(hSQL, "SURNAME");
    sInfo[1][i] = SQLGetField(hSQL, "OCCUPATION");
    sInfo[2][i] = SQLGetField(hSQL, "NUMBER");
END

This code example leaves the information in the sInfo two dimensional array as follows:

sInfo

See Also

SQL Functions

Published June 2018