Executes an SQL query on a database and returns a handle to any resulting disconnected recordset. All recordsets created in this way should be closed by the SQLEnd() function. If unsuccessful, the function returns an invalid handle and an error code is returned (can be tested by IsError()). In case of no data returned, the function returns an invalid handle and no error. With this function, you can execute any SQL query or command supported by the SQL database, but it is designed mainly to work with queries returning data like for example SELECT. Queries which don't return database data, like INSERT or UPDATE, should be executed via SQLCall.
Recordsets produced by this function are disconnected. The disconnected recordsets fetch the data from the DB when the query is executed and thus they don't need an open connection when recordset functions are used. This kind of recordset improves speed of access to data after executing the query, but may consume significant amounts of memory.
Creating disconnected recordsets without releasing them may lead to utilizing all available memory, which can result in degraded performance.
NOTICE |
EXCESSIVE MEMORY USAGE Limit the number of disconnected recordsets using the [SQL]MaxConnections parameter. Release unused disconnected recordsets. Test projects using disconnected recordsets prior to deployment in production. Failure to follow these instructions can result in equipment damage. |
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.
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.
NOTICE |
SECURITY BREACH VIA SQL INJECTION - Validate all textbox entries using validation controls, regular expressions and code Failure to follow these instructions can result in equipment damage. |
Syntax
SQLGetRecordset(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
The handle to a recordset holding the result of the query. If unsuccessful an invalid handle and an error code are returned (for details call the SQLErrMsg() function). If no data is returned, an invalid handle and no error message are returned. The presence of an error can be tested by calling the IsError() CiCode function
Related Functions
SQLCreate, SQLOpen, SQLClose, SQLDispose, SQLConnect, SQLDisconnect, SQLInfo, SQLSet, SQLAppend, SQLExec, SQLGetRecordset, SQLCall, SQLGetScalar, SQLEnd
Example
See SQLCreate.
See Also
Published June 2018