DB2 10.5 for Linux, UNIX, and Windows

SQLFreeStmt function (CLI) - Free (or reset) a statement handle

Ends processing on the statement referenced by the statement handle.

Specification:

CLI 1.1
ODBC 1.0
ISO CLI

Use this function to:

Close a cursor and discard all pending results
Disassociate (reset) parameters from application variables and LOB file references
Unbind columns from application variables and LOB file references
Drop the statement handle and free the CLI resources associated with the statement handle.

SQLFreeStmt() is called after executing an SQL statement and processing the results.

Syntax

SQLRETURN   SQLFreeStmt (SQLHSTMT          StatementHandle,   /* hstmt */
                         SQLUSMALLINT      Option);           /* fOption */

Function arguments

Table 1. SQLFreeStmt arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle
SQLUSMALLINT	Option	input	Option which specifies the manner of freeing the statement handle. The option must have one of the following values: SQL_CLOSE SQL_DROP SQL_UNBIND SQL_RESET_PARAMS

Usage

SQLFreeStmt() can be called with the following options:

SQL_CLOSE: The cursor (if any) associated with the statement handle (StatementHandle) is closed and all pending results are discarded. The application can reopen the cursor by calling SQLExecute() with the same or different values in the application variables (if any) that are bound to StatementHandle. The cursor name is retained until the statement handle is dropped or a subsequent call to SQLGetCursorName() is successful. If no cursor has been associated with the statement handle, this option has no effect (no warning or error is generated).
SQLCloseCursor() can also be used to close a cursor.
SQL_DROP: CLI resources associated with the input statement handle are freed, and the handle is invalidated. The open cursor, if any, is closed and all pending results are discarded.
This option has been replaced with a call to SQLFreeHandle() with the HandleType set to SQL_HANDLE_STMT. Although this version of CLI continues to support this option, begin using SQLFreeHandle() in your CLI programs so that they conform to the latest standards.
SQL_UNBIND: Sets the SQL_DESC_COUNT field of the ARD (Application Row Descriptor) to 0, releasing all column buffers bound by SQLBindCol() or SQLBindFileToCol() for the given StatementHandle. This does not unbind the bookmark column; to do that, the SQL_DESC_DATA_PTR field of the ARD for the bookmark column is set to NULL. Note that if this operation is performed on an explicitly allocated descriptor that is shared by more than one statement, the operation will affect the bindings of all statements that share the descriptor.
SQL_RESET_PARAMS: Sets the SQL_DESC_COUNT field of the APD (Application Parameter Descriptor) to 0, releasing all parameter buffers set by SQLBindParameter() or SQLBindFileToParam() for the given StatementHandle. Note that if this operation is performed on an explicitly allocated descriptor that is shared by more than one statement, this operation will affect the bindings of all the statements that share the descriptor.

SQLFreeStmt() has no effect on LOB locators, call SQLExecDirect() with the FREE LOCATOR statement to free a locator.

It is possible to reuse a statement handle to execute a different statement:

If the handle was associated with a query, catalog function or SQLGetTypeInfo(), you must close the cursor.
If the handle was bound with a different number or type of parameters, the parameters must be reset.
If the handle was bound with a different number or type of column bindings, the columns must be unbound.

Alternatively you may drop the statement handle and allocate a new one.

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

SQL_SUCCESS_WITH_INFO is not returned if Option is set to SQL_DROP, as there would be no statement handle to use when SQLGetDiagRec() or SQLGetDiagField() is called.

Diagnostics

Table 2. SQLFreeStmt SQLSTATEs
SQLSTATE	Description	Explanation
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
58004	Unexpected system failure.	Unrecoverable system error.
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.
HY010	Function sequence error.	The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
HY092	Option type out of range.	The value specified for the argument Option was not SQL_CLOSE, SQL_DROP, SQL_UNBIND, or SQL_RESET_PARAMS.
HY506	Error closing a file.	Error encountered while trying to close a temporary file.

Authorization

None.

Example

  /* free the statement handle */
  cliRC = SQLFreeStmt(hstmt, SQL_UNBIND);
  rc = HandleInfoPrint(SQL_HANDLE_STMT, hstmt, cliRC, __LINE__, __FILE__);
  if (rc != 0)
  {
    return 1;
  }

  /* free the statement handle */
  cliRC = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
  rc = HandleInfoPrint(SQL_HANDLE_STMT, hstmt, cliRC, __LINE__, __FILE__);
  if (rc != 0)
  {
    return 1;
  }

  /* free the statement handle */
  cliRC = SQLFreeStmt(hstmt, SQL_CLOSE);
  rc = HandleInfoPrint(SQL_HANDLE_STMT, hstmt, cliRC, __LINE__, __FILE__);
  if (rc != 0)
  {
    return 1;
  }