SQLCancel function (CLI) - Cancel statement
Facilitates premature termination of the data-at-execution sequence for sending and retrieving long data in pieces. It can also be used to cancel a function called in a different thread.
Specification:
- CLI 1.1
- ODBC 1.0
- ISO CLI
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 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
- SQL_SUCCESS
- SQL_SUCCESS_WITH_INFO
- SQL_INVALID_HANDLE
- SQL_ERROR
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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations. |
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 CLI when inserting LOB data in pieces using SQLParamData()/SQLPutData(). |
Restrictions
None.
Example
/* cancel the SQL_DATA_AT_EXEC state for hstmt */
cliRC = SQLCancel(hstmt);