Asynchronous execution of CLI functions

CLI can run a subset of CLI functions asynchronously. Asynchronous execution is possible for those functions that normally send a request to the server and then wait for a response.

For asynchronous functions, the CLI driver returns control to the application after calling the function but before that function has finished executing. The functions return SQL_STILL_EXECUTING each time they are called until they are finished running, at which point they return a different value (for example, SQL_SUCCESS). Rather than waiting for a response, a function executing asynchronously returns control to the application. The application can then perform other tasks and poll the function until a return code other than SQL_STILL_EXECUTING is returned. Refer to the SQL_ATTR_ASYNC_ENABLE connection or statement attribute for a list of functions that can be executed asynchronously.

In order for an application to run CLI functions asynchronously, the application must include following function calls:
  1. A call to the function SQLGetInfo() with the SQL_ASYNC_MODE option to ensure support for asynchronous calls.
  2. A call to SQLSetConnectAttr() or SQLSetStmtAttr() with the SQL_ATTR_ASYNC_ENABLE attribute to enable asynchronous calls once it has been established that there is support for asynchronous calls.
  3. A call to a function that supports asynchronous execution and polling of the asynchronous function. When the application calls a function that can be run asynchronously, one of two things can happen:
    • If the function will not benefit from being run asynchronously, CLI can decide to run it synchronously and return the normal return code (other than SQL_STILL_EXECUTING). In this case the application runs as it would if the asynchronous mode had not been enabled.
    • CLI will perform some minimal processing (such as checking the arguments for errors), then pass the statement on to the server. Once this quick processing is complete a return code of SQL_STILL_EXECUTING is returned to the application.

Functions that can be called during asynchronous execution

Once a function has been called asynchronously, only the original function, SQLAllocHandle(), SQLCancel(), SQLGetDiagField(), or SQLGetDiagRec() can be called on the statement or the connection associated with StatementHandle, until the original function returns a code other than SQL_STILL_EXECUTING.

Any other function called on StatementHandle or the connection associated with StatementHandle returns SQL_ERROR with an SQLSTATE of HY010 (Function sequence error.).

Diagnostic information while a function is running asynchronously

SQLGetDiagField() returns the following values when it is called on a statement handle that has an asynchronous function executing:
  • The values of SQL_DIAG_CURSOR_ROW_COUNT, SQL_DIAG_DYNAMIC_FUNCTION, SQL_DIAG_DYNAMIC_FUNCTION_CODE, and SQL_DIAG_ROW_COUNT header fields are undefined.
  • SQL_DIAG_NUMBER header field returns 0.
  • 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 that has an asynchronous function executing.

Cancelling the asynchronous function call

The application can issue a request to cancel any function that is running asynchronously by calling SQLCancel(). A function that has already finished executing cannot be cancelled.

The return code from the SQLCancel() call indicates whether the cancel request was received, not whether the execution of the asynchronous function was stopped.

The only way to tell if the function was cancelled is to call it again, using the original arguments.
  • If the cancel was successful, the function will return SQL_ERROR and an SQLSTATE of HY008 (Operation was Canceled.).
  • If the cancel was not successful, the function will return a value other than SQL_ERROR with an SQLSTATE of HY008. For example, the function might return SQL_STILL_EXECUTING.