SQLCancel() - Cancel statement

Usage

Use SQLCancel() to cancel the following types of processing on a statement:

• Data-at-execution sequences on the current thread.
• Functions running on the statement on another thread.

Canceling a data-at-execution sequence

After SQLExecDirect() or SQLExecute() returns SQL_NEED_DATA to solicit values for data-at-execution parameters, you can use SQLCancel() to cancel the data-at-execution sequence. You can call SQLCancel() any time before the final SQLParamData() in the sequence. After you cancel this sequence, you can call SQLExecute() or SQLExecDirect() to re-initiate the data-at-execution sequence.

If you call SQLCancel() on an statement handle that is not associated with a data-at-execution sequence, SQLCancel() has the same effect as SQLFreeHandle() with the HandleType set to SQL_HANDLE_STMT. You should not call SQLCancel() to close a cursor; instead, use SQLCloseCursor() to close cursors.

Canceling functions in multithreaded applications

When you execute a multithreaded application, you can cancel a function that is running synchronously on another thread. To cancel the function, you must call SQLCancel() on a different thread with the same statement handle as that used by the target function, and set the INTERRUPT keyword in the ODBC initialization file to either INTERRUPT=1 (the default setting) or INTERRUPT=2. In Db2 ODBC, INTERRUPT=1 and INTERRUPT=2 exhibit the same behavior, which is set to always drop the connection on a SQLCancel(). After you call SQLCancel(), Db2 ODBC sets the return code to either SQL_SUCCESS or SQL_ERROR (no SQLSTATE) to indicate whether the cancel request was processed successfully.  If the request was successful, the connection associated with the statement handle is dropped and the canceled function returns SQLCODE -924 and SQLSTATE 58006.  In order for the statement handle to process additional database requests, you must establish a new connection with the database server.

If an SQL statement is being executed when SQLCancel() is called on another thread to cancel the statement execution, it is possible for the execution to succeed and return SQL_SUCCESS while the cancel is also successful. In this case, the connection associated with the statement is dropped regardless of the return code, so you will not be able to process additional database requests on that statement until you re-establish the connection.