SQLExec

Executes an SQL query on a database. With this function, you can execute any SQL query or command supported by the SQL database. Only "CHAR" type fields are supported in database tables.

Keywords such as "DATE", "TIME", and "DESC" cannot be used as field names by some database systems. To use fields with these names, you need to append underscores to the names (for example, "TIME_", "DATE_", "DESC_").

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. Similarly, CitectSCADA 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.

Syntax

SQLExec(hSQL, sSelect)

hSQL:

The handle to the SQL connection, returned from the SQLConnect() function. The SQL connection handle identifies the table where details of the associated SQL connection are stored.

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

SQLBeginTran, SQLCommit, SQLConnect, SQLDisconnect, SQLEnd, SQLErrMsg, SQLFieldInfo, SQLGetField, SQLInfo, SQLNext, SQLNoFields, SQLNumChange, SQLRollBack, SQLTraceOff, SQLTraceOn

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','CITECT')";

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 CitectSCADA. 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='CITECT' 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