DB2 Version 10.1 for Linux, UNIX, and Windows

SQLSetStmtAttr function (CLI) - Set options related to a statement

Sets options related to a statement. To set an option for all statements associated with a specific connection, an application can call SQLSetConnectAttr().

Specification:

CLI 5.0
ODBC 3.0
ISO CLI

Refer to the CLI statement attributes list for all available statement attributes.

Unicode equivalent: This function can also be used with the Unicode character set. The corresponding Unicode function is SQLSetStmtAttrW(). See Unicode functions (CLI) for information about ANSI to Unicode function mappings.

Syntax

SQLRETURN   SQLSetStmtAttr (
               SQLHSTMT          StatementHandle,  /* hstmt */
               SQLINTEGER        Attribute,        /* fOption */
               SQLPOINTER        ValuePtr,         /* pvParam */
               SQLINTEGER        StringLength);    /* fStrLen */

Function arguments

Table 1. SQLSetStmtAttr arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLINTEGER	Attribute	input	Option to set, described in the CLI statement attributes list.
SQLPOINTER	ValuePtr	input	Pointer to the value to be associated with Attribute. If Attribute is an ODBC-defined attribute, the application might need to qualify the attribute value in ValuePtr by setting the StringLength attribute as described in the StringLength description. If Attribute is a CLI attribute, the application should always qualify the attribute value in ValuePtr by setting the StringLength attribute as described in the StringLength description. Note: If Attribute is an ODBC attribute, ValuePtr can, depending on the attribute, be set to an unsigned integer. If Attribute is a CLI attribute, ValuePtr can, depending on the attribute, be set to a signed integer. If ValuePtr is set to a signed negative integer and an unsigned integer is expected, ValuePtr might be treated as a large unsigned integer by CLI without warning. Alternatively, CLI might return an error (SQLSTATE HY024).
SQLINTEGER	StringLength	input	If Attribute is an ODBC attribute, the application might need to qualify the attribute by setting StringLength to the following values: If ValuePtr points to a character string or a binary buffer, StringLength should be the length of *ValuePtr. For character string data, StringLength should contain the number of bytes in the string. If ValuePtr is a pointer, but not to a string or binary buffer, then StringLength should have the value SQL_IS_POINTER. If ValuePtr points to an unsigned integer, the StringLength attribute is ignored. If Attribute is a CLI attribute, the application must qualify the attribute by setting StringLength to the following values: If ValuePtr is a pointer to a character string, then StringLength is the number of bytes needed to store the string or SQL_NTS. If ValuePtr is a pointer to a binary buffer, then the application should place the result of the SQL_LEN_BINARY_ATTR (length) macro in StringLength. This places a negative value in StringLength. If ValuePtr contains a fixed-length value, then StringLength is either SQL_IS_INTEGER or SQL_IS_UINTEGER, as appropriate. If ValuePtr is a pointer to a value other than a character string, a binary string, or a fixed-length value, then StringLength should have the value SQL_IS_POINTER.

Usage

Statement attributes for a statement remain in effect until they are changed by another call to SQLSetStmtAttr() or the statement is dropped by calling SQLFreeHandle(). Calling SQLFreeStmt() with the SQL_CLOSE, SQL_UNBIND, or SQL_RESET_PARAMS options does not reset statement attributes.

Some statement attributes support substitution of a similar value if the data source does not support the value specified in *ValuePtr. In such cases, CLI returns SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value changed). For example, CLI supports a pure keyset cursor. As a result, CLI does not allow applications to change the default value of the SQL_ATTR_KEYSET_SIZE attribute. Instead, CLI substitutes SQL_KEYSET_SIZE_DEFAULT for all other values that might be supplied in the *ValuePtr argument and returns SQL_SUCCESS_WITH_INFO. To determine the substituted value, an application calls SQLGetStmtAttr().

The format of information set with ValuePtr depends on the specified Attribute. SQLSetStmtAttr() accepts attribute information in one of two different formats: a null-terminated character string or a 32-bit integer value. The format of information returned in SQLGetStmtAttr() reflects what was specified in SQLSetStmtAttr(). For example, character strings pointed to by the ValuePtr argument of SQLSetStmtAttr() have a length of StringLength, and this is the value that would be returned by SQLGetStmtAttr().

Setting statement attributes by setting descriptors

Many statement attributes also corresponding to a header field of one or more descriptors. These attributes can be set not only by a call to SQLSetStmtAttr(), but also by a call to SQLSetDescField(). Setting these options by a call to SQLSetStmtAttr(), rather than SQLSetDescField(), has the advantage that a descriptor handle does not have to be fetched.

Note: Calling SQLSetStmtAttr() for one statement can affect other statements. This occurs when the application parameter descriptor (APD) or application row descriptor (ARD) associated with the statement is explicitly allocated and is also associated with other statements. Because SQLSetStmtAttr() modifies the APD or ARD, the modifications apply to all statements with which this descriptor is associated. If this is not the required behavior, the application should dissociate this descriptor from the other statement (by calling SQLSetStmtAttr() to set the SQL_ATTR_APP_ROW_DESC or SQL_ATTR_APP_PARAM_DESC field to a different descriptor handle) before calling SQLSetStmtAttr() again.

