By default, drivers execute ODBC functions synchronously; that is, the application calls a function and the driver does not return control to the application until it has finished executing the function. However, some functions can be executed asynchronously; that is, the application calls the function and the driver, after minimal processing, returns control to the application. The application can then call other functions while the first function is still executing.
Asynchronous execution is supported for most functions that are largely executed on the data source, such as the functions to prepare and execute SQL statements, retrieve metadata, and fetch data. It is most useful when the task being executed on the data source takes a long time, such as a complex query against a large database.
Note In general, applications should execute functions asynchronously only on single-threaded operating systems. On multithread operating systems, applications should execute functions on separate threads, rather than executing them asynchronously on the same thread. No functionality is lost if drivers that operate only on multithread operating systems do not support asynchronous execution.
Asynchronous execution is controlled on either a per-statement or per-connection basis, depending on the data source. That is, the application specifies not that a particular function is to be executed asynchronously, but that any function executed on a particular statement or a particular connection is to be executed asynchronously. To find out which one is supported, an application calls SQLGetInfo with an option of SQL_ASYNC_MODE. SQL_AM_CONNECTION is returned if connection-level asynchronous execution is supported; SQL_AM_STATEMENT if statement-level asynchronous execution is supported.
To specify that functions executed with a particular statement are to be executed asynchronously, the application calls SQLSetStmtAttr with the SQL_ATTR_ASYNC_ENABLE attribute and sets it to SQL_ASYNC_ENABLE_ON. If connection-level asynchronous processing is supported, the SQL_ATTR_ASYNC_ENABLE statement attribute is read-only, and its value is the same as the connection attribute of the connection on which the statement was allocated. It is driver-specific whether the value of the statement attribute is set at statement allocation time or later. Attempting to set it will return SQL_ERROR and SQLSTATE HYC00 (Optional feature not implemented).
The maximum number of active concurrent statements in asynchronous mode that the driver can support on a given connection can be determined by calling SQLGetInfo with the SQL_MAX_ASYNC_CONCURRENT_STATEMENTS option.
To specify that functions executed with a particular connection are to be executed asynchronously, the application calls SQLSetConnectAttr with the SQL_ATTR_ASYNC_ENABLE attribute and sets it to SQL_ASYNC_ENABLE_ON. All future statement handles allocated on the connection will be enabled for asynchronous execution; it is driver-defined whether existing statement handles will be enabled by this action. If SQL_ATTR_ASYNC_ENABLE is set to SQL_ASYNC_ENABLE_OFF, all statements on the connection are in synchronous mode. Note that an error is returned if asynchronous execution is enabled while there is an active statement on the connection.
When the application executes a function with a statement that is enabled for asynchronous processing, the driver performs a minimal amount of processing (such as checking arguments for errors), hands processing to the data source, and returns control to the application with the SQL_STILL_EXECUTING return code. The application then performs other tasks. To determine when the asynchronous function has finished, the application polls the driver at regular intervals by calling the function with the same arguments as it originally used. If the function is still executing, it returns SQL_STILL_EXECUTING; if it has finished executing, it returns the code it would have returned had it executed synchronously, such as SQL_SUCCESS, SQL_ERROR, or SQL_NEED_DATA. For example:
SQLHSTMT hstmt1;
SQLRETURN rc;
// Specify that the statement is to be executed asynchronously.
SQLSetStmtAttr(hstmt1, SQL_ATTR_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON, 0);
// Execute a SELECT statement asynchronously.
while ((rc=SQLExecDirect(hstmt1,"SELECT * FROM Orders",SQL_NTS))==SQL_STILL_EXECUTING) {
// While the statement is still executing, do something else.
// Do not use hstmt1, as it is being used asynchronously.
}
// When the statement has finished executing, retrieve the results.
Whether the driver executes the function synchronously or asynchronously is up to the driver. For example, suppose the result set metadata is cached in the driver. In this case, it takes very little time to execute SQLDescribeCol and the driver should simply execute the function, rather than artificially delaying execution. On the other hand, if the driver needs to retrieve the metadata from the data source, it should return control to the application while it is doing this. Thus, the application must be able to handle a return code other than SQL_STILL_EXECUTING when it first executes a function asynchronously.
While the function is being executed asynchronously, the application can call functions on any other statements. It can also call functions on any connection except the one associated with the asynchronous statement. On the asynchronous statement, the application can call only the asynchronously executing function, or SQLCancel, SQLGetDiagField, or SQLGetDiagRec. SQLGetDiagField or SQLGetDiagRec can be called on an asynchronously executing statement handle to return a header diagnostic field but not a record diagnostic field. On the connection associated with the asynchronous statement, the application can call only SQLAllocHandle (to allocate a statement handle), SQLGetDiagField, SQLGetDiagRec, or SQLGetFunctions. If the application calls any other function with the asynchronous statement or with the connection associated with that statement, the function returns SQLSTATE HY010 (Function sequence error). The application can call any function using handles other than the original statement handle or the original connection handle. For example:
SQLHDBC hdbc1, hdbc2;
SQLHSTMT hstmt1, hstmt2, hstmt3;
SQLCHAR *SQLStatement = "SELECT * FROM Orders";
SQLUINTEGER InfoValue;
SQLRETURN rc;
SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt2);
SQLAllocHandle(SQL_HANDLE_STMT, hdbc2, &hstmt3);
// Specify that hstmt1 is to be executed asynchronously.
SQLSetStmtAttr(hstmt1, SQL_ATTR_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON, 0);
// Execute hstmt1 asynchronously.
while ((rc = SQLExecDirect(hstmt1, SQLStatement, SQL_NTS)) == SQL_STILL_EXECUTING) {
// The following calls return HY010 because the previous call to SQLExecDirect is
// still executing asynchronously on hstmt1. The first call uses hstmt1 and the
// second call uses hdbc1, on which hstmt1 is allocated.
SQLExecDirect(hstmt1, SQLStatement, SQL_NTS); // Error!
SQLGetInfo(hdbc1, SQL_UNION, (SQLPOINTER) &InfoValue, 0, NULL); // Error!
// The following calls do not return errors. They use a statement handle other than
// hstmt1 or a connection handle other than hdbc1.
SQLExecDirect(hstmt2, SQLStatement, SQL_NTS); // OK
SQLTables(hstmt3, NULL, 0, NULL, 0, NULL, 0, NULL, 0); // OK
SQLGetInfo(hdbc2, SQL_UNION, (SQLPOINTER) &InfoValue, 0, NULL); // OK
}
When an application calls a function to determine if it is still executing asynchronously, it must use the original statement handle. This is because asynchronous execution is tracked on a per-statement basis. The application must also supply valid values for the other arguments—the original arguments will do—to get past error checking in the Driver Manager. However, after the driver checks the statement handle and determines that the statement is executing asynchronously, it ignores all other arguments.
While a function is executing asynchronously
—that is, after it has returned SQL_STILL_EXECUTING and before it returns a different code—the application can cancel it by calling SQLCancel with the same statement handle. This is not guaranteed to cancel function execution. For example, the function might have already finished. Furthermore, the code returned by SQLCancel indicates whether SQLCancel successfully attempted to cancel the function, not whether it actually did cancel the function. To determine if the function was canceled, the application calls the function again. If the function was canceled, it returns SQL_ERROR and SQLSTATE HY008 (Operation canceled). If the function was not canceled, it returns another code, such as SQL_SUCCESS, SQL_STILL_EXECUTING, or SQL_ERROR with a different SQLSTATE.To disable asynchronous execution of a particular statement when the driver supports statement-level asynchronous processing, the application calls SQLSetStmtAttr with the SQL_ATTR_ASYNC_ENABLE attribute and sets it to SQL_ASYNC_ENABLE_OFF. If the driver supports connection-level asynchronous processing, the application calls SQLSetConnectAttr to set SQL_ATTR_ASYNC_ENABLE to SQL_ASYNC_ENABLE_OFF, which disables asynchronous execution of all statements on the connection.
When SQLGetDiagField is called on a statement handle on which a function is currently executing asynchronously, the following values are returned:
What the SQL_DIAG_CURSOR_ROW_COUNT, SQL_DIAG_DYNAMIC_FUNCTION,
SQL_DIAG_DYNAMIC_FUNCTION_CODE, and SQL_DIAG_ROW_COUNT header fields return are undefined.
The SQL_DIAG_NUMBER header field returns 0.
The SQL_DIAG_RETURN_CODE header field returns SQL_STILL_EXECUTING.
All record fields return SQL_NO_DATA.
SQLGetDiagRec always returns SQL_NO_DATA when it is called on a statement handle on which a function is currently executing asynchronously.
For a list of functions that can be executed asynchronously, see the SQL_ATTR_ASYNC_ENABLE attribute in the SQLSetStmtAttr function description.