SQLSetStmtAttr - Set a statement attribute

SQLSetStmtAttr - Set a statement attribute

SQLSetStmtAttr() sets an attribute of a specific statement handle. To set an option for all statement handles associated with a connection handle, the application can call SQLSetConnectOption().

Syntax
SQLRETURN SQLSetStmtAttr  (SQLHSTMT       hstmt,
                           SQLINTEGER     fAttr,
                           SQLPOINTER     vParam,
                           SQLINTEGER     sLen);
Function arguments

Table 1. SQLSetStmtAttr arguments
Data type Argument Use Description

SQLHSTMT hstmt Input Statement handle.
SQLINTEGER fAttr Input Attribute to set. Refer to Table 2 for the list of settable statement attributes.
SQLPOINTER vParam Input Value associated with fAttr. vParam can be a 32-bit integer value or a character string.
SQLINTEGER sLen Input Length of data if data is a character string; otherwise, unused.

Usage

Statement options for an hstmt remain in effect until they are changed by another call to SQLSetStmtAttr() or the hstmt is dropped by calling SQLFreeStmt() with the SQL_DROP option. Calling SQLFreeStmt() with the SQL_CLOSE, SQL_UNBIND, or SQL_RESET_PARAMS options does not reset the statement options. The format of information set through vParam depends on the specified fOption. The format of each is noted in Table 2.
Table 2. Statement attributes
fAttr Contents

SQL_ATTR_APP_PARAM_DESC VParam must be a descriptor handle. The specified descriptor serves as the application parameter descriptor for later calls to SQLExecute() and SQLExecDirect() on the statement handle.
SQL_ATTR_APP_ROW_DESC VParam must be a descriptor handle. The specified descriptor serves as the application row descriptor for later calls to SQLFetch() on the statement handle.
SQL_ATTR_BIND_TYPE This specifies whether row-wise or column-wise binding is used.

SQL_BIND_BY_ROW – Binding is row-wise. This is the default.
When using row-wise binding for a multiple row fetch, all of the data for a row is returned in contiguous storage, followed by the data for the next row, and so on.

SQL_BIND_BY_COLUMN – Binding is column-wise.
When using column-wise binding for a multiple row fetch, all of the data for each column is returned in contiguous storage. The storage for each column need not be contiguous. A different address is provided by the user for each column in the result set, and it is the responsibility of the user to ensure that each address has space for all the data to be retrieved.

SQL_ATTR_CURSOR_HOLD A 32-bit integer value that specifies if cursors opened for this statement handle should be held.

SQL_FALSE – An open cursor for this statement handle is closed on a commit or rollback operation. This is the default.

SQL_TRUE – An open cursor for this statement handle is not closed on a commit or rollback operation.

SQL_ATTR_CURSOR_SCROLLABLE A 32-bit integer value that specifies if cursors opened for this statement handle should be scrollable.

SQL_FALSE – Cursors are not scrollable, and SQLFetchScroll() cannot be used against them. This is the default.

SQL_TRUE – Cursors are scrollable. SQLFetchScroll() can be used to retrieve data from these cursors.

SQL_ATTR_CURSOR_SENSITIVITY A 32-bit integer value that specifies whether cursors opened for this statement handle make visible the changes made to the result set by another cursor. See DECLARE CURSOR for a more precise definition of the following options:

SQL_UNSPECIFIED – Cursors on the statement handle might make visible none, some, or all such changes depending on the cursor type. This is the default.

SQL_INSENSITIVE – All valid cursors on the statement handle show the result set without reflecting any changes made to it by any other cursor.

SQL_SENSITIVE – All valid cursors on the statement handle make visible all changes made to a result by another cursor.

SQL_ATTR_CURSOR_TYPE A 32-bit integer value that specifies the behavior of cursors opened for this statement handle.

SQL_CURSOR_FORWARD_ONLY – Cursors are not scrollable, and the SQLFetchScroll() function cannot be used against them. This is the default.

SQL_CURSOR_DYNAMIC – Cursors are scrollable except for insensitive cursor sensitivity. The SQLFetchScroll() function can be used to retrieve data from these cursors.

SQL_CURSOR_STATIC – Cursors are scrollable except for sensitive cursor sensitivity. The SQLFetchScroll() function can be used to retrieve data from these cursors.

SQL_ATTR_EXTENDED_COL_INFO A 32-bit integer value that specifies if cursors opened for this statement handle should provide extended column information.

SQL_FALSE – This statement handle cannot be used on the SQLColAttributes() function to retrieve extended column information. This is the default. Setting this attribute at the statement level overrides the connection level setting of the attribute.

SQL_TRUE – This statement handle can be used on the SQLColAttributes() function to retrieve extended column information, such as base table, base schema, base column, and label.

SQL_ATTR_FOR_FETCH_ONLY A 32-bit integer value that specifies whether cursors opened for this statement handle should be read only:

