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

SQLFreeStmt() ends processing for a statement, to which a statement handle refers. You can use it to close a cursor or drop the statement handle to free the Db2 ODBC resources that are associated with the statement handle. Call SQLFreeStmt() after you execute an SQL statement and process the results.

ODBC specifications for SQLFreeStmt()

Table 1. SQLFreeStmt() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
1.0 Yes Yes

Syntax

SQLRETURN   SQLFreeStmt      (SQLHSTMT          hstmt,
                              SQLUSMALLINT      fOption);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. SQLFreeStmt() arguments
Data type Argument Use Description
SQLHSTMT hstmt input Specifies the statement handle that refers to the statement to be stopped.
SQLUSMALLINT fOption input The following values specify the manner in which you free the statement handle:
  • SQL_UNBIND
  • SQL_RESET_PARAMS
  • SQL_CLOSE
  • SQL_DROP (Deprecated)

See Usage for details about these values.

Usage

When you call SQLFreeStmt(), you set the fOption argument to one of the following options. SQLFreeStmt() performs different actions based upon which one of these options you specify.
SQL_UNBIND
All the columns that are bound by previous SQLBindCol() calls on this statement handle are released (the association between application variables or file references and result set columns is broken).
SQL_RESET_PARAMS
All the parameters that are set by previous SQLBindParameter() calls on this statement handle are released. (The association between application variables, or file references, and parameter markers in the SQL statement for the statement handle is broken.)
SQL_CLOSE
The cursor (if any) that is associated with the statement handle is closed and all pending results are discarded. You can reopen the cursor by calling SQLExecute() or SQLExecDirect() with the same or different values in the application variables (if any) that are bound to the statement handle. The cursor name is retained until the statement handle is dropped or the next successful SQLSetCursorName() call. If a cursor is not associated with the statement handle, this option has no effect. (In the case where no cursors exist, a warning or an error is not generated.)

You can also call the ODBC 3.0 API SQLCloseCursor() to close the cursor.

SQL_DROP (Deprecated)
In ODBC 3.0, SQLFreeHandle() with HandleType set to SQL_HANDLE_STMT replaces the SQL_DROP option of SQLFreeStmt().

Although Db2 ODBC supports the SQL_DROP option for backward compatibility, you should use current ODBC 3.0 functions in your applications.

SQLFreeStmt() does not affect LOB locators. To free a locator, call SQLExecDirect() with the FREE LOCATOR statement.

After you execute a statement on a statement handle, you can reuse that handle to execute a different statement. The following situations require you to take additional action before you reuse a statement handle:
  • When the statement handle that you want to reuse is associated with a catalog function or SQLGetTypeInfo(), you must close the cursor on that handle.
  • When you want to reuse a statement handle for a different number or different types of parameters than you originally bound, you must reset the parameters on that handle.
  • When you want to reuse a statement handle for a different number or different types of columns than you originally bound, you must unbind the original columns.
Alternatively, you can drop the statement handle and allocate a new one.

Return codes

After you call SQLFreeStmt(), it returns one of the following values:
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE

SQL_SUCCESS_WITH_INFO is not returned if fOption is set to SQL_DROP, because no statement handle is available to use when SQLGetDiagRec() is called.

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 3. SQLFreeStmt() SQLSTATEs
SQLSTATE Description Explanation
08S01 Communication link failure. The communication link between the application and data source fails before the function completes.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY010 Function sequence error. The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the SQLParamData() or SQLPutData() functions.)
HY092 Option type out of range. The specified value for the fOption argument is not one of the following values:
  • SQL_CLOSE
  • SQL_DROP
  • SQL_UNBIND
  • SQL_RESET_PARAMS

Example

Refer to SQLFetch() for a related example.