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;
  }