** SQLConnect(sConnect)

Makes a connection to a database system, and returns a handle to the connection for use by the other SQL functions. Through this connection, you can execute SQL statements in the specified database. You must call this function before any other SQL function.

You only require one connection for each database system that is to be accessed (e.g. Oracle, dBASE, Excel, etc.). You should not use a SQL database for storage of real-time data (such as alarms), because SQL databases do not provide real-time performance when accessing database data. Only use a SQL database where data transfer is not critical (for example, recipes or reports). If you try to use SQL to store real-time data, CitectSCADA's performance could be greatly degraded.

** SQLAppend(hSQL, String)

This appends a statement string to the SQL buffer. Cicode cannot send a SQL statement that is longer than 255 characters. If you have a SQL statement that is longer than the 255-character limit, you can split the statement into smaller strings, and use this function to append the statements in the SQL buffer.

** SQLSet(hSQL, String)

Sets a statement string in the SQL buffer.

EXAMPLE of using the above code is:

hSQL = SQLConnect("DRV=QEDBF");
SQLBeginTran(hSQL);
SQLSet(hSQL, "SELECT *")
SQLAppend(hSQL, " FROM EMP");
SQLAppend(hSQL, " ORDER BY last_name");
SQLExec(hSQL, "");

** SQLBeginTran(hSQL)

Starts a database transaction. When you make a transaction, your changes are not written to the database until you call the SQLCommit() function. Alternatively, you can use the SQLRollBack() function to discard all changes made during the transaction.

After you begin a transaction, you must call either SQLCommit() to save the changes or SQLRollBack() to discard the changes. These functions complete the transaction and release all database locks. Unless you complete the transaction, you cannot successfully disconnect the SQL connection. A single database connection can only handle one transaction at a time. After you call SQLBeginTran(), you must complete that transaction before you can call SQLBeginTran() again.

If you disconnect from a database while a transaction is active (not completed), CitectSCADA automatically "rolls back" the transaction. Any changes you made to the database in that transaction are discarded. You do not need to begin a transaction to modify a database. Any changes you make to a database before you call SQLBeginTran() are automatically committed, and no database locks are held. The SQLBeginTran() function is not supported by all databases. If you have difficulty using the function, check that both your database and ODBC driver support transactions. Refer to the documentation for your database for more information on transactions.

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

See Also: ODBC Compatibility

Related Functions: SQLCommit, SQLConnect, SQLDisconnect, SQLEnd, SQLErrMsg, SQLExec, SQLFieldInfo, SQLGetField, SQLInfo, SQLNext, SQLNoFields, SQLNumChange, SQLRollBack, SQLTraceOff, SQLTraceOn

Examples:

/* Increase each employee's salary and superannuation by a specified amount. If any errors occur, the changes are aborted */
INT
FUNCTION
PayIncrease(STRING sIncrease)

INT hSQL;
INT Count1;
INT Count2;
hSQL = SQLConnect("DRV=QEDBF");

    SQLBeginTran(hSQL);
    SQLExec(hSQL, "UPDATE C:\DATA\EMPLOYEE SET Salary = Salary + " +sIncrease);
    Count1 = SQLNumChange(hSQL);
    SQLExec(hSQL, "UPDATE C:\DATA\EMPLOYEE SET Super = Super + " +sIncrease);
    Count2 = SQLNumChange(hSQL);
    IF Count1 = Count2 THEN
        SQLCommit(hSQL);
    ELSE
        SQLRollBack(hSQL);
    END
    SQLEnd(hSQL);
    SQLDisconnect(hSQL);
END

NOTES:

The above information can be found in the Citect Help Topics (in Citect Explorer click on--> Help click on--> "Citect Help Topics")

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.

String: The statement string to append to the SQL buffer