SQL_TRUE – Cursors are read-only and cannot be used for positioned update or delete operations. This is the default unless SQL_ATTR_FOR_FETCH_ONLY environment has been set to SQL_FALSE.

SQL_FALSE – Cursors can be used for positioned update or delete operations.

SQL_ATTR_FULL_OPEN A 32-bit integer value that specifies if cursors opened for this statement handle should be full open operations.

SQL_FALSE – Opening a cursor for this statement handle might use a cached cursor for performance reasons. This is the default.

SQL_TRUE – Opening a cursor for this statement handle always forces a full open operation of a new cursor.

SQL_ATTR_ROW_STATUS_PTR An output smallint pointer to specify an array of status values at SQLFetchScroll(). The number of elements must equal the number of rows in the row set (as defined by the SQL_ROWSET_SIZE attribute). A status value SQL_ROW_SUCCESS for each row fetched is returned.
If the number of rows fetched is less than the number of elements in the status array (that is, less than the row set size), the remaining status elements are set to SQL_ROW_NOROW. The number of rows fetched is returned in the output pointer. This can be set by the SQLSetStmtAttr attribute SQL_ATTR_ROWS_FETCHED_PTR. DB2^® UDB CLI cannot detect whether a row has been updated or deleted since the start of the fetch. Therefore, the following ODBC defined status values are not reported:

SQL_ROW_DELETED.

SQL_ROW_UPDATED.

SQL_ATTR_ROWS_FETCHED_PTR An output integer pointer that contains the number of rows actually fetched by SQLFetchScroll(). If an error occurs during processing, the pointer points to the ordinal position of the row (in the row set) that precedes the row where the error occurred. If an error occurs retrieving the first row, the pointer points to the value 0.
SQL_ATTR_ROWSET_SIZE A 32-bit integer value that specifies the number of rows in the row set. This is the number of rows returned by each call to SQLExtendedFetch(). The default value is 1.

Return codes

SQL_SUCCESS

SQL_ERROR

SQL_INVALID_HANDLE

Diagnostics

Table 3. SQLStmtAttr SQLSTATEs
SQLSTATE Description Explanation

40003 * Statement completion unknown The communication link between the CLI and the data source fails before the function completes processing.
HY000 General error An error occurred for which there is no specific SQLSTATE and for which no implementation defined SQLSTATE is defined. The error message returned by SQLError in the argument szErrorMsg describes the error and its cause.
HY001 Memory allocation failure The driver is unable to allocate memory required to support the processing or completion of the function.
HY009 Argument value that is not valid Given the specified fAttr value, a value that is not valid is specified for the argument vParam.
An fAttr value that is not valid is specified.
The argument vParam is a null pointer.
HY010 Function sequence error The function is called out of sequence.
HYC00 Driver not capable The driver or the data sources does not support the specified option.

References

SQLFetchScroll - Fetch from a scrollable cursor

SQLSetStmtOption - Set statement option

Parent topic:
DB2 UDB CLI functions

Data type	Argument	Use	Description
SQLHSTMT	hstmt	Input	Statement handle.
SQLINTEGER	fAttr	Input	Attribute to set. Refer to Table 2 for the list of settable statement attributes.
SQLPOINTER	vParam	Input	Value associated with fAttr. vParam can be a 32-bit integer value or a character string.
SQLINTEGER	sLen	Input	Length of data if data is a character string; otherwise, unused.

