| Specification: | DB2 CLI 1.1 | ODBC 1.0 | ISO CLI |
SQLCancel() can be used to prematurely terminate the data-at-execution sequence described in Sending/Retrieving Long Data in Pieces.
In a multi-threaded application, SQLCancel() will cancel the original request, which will return an SQLSTATE of HY008.
Syntax
SQLRETURN SQLCancel (SQLHSTMT StatementHandle); /* hstmt */
Function Arguments
| Data Type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | input | Statement handle |
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 described in Sending/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.
In DB2 CLI version 2, or when the SQL_ATTR_ODBC_VERSION environment attribute is set to SQL_OV_ODBC2, if an application calls SQLCancel() when no processing is being done on the statement, SQLCancel() has the same effect as SQLFreeStmt() with the SQL_CLOSE option. This is not the case in DB2 CLI version 5, or when the SQL_ATTR_ODBC_VERSION environment attribute is set to SQL_OV_ODBC5. A call to SQLCancel() when no processing is being done on the statement is not treated as SQLFreeStmt() with the SQL_CLOSE option, but has no effect at all. Applications should not call SQLCancel() to close a cursor, but rather SQLFreeStmt() should be used.
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. If the function has finished processing, it returns a different code.
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 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 SQL_ERROR and SQLSTATE HY008 (Operation was cancelled). If the function completed its normal processing, the return code is SQL_SUCCESS or SQL_SUCCESS_WITH_INFO if the function succeeded or SQL_ERROR and a SQLSTATE other than HY008 (Operation was cancelled) if the function failed.
For more information about asynchronous processing, see Asynchronous Execution of CLI.
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. As in canceling a function running asynchronously, the return code of the SQLCancel() indicates only whether DB2 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 cancelled).
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, DB2 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.
For more information about threading, see Writing Multi-Threaded Applications.
Return Codes
Diagnostics
| SQLSTATE | Description | Explanation |
|---|---|---|
| 40003 08S01 | Communication link failure. | The communication link between the application and data source failed before the function completed. |
| HY001 | Memory allocation failure. | DB2 CLI is unable to allocate memory required to support execution or completion of the function. |
| HY013 | Unexpected memory handling error. | DB2 CLI was unable to access memory required to support execution or completion of the function. |
| HY018 | Server declined cancel request. | The server declined the cancel request. |
| HY506 | Error closing a file. | An error occurred when closing the temporary file generated by DB2 CLI when inserting LOB data in pieces using SQLParamData()/SQLPutData(). |
Restrictions
None.
(The complete sample dtlob.c is also available here .)
/* From the CLI sample dtlob.c */
/* ... */
/* cancel the DATA AT EXEC state for hstmt */
sqlrc = SQLCancel(hstmt);
STMT_HANDLE_CHECK( hstmt, sqlrc);
}
References