SQLCancel function (CLI) - Cancel statement

Specification:

• CLI 1.1
• ODBC 1.0
• ISO CLI

Syntax

SQLRETURN   SQLCancel        (SQLHSTMT          StatementHandle);  /* hstmt */

Function arguments

Usage

After SQLExecDirect() or SQLExecute() returns SQL_NEED_DATA to solicit for values for data-at-execution parameters, SQLCancel() can be used to cancel the data-at-execution sequence for sending and retrieving long data in pieces. SQLCancel() can be called any time before the final SQLParamData() in the sequence. After the cancellation of this sequence, the application can call SQLExecute() or SQLExecDirect() to re-initiate the data-at-execution sequence.

If no processing is being done on the statement, SQLCancel() has no effect. Applications should not call SQLCancel() to close a cursor, but rather SQLFreeStmt() should be used.

Canceling queries on host databases

To call SQLCancel() against a server which does not have native interrupt support (such as DB2® for z/OS®, Version 7 and earlier, and IBM® DB2 for IBM i), the INTERRUPT_ENABLED option must be set when cataloging the DCS database entry for the server.

When the INTERRUPT_ENABLED option is set and SQLCancel() is received by the server, the server drops the connection and rolls back the unit of work. The application receives an SQL30081N error indicating that the connection to the server has been terminated. In order for the application to process additional database requests, the application must establish a new connection with the database server.

Canceling asynchronous processing

After an application calls a function asynchronously, it calls the function repeatedly to determine whether it has finished processing. If the function is still processing, it returns SQL_STILL_EXECUTING.

After any call to the function that returns SQL_STILL_EXECUTING, an application can call SQLCancel() to cancel the function. If the cancel request is successful, SQL_SUCCESS is returned. This message does not indicate that the function was actually canceled; it indicates that the cancel request was processed. The application must then continue to call the original function until the return code is not SQL_STILL_EXECUTING. If the function was successfully canceled, the return code is for that function is SQL_ERROR and SQLSTATE HY008 (Operation was Canceled.). If the function succeeded by completing its normal processing, the return code is SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. If the function failed for reasons other than cancellation, the return code is SQL_ERROR and an SQLSTATE other than HY008 (Operation was Canceled.).

Canceling functions in multithread applications

In a multithread application, the application can cancel a function that is running synchronously on a statement. To cancel the function, the application calls SQLCancel() with the same statement handle as that used by the target function, but on a different thread. How the function is canceled depends upon the operating system. The return code of the SQLCancel() call indicates only whether CLI processed the request successfully. Only SQL_SUCCESS or SQL_ERROR can be returned; no SQLSTATEs are returned. If the original function is canceled, it returns SQL_ERROR and SQLSTATE HY008 (Operation was Canceled.).

If an SQL statement is being executed when SQLCancel() is called on another thread to cancel the statement execution, it is possible that the execution succeeds and returns SQL_SUCCESS, while the cancel is also successful. In this case, CLI assumes that the cursor opened by the statement execution is closed by the cancel, so the application will not be able to use the cursor.

Return codes

Diagnostics

Restrictions

None.