fAttr	Contents
SQL_ATTR_APP_PARAM_DESC	VParam must be a descriptor handle. The specified descriptor serves as the application parameter descriptor for later calls to `SQLExecute()` and `SQLExecDirect()` on the statement handle.
SQL_ATTR_APP_ROW_DESC	VParam must be a descriptor handle. The specified descriptor serves as the application row descriptor for later calls to `SQLFetch()` on the statement handle.
SQL_ATTR_BIND_TYPE	This specifies whether row-wise or column-wise binding is used. SQL_BIND_BY_ROW – Binding is row-wise. This is the default. When using row-wise binding for a multiple row fetch, all of the data for a row is returned in contiguous storage, followed by the data for the next row, and so on. SQL_BIND_BY_COLUMN – Binding is column-wise. When using column-wise binding for a multiple row fetch, all of the data for each column is returned in contiguous storage. The storage for each column need not be contiguous. A different address is provided by the user for each column in the result set, and it is the responsibility of the user to ensure that each address has space for all the data to be retrieved.
SQL_ATTR_CURSOR_HOLD	A 32-bit integer value that specifies if cursors opened for this statement handle should be held. SQL_FALSE – An open cursor for this statement handle is closed on a commit or rollback operation. This is the default. SQL_TRUE – An open cursor for this statement handle is not closed on a commit or rollback operation.
SQL_ATTR_CURSOR_SCROLLABLE	A 32-bit integer value that specifies if cursors opened for this statement handle should be scrollable. SQL_FALSE – Cursors are not scrollable, and `SQLFetchScroll()` cannot be used against them. This is the default. SQL_TRUE – Cursors are scrollable. `SQLFetchScroll()` can be used to retrieve data from these cursors.
SQL_ATTR_CURSOR_SENSITIVITY	A 32-bit integer value that specifies whether cursors opened for this statement handle make visible the changes made to the result set by another cursor. See DECLARE CURSOR for a more precise definition of the following options: SQL_UNSPECIFIED – Cursors on the statement handle might make visible none, some, or all such changes depending on the cursor type. This is the default. SQL_INSENSITIVE – All valid cursors on the statement handle show the result set without reflecting any changes made to it by any other cursor. SQL_SENSITIVE – All valid cursors on the statement handle make visible all changes made to a result by another cursor.
SQL_ATTR_CURSOR_TYPE	A 32-bit integer value that specifies the behavior of cursors opened for this statement handle. SQL_CURSOR_FORWARD_ONLY – Cursors are not scrollable, and the `SQLFetchScroll()` function cannot be used against them. This is the default. SQL_CURSOR_DYNAMIC – Cursors are scrollable except for insensitive cursor sensitivity. The `SQLFetchScroll()` function can be used to retrieve data from these cursors. SQL_CURSOR_STATIC – Cursors are scrollable except for sensitive cursor sensitivity. The `SQLFetchScroll()` function can be used to retrieve data from these cursors.
SQL_ATTR_EXTENDED_COL_INFO	A 32-bit integer value that specifies if cursors opened for this statement handle should provide extended column information. SQL_FALSE – This statement handle cannot be used on the `SQLColAttributes()` function to retrieve extended column information. This is the default. Setting this attribute at the statement level overrides the connection level setting of the attribute. SQL_TRUE – This statement handle can be used on the `SQLColAttributes()` function to retrieve extended column information, such as base table, base schema, base column, and label.
SQL_ATTR_FOR_FETCH_ONLY	A 32-bit integer value that specifies whether cursors opened for this statement handle should be read only: SQL_TRUE – Cursors are read-only and cannot be used for positioned update or delete operations. This is the default unless SQL_ATTR_FOR_FETCH_ONLY environment has been set to SQL_FALSE. SQL_FALSE – Cursors can be used for positioned update or delete operations.
SQL_ATTR_FULL_OPEN	A 32-bit integer value that specifies if cursors opened for this statement handle should be full open operations. SQL_FALSE – Opening a cursor for this statement handle might use a cached cursor for performance reasons. This is the default. SQL_TRUE – Opening a cursor for this statement handle always forces a full open operation of a new cursor.
SQL_ATTR_ROW_STATUS_PTR	An output smallint pointer to specify an array of status values at SQLFetchScroll(). The number of elements must equal the number of rows in the row set (as defined by the SQL_ROWSET_SIZE attribute). A status value SQL_ROW_SUCCESS for each row fetched is returned. If the number of rows fetched is less than the number of elements in the status array (that is, less than the row set size), the remaining status elements are set to SQL_ROW_NOROW. The number of rows fetched is returned in the output pointer. This can be set by the SQLSetStmtAttr attribute SQL_ATTR_ROWS_FETCHED_PTR. DB2^® UDB CLI cannot detect whether a row has been updated or deleted since the start of the fetch. Therefore, the following ODBC defined status values are not reported: SQL_ROW_DELETED. SQL_ROW_UPDATED.
SQL_ATTR_ROWS_FETCHED_PTR	An output integer pointer that contains the number of rows actually fetched by `SQLFetchScroll()`. If an error occurs during processing, the pointer points to the ordinal position of the row (in the row set) that precedes the row where the error occurred. If an error occurs retrieving the first row, the pointer points to the value 0.
SQL_ATTR_ROWSET_SIZE	A 32-bit integer value that specifies the number of rows in the row set. This is the number of rows returned by each call to `SQLExtendedFetch()`. The default value is 1.

SQLSTATE	Description	Explanation
40003 *	Statement completion unknown	The communication link between the CLI and the data source fails before the function completes processing.
HY000	General error	An error occurred for which there is no specific SQLSTATE and for which no implementation defined SQLSTATE is defined. The error message returned by SQLError in the argument szErrorMsg describes the error and its cause.
HY001	Memory allocation failure	The driver is unable to allocate memory required to support the processing or completion of the function.
HY009	Argument value that is not valid	Given the specified fAttr value, a value that is not valid is specified for the argument vParam. An fAttr value that is not valid is specified. The argument vParam is a null pointer.
HY010	Function sequence error	The function is called out of sequence.
HYC00	Driver not capable	The driver or the data sources does not support the specified option.