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().

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLSetStmtAttrW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI. End of change

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 row 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 `SQLColAttribute()` 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 `SQLColAttribute()` 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_NUMBER_RESULTSET_ROWS_PTR	A 32-bit integer * value the points to a buffer which contains the total number of rows available from the result set. This attribute will only return a valid result if the cursor sensitivity is insensitive and the cursor type is static. Without these settings, the returned result will be zero. This value is set after a successful call to SQLExecute() or SQLExecDirect().
SQL_ATTR_PARAM_BIND_TYPE	A 32-bit integer value: 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_PARAM_STATUS_PTR	A 32-bit integer * value that points to an array of values containing status information for each row of parameter values. The status values are set after a call to `SQLExecDirect()` or `SQLExecute()`. This field is used only if SQL_ATTR_PARAMSET_SIZE is greater than 1. The following status values can be returned. SQL_PARAM_SUCCESS: The SQL statement was successfully executed for this set of parameters. SQL_PARAM_SUCCESS_WITH_INFO: The SQL statement was successfully executed for this set of parameters; however, warning information was returned. SQL_PARAM_ERROR: There was an error in processing this set of parameters. SQL_PARAM_UNUSED: The parameter that was set is unused. This can occur if a previously set parameter caused an error which aborted further processing. SQL_PARAM_DIAG_UNAVAILABLE: This is not currently set by DB2 CLI. This statement attribute can be set to a null pointer, in which case DB2 CLI does not return parameter status values.
SQL_ATTR_PARAMS_PROCESSED_PTR	A 32-bit integer * value that points to the current row number. As each row of parameters is processed this is set to the number of that row. If the call to SQLExecDirect() or SQLExecute() that fills in the SQLINTEGER buffer pointed to by this attribute does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the contents of the buffer are undefined. This statement attribute can be set to a null pointer, in which case DB2 CLI does not return the row number.
SQL_ATTR_PARAMSET_SIZE	A 32-bit integer value that specifies the number of values to be associated with each parameter marker. If this is greater that 1, the rgbValue argument in `SQLBindParameter()` points to an array of parameter values, and pcbValue points to an array of lengths. This is an alternative to setting a value size through the `SQLParamOptions()` API.
SQL_ATTR_ROW_BIND_TYPE	A 32-bit integer value: 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 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_ROW_STATUS_PTR	A 16-bit SMALLINT * value that points to 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 for i 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	A 32-bit integer * value that points to a buffer 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