When a statement attribute that is also a descriptor field is set by a call to SQLSetStmtAttr(), the corresponding field in the descriptor that is associated with the statement is also set. The field is set only for the applicable descriptors that are currently associated with the statement identified by the StatementHandle argument, and the attribute setting does not affect any descriptors that might be associated with that statement in the future. When a descriptor field that is also a statement attribute is set by a call to SQLSetDescField(), the corresponding statement attribute is also set.

Statement attributes determine which descriptors a statement handle is associated with. When a statement is allocated (see SQLAllocHandle()), four descriptor handles are automatically allocated and associated with the statement. Explicitly allocated descriptor handles can be associated with the statement by calling SQLAllocHandle() with a HandleType of SQL_HANDLE_DESC to allocate a descriptor handle, then calling SQLSetStmtAttr() to associate the descriptor handle with the statement.

The following statement attributes correspond to descriptor header fields:

Table 2. Statement attributes
Statement attribute	Header field	Descriptor
SQL_ATTR_PARAM_BIND_OFFSET_PTR	SQL_DESC_BIND_OFFSET_PTR	APD
SQL_ATTR_PARAM_BIND_TYPE	SQL_DESC_BIND_TYPE	APD
SQL_ATTR_PARAM_OPERATION_PTR	SQL_DESC_ARRAY_STATUS_PTR	APD
SQL_ATTR_PARAM_STATUS_PTR	SQL_DESC_ARRAY_STATUS_PTR	IPD
SQL_ATTR_PARAMS_PROCESSED_PTR	SQL_DESC_ROWS_PROCESSED_PTR	IPD
SQL_ATTR_PARAMSET_SIZE	SQL_DESC_ARRAY_SIZE	APD
SQL_ATTR_ROW_ARRAY_SIZE	SQL_DESC_ARRAY_SIZE	APD
SQL_ATTR_ROW_BIND_OFFSET_PTR	SQL_DESC_BIND_OFFSET_PTR	ARD
SQL_ATTR_ROW_BIND_TYPE	SQL_DESC_BIND_TYPE	ARD
SQL_ATTR_ROW_OPERATION_PTR	SQL_DESC_ARRAY_STATUS_PTR	APD
SQL_ATTR_ROW_STATUS_PTR	SQL_DESC_ARRAY_STATUS_PTR	IRD
SQL_ATTR_ROWS_FETCHED_PTR	SQL_DESC_ROWS_PROCESSED_PTR	IRD

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 3. SQLSetStmtAttr SQLSTATEs
SQLSTATE	Description	Explanation
01000	Warning.	Informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
01S02	Option value changed.	CLI did not support the value specified in ValuePtr, or the value specified in ValuePtr was invalid because of SQL constraints or requirements, so CLI substituted a similar value. (Function returns SQL_SUCCESS_WITH_INFO.)
08S01	Communication link failure.	The communication link between CLI and the data source to which it was connected failed before the function completed processing.
24000	Invalid cursor state.	The Attribute was SQL_ATTR_CONCURRENCY, SQL_ATTR_CURSOR_TYPE, SQL_ATTR_SIMULATE_CURSOR, or SQL_ATTR_USE_BOOKMARKS and the cursor was open.
HY000	General error.	An error occurred for which there was no specific SQLSTATE. The error message returned by `SQLGetDiagRec()` in the *MessageText buffer describes the error and its cause.
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.
HY009	Invalid argument value.	A null pointer was passed for ValuePtr and the value in *ValuePtr was a string attribute.
HY010	Function sequence error.	An asynchronously executing function was called for the StatementHandle and was still executing when this function was called. SQLExecute() or SQLExecDirect() was called for the StatementHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns.
HY011	Operation invalid at this time.	The Attribute was SQL_ATTR_CONCURRENCY, SQL_ ATTR_CURSOR_TYPE, SQL_ATTR_SIMULATE_CURSOR, or SQL_ATTR_USE_BOOKMARKS and the statement was prepared.
HY017	Invalid use of an automatically allocated descriptor handle.	The Attribute argument was SQL_ATTR_IMP_ROW_DESC or SQL_ATTR_IMP_PARAM_DESC. The Attribute argument was SQL_ATTR_APP_ROW_DESC or SQL_ATTR_APP_PARAM_DESC, and the value in *ValuePtr was an implicitly allocated descriptor handle.
HY024	Invalid attribute value.	Given the specified Attribute value, an invalid value was specified in *ValuePtr. (CLI returns this SQLSTATE only for connection and statement attributes that accept a discrete set of values, such as SQL_ATTR_ACCESS_MODE.)
HY090	Invalid string or buffer length.	The StringLength argument was less than 0, but was not SQL_NTS.
HY092	Option type out of range.	The value specified for the argument Attribute was not valid for this version of CLI.
HYC00	Driver not capable.	The value specified for the argument Attribute was a valid connection or statement attribute for the version of the CLI driver, but was not supported by the data source.

Restrictions

None.

Example

  /* set the required statement attributes */
  cliRC = SQLSetStmtAttr(hstmt,
                         SQL_ATTR_ROW_ARRAY_SIZE,
                         (SQLPOINTER)ROWSET_SIZE,
                         0);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  /* set the required statement attributes */
  cliRC = SQLSetStmtAttr(hstmt,
                         SQL_ATTR_ROW_BIND_TYPE,
                         SQL_BIND_BY_COLUMN,
                         0);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  /* set the required statement attributes */
  cliRC = SQLSetStmtAttr(hstmt,
                         SQL_ATTR_ROWS_FETCHED_PTR,
                         &rowsFetchedNb,
                         0);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);