Call Level Interface Guide and Reference

SQLSetStmtAttr - Set Options Related to a Statement

Purpose

Specification: DB2 CLI 5.0 ODBC 3.0 ISO CLI

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

Syntax

SQLRETURN   SQLSetStmtAttr   (SQLHSTMT          StatementHandle,
                              SQLINTEGER        Attribute,
                              SQLPOINTER        ValuePtr,
                              SQLINTEGER        StringLength);

Function Arguments

Table 169. SQLSetStmtAttr Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLINTEGER Attribute input Option to set, listed in Statement Attributes
SQLHSTMT *ValuePtr input If Attribute is an ODBC-defined attribute and ValuePtr points to a character string or a binary buffer, this argument should be the length of *ValuePtr. If Attribute is an ODBC-defined attribute and ValuePtr is an integer, StringLength is ignored.
If Attribute is a DB2 CLI attribute, the application indicates the nature of the attribute by setting the StringLength argument. StringLength can have the following values:

If ValuePtr is a pointer to a character string, then StringLength is the length of the string or SQL_NTS.
If ValuePtr is a pointer to a binary buffer, then the application places the result of the SQL_LEN_BINARY_ATTR(length) macro in StringLength. This places a negative value in StringLength.
If ValuePtr is a pointer to a value other than a character string or a binary string, then StringLength should have the value SQL_IS_POINTER.
If ValuePtr contains a fixed-length value, then StringLength is either SQL_IS_INTEGER or SQL_IS_UINTEGER, as appropriate.

SQLINTEGER StringLength input If ValuePtr points to a character string or a binary buffer, this argument should be the length of *ValuePtr. If ValuePtr is a pointer, but not to a string or binary buffer, then StringLength should have the value SQL_IS_POINTER. If ValuePtr is not a pointer, then StringLength should have the value SQL_IS_NOT_POINTER.

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLINTEGER	`Attribute`	input	Option to set, listed in Statement Attributes
SQLHSTMT	*ValuePtr	input	If `Attribute` is an ODBC-defined attribute and `ValuePtr` points to a character string or a binary buffer, this argument should be the length of `*ValuePtr`. If `Attribute` is an ODBC-defined attribute and `ValuePtr` is an integer, `StringLength` is ignored. If `Attribute` is a DB2 CLI attribute, the application indicates the nature of the attribute by setting the `StringLength` argument. `StringLength` can have the following values: If `ValuePtr` is a pointer to a character string, then `StringLength` is the length of the string or SQL_NTS. If `ValuePtr` is a pointer to a binary buffer, then the application places the result of the SQL_LEN_BINARY_ATTR(length) macro in `StringLength`. This places a negative value in `StringLength`. If `ValuePtr` is a pointer to a value other than a character string or a binary string, then `StringLength` should have the value SQL_IS_POINTER. If `ValuePtr` contains a fixed-length value, then `StringLength` is either SQL_IS_INTEGER or SQL_IS_UINTEGER, as appropriate.
SQLINTEGER	`StringLength`	input	If `ValuePtr` points to a character string or a binary buffer, this argument should be the length of *`ValuePtr`. If `ValuePtr` is a pointer, but not to a string or binary buffer, then `StringLength` should have the value SQL_IS_POINTER. If `ValuePtr` is not a pointer, then `StringLength` should have the value SQL_IS_NOT_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, DB2 CLI returns SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (Option value changed). For example, if Attribute is SQL_ATTR_CONCURRENCY, *ValuePtr is SQL_CONCUR_ROWVER, and the data source does not support this, DB2 CLI substitutes SQL_CONCUR_VALUES 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 each is noted in the attribute's description. This format applies to the information returned for each attribute in SQLGetStmtAttr(). Character strings pointed to by the ValuePtr argument of SQLSetStmtAttr() have a length ofStringLength.

Setting Statement Attributes by Setting Descriptors

Many statement attributes also corresponding to a header field of one or more descriptors. These attributes may 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 APD or 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 desired 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 may 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 an fHandleType 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 170. Statement Attributes

Statement Attribute Header Field Desc.
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

Statement Attribute	Header Field	Desc.
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

Statement Attributes

The currently defined attributes and the version of DB2 CLI in which they were introduced are shown below; it is expected that more will be defined to take advantage of different data sources.
Note: All statement attributes from DB2 CLI version 2 have been renamed. In version 2 they began with SQL_ but now begin with SQL_ATTR_

SQL_ATTR_APP_PARAM_DESC (DB2 CLI v5)

The handle to the APD for subsequent call to SQLExecute() and SQLExecDirect() on the statement handle. The initial value of this attribute is the descriptor implicitly allocated when the statement was initially allocated. If this value of this attribute is set to SQL_NULL_DESC, an explicitly allocated APD handle that was previously associated with the statement handle is dissociated from it, and the statement handle reverts to the implicitly allocated APD handle.

This attribute cannot be set to a descriptor handle that was implicitly allocated for another statement or to another descriptor handle that was implicitly set on the same statement; implicitly allocated descriptor handles cannot be associated with more than one statement or descriptor handle.

This attribute cannot be set at the connection level.

SQL_ATTR_APP_ROW_DESC (DB2 CLI v5)

The handle to the ARD for subsequent fetches on the statement handle. The initial value of this attribute is the descriptor implicitly allocated when the statement was initially allocated. If this value of this attribute is set to SQL_NULL_DESC, an explicitly allocated ARD handle that was previously associated with the statement handle is dissociated from it, and the statement handle reverts to the implicitly allocated ARD handle.

This attribute cannot be set at the connection level.

SQL_ATTR_ASYNC_ENABLE (DB2 CLI v2)

A 32-bit integer value that specifies whether a function called with the specified statement is executed asynchronously:

SQL_ASYNC_ENABLE_OFF = Off (the default)
SQL_ASYNC_ENABLE_ON = On

Once a function has been called asynchronously, only the original function, SQLAllocHandle(), SQLCancel(), SQLSetStmtAttr(), SQLGetDiagField(), SQLGetDiagRec(), or SQLGetFunctions() can be called on the statement or the connection associated with the statement, until the original function returns a code other than SQL_STILL_EXECUTING. Any other function called on the statement or the connection associated with the statement returns SQL_ERROR with an SQLSTATE of HY010 (Function sequence error). Functions can be called on other statements.

Because DB2 CLI supports statement level asynchronous-execution, the statement attribute SQL_ATTR_ASYNC_ENABLE may be set. Its initial value is the same as the value of the connection level attribute with the same name at the time the statement handle was allocated.

In general, applications should execute functions asynchronously only on single-threaded operating systems. On multi-threaded operating systems, applications should execute functions on separate threads, rather than executing them asynchronously on the same thread. DB2 CLI applications that only operate on multi-threaded operating systems do not need to support asynchronous execution. See Writing Multi-Threaded Applications and Asynchronous Execution of CLI for more information.

The following functions can be executed asynchronously:

 
    SQLColAttribute()        SQLGetTypeInfo()
    SQLColumnPrivileges()    SQLMoreResults()
    SQLColumns()             SQLNumParams()
    SQLCopyDesc()            SQLNumResultCols()
    SQLDescribeCol()         SQLParamData()
    SQLDescribeParam()       SQLPrepare()
    SQLExecDirect()          SQLPrimaryKeys()
    SQLExecute()             SQLProcedureColumns()
    SQLFetch()               SQLProcedures()
    SQLFetchScroll()         SQLPutData()
    SQLForeignKeys()         SQLSetPos()
    SQLGetData()             SQLSpecialColumns()
    SQLGetDescField() 1*     SQLStatistics()
    SQLGetDescRec() 1*       SQLTablePrivileges()
    SQLGetDiagField()        SQLTables()
    SQLGetDiagRec()
 
1* These functions can be called asynchronously only if the descriptor
   is an implementation descriptor, not an application descriptor.

Asynchronous executing can also be set using the ASYNCENABLE DB2 CLI/ODBC configuration keyword. See Configuring db2cli.ini for more information.

SQL_ATTR_BIND_TYPE (DB2 CLI v2)

A 32-bit integer value that sets the binding orientation to be used when SQLExtendedFetch() is called with this statement handle. Column-wise binding is selected by supplying the value SQL_BIND_BY_COLUMN for the argument vParam. Row-wise binding is selected by supplying a value for vParam specifying the length of the structure or an instance of a buffer into which result columns will be bound.

For row-wise binding, the length specified in vParam must include space for all of the bound columns and any padding of the structure or buffer to ensure that when the address of a bound column is incremented with the specified length, the result will point to the beginning of the same column in the next row. (When using the sizeof operator with structures or unions in ANSI C, this behavior is guaranteed.)

Column-wise binding is the default for this option.

SQL_ATTR_CLOSEOPEN (DB2 CLI v6)

To reduce the time it takes to open and close cursors, DB2 will automatically close an open cursor if a second cursor is opened using the same handle. Network flow is therefore reduced when the close request is chained with the open request and the two statements are combined into one network request (instead of two).

0 = DB2 acts as a regular ODBC data source: Do not chain the close and open statements, return an error if the cursor is open. This is the default.
1 = Chain the close and open statements.

Previous CLI applications will not benefit from this default because they are designed to explicitly close the cursor. New applications, however, can take advantage of this behavior by not closing the cursors explicitly, but by allowing CLI to close the cursor on subsequent open requests.

SQL_ATTR_CONCURRENCY (DB2 CLI v2)

A 32-bit integer value that specifies the cursor concurrency:

SQL_CONCUR_READ_ONLY = Cursor is read-only. No updates are allowed. Supported by forward-only, static and keyset cursors.
SQL_CONCUR_LOCK = Cursor uses the lowest level of locking sufficient to ensure that the row can be updated. Supported by forward-only and keyset cursors.
SQL_CONCUR_VALUES = Cursor uses optimistic concurrency control, comparing values.

The default value for SQL_ATTR_CONCURRENCY is SQL_CONCUR_READ_ONLY for static and forward-only cursors. The default for a keyset cursor is SQL_CONCUR_VALUES.

This attribute can also be set through the Concurrency argument in SQLSetScrollOptions(). This attribute cannot be specified for an open cursor.

If the SQL_ATTR_CURSOR_TYPE Attribute is changed to a type that does not support the current value of SQL_ATTR_CONCURRENCY, the value of SQL_ATTR_CONCURRENCY will be changed at execution time, and a warning issued when SQLExecDirect() or SQLPrepare() is called.

If a SELECT FOR UPDATE statement is executed while the value of SQL_ATTR_CONCURRENCY is set to SQL_CONCUR_READ_ONLY, an error will be returned. If the value of SQL_ATTR_CONCURRENCY is changed to a value that is supported for some value of SQL_ATTR_CURSOR_TYPE, but not for the current value of SQL_ATTR_CURSOR_TYPE, the value of SQL_ATTR_CURSOR_TYPE will be changed at execution time, and SQLSTATE 01S02 (Option value changed) is issued when SQLExecDirect() or SQLPrepare() is called.

If the specified concurrency is not supported by the data source, then DB2 CLI substitutes a different concurrency and returns SQLSTATE 01S02 (Option value changed). The order of substitution depends on the cursor type:

Forward-Only: SQL_CONCUR_LOCK is substituted for SQL_CONCUR_ROWVER and SQL_CONCUR_VALUES
Static: only SQL_CONCUR_READ_ONLY is valid
Keyset: SQL_CONCUR_VALUES is substituted for SQL_CONCUR_ROWVER

Note:

The following value has also been defined by ODBC, but is not supported by DB2 CLI

SQL_CONCUR_ROWVER = Cursor uses optimistic concurrency control.

SQL_ATTR_CURSOR_HOLD (DB2 CLI v2)

A 32-bit integer which specifies whether the cursor associated with this StatementHandle is preserved in the same position as before the COMMIT operation, and whether the application can fetch without executing the statement again.

SQL_CURSOR_HOLD_ON (this is the default)
SQL_CURSOR_HOLD_OFF

The default value when an StatementHandle is first allocated is SQL_CURSOR_HOLD_ON.

This option cannot be specified while there is an open cursor on this StatementHandle.

Cursor hold can also be set using the CURSORHOLD DB2 CLI/ODBC configuration keyword. See Configuring db2cli.ini for more information.
Note: This option is an IBM extension.

SQL_ATTR_CURSOR_SCROLLABLE (DB2 CLI v6)

A 32-bit integer that specifies the level of support that the application requires. Setting this attribute affects subsequent calls to SQLExecDirect() and SQLExecute(). The supported values are:

SQL_NONSCROLLABLE = Scrollable cursors are not required on the statement handle. If the application calls SQLFetchScroll() on this handle, the only valid value of FetchOrientation() is SQL_FETCH_NEXT. This is the default.
SQL_SCROLLABLE = Scrollable cursors are required on the statement handle. When calling SQLFetchScroll(), the application may specify any valid value of FetchOrientation, achieving cursor positioning in modes other than the sequential mode. For more information on scrollable cursors, see Scrollable Cursors.

SQL_ATTR_CURSOR_SENSITIVITY (DB2 CLI v6)

A 32-bit integer that specifies whether cursors on the statement handle make visible the changes made to a result set by another cursor. Setting this attribute affects subsequent calls to SQLExecDirect() and SQLExecute(). The supported values are:

SQL_UNSPECIFIED = It is unspecified what the cursor type is and whether cursors on the statement handle make visible the changes made to a result set by another cursor. Cursors on the statement handle may make visible none, some or all such changes. This is the default.
SQL_INSENSITIVE = All cursors on the statement handle show the result set without reflecting any changes made to it by any other cursor. Insensitive cursors are read-only. This corresponds to a static cursor which has a concurrency that is read-only.
SQL_SENSITIVE = All cursors on the statement handle make visible all changes made to a result by another cursor.

SQL_ATTR_CURSOR_TYPE (DB2 CLI v2)

A 32-bit integer value that specifies the cursor type. The supported values are:

SQL_CURSOR_FORWARD_ONLY = The cursor only scrolls forward.
SQL_CURSOR_STATIC = The data in the result set is static. This is the default.
SQL_CURSOR_KEYSET_DRIVEN = DB2 CLI supports a pure keyset cursor. The SQL_KEYSET_SIZE statement attribute is ignored. To limit the size of the keyset the application must limit the size of the result set by setting the SQL_ATTR_MAX_ROWS attribute to a value other than 0.

This option cannot be specified for an open cursor.

If the specified cursor type is not supported by the data source, CLI substitutes a different cursor type and returns SQLSTATE 01S02 (Option value changed). For a mixed or dynamic cursor, CLI substitutes, in order, a keyset-driven or static cursor.

Note: The following value has also been defined by ODBC, but is not supported by DB2 CLI:

SQL_CURSOR_DYNAMIC

If this values is used, DB2 CLI sets the statement attribute to SQL_CURSOR_STATIC SQL_CURSOR_FORWARD_ONLY and returns SQLSTATE 01S02 (Option value changed). In this case the application should call SQLGetStmtAttr() to query the actual value.

SQL_ATTR_DEFERRED_PREPARE (DB2 CLI v5)

Specifies whether the PREPARE request is deferred until the corresponding execute request is issued.

SQL_DEFERRED_PREPARE_OFF = Disable deferred prepare. The PREPARE request will be executed the moment it is issued.
SQL_DEFERRED_PREPARE_ON (default) = Enable deferred prepare. Defer the execution of the PREPARE request until the corresponding execute request is issued. The two requests are then combined into one command/reply flow (instead of two) to minimize network flow and to improve performance.
If the target DB2 database or the DDCS gateway does not support deferred prepare, the client disables deferred prepare for that connection.

The default behavior has changed from DB2 version 2. Deferred prepare is now the default and must be explicitly turned off if required.
Note: When deferred prepare is enabled, the row and cost estimates normally returned in the SQLERRD(3) and SQLERRD(4) of the SQLCA of a PREPARE statement may become zeros. This may be of concern to users who want to use these values to decide whether or not to continue the SQL statement.

This option is turned off if the CLI/ODBC option DB2ESTIMATE is set to a value other than zero.

Deferred prepare can also be set using the DEFERREDPREPARE DB2 CLI/ODBC configuration keyword. See Configuring db2cli.ini for more information.
Note: This is an IBM defined extension.

SQL_ATTR_EARLYCLOSE (DB2 CLI v5)

Specifies whether or not the temporary cursor on the server can be automatically closed, without closing the cursor on the client, when the last record is sent to the client.

SQL_EARLYCLOSE_OFF = Do not close the temporary cursor on the server early.
SQL_EARLYCLOSE_ON = Close the temporary cursor on the server early (default).
This saves the CLI/ODBC driver a network request by not issuing the statement to explicitly close the cursor because it knows that it has already been closed.
Having this option on will speed up applications that make use of many small result sets.
The EARLYCLOSE feature is not used if either:
- The statement disqualifies for blocking.
- The cursor type is anything other than SQL_CURSOR_FORWARD_ONLY.

The early close feature can also be set using the EARLYCLOSE DB2 CLI/ODBC configuration keyword. See Configuring db2cli.ini for more information.
Note: This is an IBM defined extension.

SQL_ATTR_ENABLE_AUTO_IPD (DB2 CLI v5)

A 32-bit integer value that specifies whether automatic population of the IPD is performed:

SQL_TRUE = Turns on automatic population of the IPD after a call to SQLPrepare().
SQL_FALSE = Turns off automatic population of the IPD after a call to SQLPrepare().

The default value of the statement attribute SQL_ATTR_ENABLE_AUTO_IPD is equal to the value of the connection attribute SQL_ATTR_AUTO_IPD.

If the connection attribute SQL_ATTR_ AUTO_IPD is SQL_FALSE, the statement attribute SQL_ATTR_ENABLE_AUTO_IPD cannot be set to SQL_TRUE.

SQL_ATTR_FETCH_BOOKMARK_PTR (DB2 CLI v5)

A pointer that points to a binary bookmark value. When SQLFetchScroll() is called with fFetchOrientation equal to SQL_FETCH_BOOKMARK, DB2 CLI picks up the bookmark value from this field. This field defaults to a null pointer.

SQL_ATTR_IMP_PARAM_DESC (DB2 CLI v5)

The handle to the IPD. The value of this attribute is the descriptor allocated when the statement was initially allocated. The application cannot set this attribute.

This attribute can be retrieved by a call to SQLGetStmtAttr(), but not set by a call to SQLSetStmtAttr().

SQL_ATTR_IMP_ROW_DESC (DB2 CLI v5)

The handle to the IRD. The value of this attribute is the descriptor allocated when the statement was initially allocated. The application cannot set this attribute.

This attribute can be retrieved by a call to SQLGetStmtAttr(), but not set by a call to SQLSetStmtAttr().

SQL_ATTR_KEYSET_SIZE (DB2 CLI v5)

DB2 CLI supports a pure keyset cursor, therefore the SQL_KEYSET_SIZE statement attribute is ignored. To limit the size of the keyset the application must limit the size of the result set by setting the SQL_ATTR_MAX_ROWS attribute to a value other than 0.

SQL_ATTR_MAX_LENGTH (DB2 CLI v2)

A 32-bit integer value corresponding to the maximum amount of data that can be retrieved from a single character or binary column. If data is truncated because the value specified for SQL_MAX_LENGTH is less than the amount of data available, a SQLGetData() call or fetch will return SQL_SUCCESS instead of returning SQL_SUCCESS_WITH_INFO and SQLSTATE 01004 (Data Truncated). The default value for vParam is 0; 0 means that DB2 CLI will attempt to return all available data for character or binary type data.

SQL_ATTR_MAX_ROWS (DB2 CLI v2)

A 32-bit integer value corresponding to the maximum number of rows to return to the application from a query. The default value for vParam is 0; 0 means all rows are returned.

SQL_ATTR_METADATA_ID (DB2 CLI v5)

A 32-bit integer value that determines how the string arguments of catalog functions are treated.

SQL_TRUE, the string argument of catalog functions are treated as identifiers. The case if not significant. For non-delimited strings, DB2 CLI removes any trailing spaces, and the string is folded to upper case. For delimited strings, DB2 CLI removes any leading or trailing spaces, and takes whatever is between the delimiters literally. If one of these arguments is set to a null pointer, the function returns SQL_ERROR and SQLSTATE HY009 (Invalid use of null pointer).
SQL_FALSE, the string arguments of catalog functions are not treated as identifiers. The case is significant. They can either contain a string search pattern or not, depending on the argument.
This is the default value.

The TableType argument of SQLTables(), which takes a list of values, is not affected by this attribute.

SQL_ATTR_NODESCRIBE (DB2 CLI v2)

This statement attribute is no longer required for DB2 CLI version 5 and later. Now that DB2 CLI uses deferred prepare by default, there is no need for the functionality of SQLSetColAttributes(). See Deferred Prepare now on by Default for more details.

A 32-bit integer which specifies whether DB2 CLI should automatically describe the column attributes of the result set or wait to be informed by the application via SQLSetColAttributes().
Note: This is an IBM defined extension.

SQL_ATTR_NOSCAN (DB2 CLI v2)

A 32-bit integer value that specified whether DB2 CLI will scan SQL strings for escape clauses. The two permitted values are:

SQL_NOSCAN_OFF - SQL strings are scanned for escape clause sequences. This is the default.
SQL_NOSCAN_ON - SQL strings are not scanned for escape clauses. Everything is sent directly to the server for processing.

This application can choose to turn off the scanning if it never uses vendor escape sequences in the SQL strings that it sends. This will eliminate some of the overhead processing associated with scanning.

SQL_ATTR_OPTIMIZE_FOR_NROWS (DB2 CLI v6)

A 32-bit integer value. It it is set to an integer larger than 0, "OPTIMIZE FOR n ROWS" clause will be appended to every select statement If set to 0 (the default) this clause will not be appended.

For more information on the effect of the OPTIMIZE FOR n ROWS clause, refer to the Administration Guide.

This value can also be set using the OPTIMIZEFORNROWS DB2 CLI/ODBC configuration keyword. See Configuring db2cli.ini for more information.

SQL_ATTR_OPTIMIZE_SQLCOLUMNS (DB2 CLI v6)

A 32-bit integer.

If set to 1, then all calls to SQLColumns() will be optimized if an explicit (no wildcard specified) Schema Name, explicit Table Name, and % (ALL columns) for Column Name are specified. The DB2 CLI/ODBC Driver will optimize this call so that the system tables will not be scanned.
If the call is optimized then the COLUMN_DEF information from SQLColumns() (which contains the default string for the columns) is not returned, and the datatype of the AS/400 NUMERIC column will be returned as SQL_DECIMAL.
If set to 0, information will be returned as usual. Use this setting if the application needs the COLUMN_DEF information.

This value can also be set using the OPTIMIZESQLCOLUMNS DB2 CLI/ODBC configuration keyword. See Configuring db2cli.ini for more information.

SQL_ATTR_PARAM_BIND_OFFSET_PTR (DB2 CLI v5)

A 32-bit integer * value that points to an offset added to pointers to change binding of dynamic parameters. If this field is non-null, DB2 CLI dereferences the pointer, adds the dereferenced value to each of the deferred fields in the descriptor record (SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR), and uses the new pointer values when binding. It is set to null by default.

The bind offset is always added directly to the SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR fields. If the offset is changed to a different value, the new value is still added directly to the value in the descriptor field. The new offset is not added to the field value plus any earlier offsets.

Setting this statement attribute sets the SQL_DESC_BIND_OFFSET_PTR field in the APD header.

SQL_ATTR_PARAM_BIND_TYPE (DB2 CLI v5)

A 32-bit integer value that indicates the binding orientation to be used for dynamic parameters.

This field is set to SQL_PARAMETER_BIND_BY_COLUMN (the default) to select column-wise binding.

To select row-wise binding, this field is set to the length of the structure or an instance of a buffer that will be bound to a set of dynamic parameters. This length must include space for all of the bound parameters and any padding of the structure or buffer to ensure that when the address of a bound parameter is incremented with the specified length, the result will point to the beginning of the same parameter in the next set of parameters. When using the sizeof operator in ANSI C, this behavior is guaranteed.

Setting this statement attribute sets the SQL_DESC_ BIND_TYPE field in the APD header.

SQL_ATTR_PARAM_OPERATION_PTR (DB2 CLI v5)

A 16-bit unsigned integer * value that points to an array of 16-bit unsigned integer values used to ignore a parameter during execution of a SQL statement. Each value is set to either SQL_PARAM_PROCEED (for the parameter to be executed) or SQL_PARAM_IGNORE (for the parameter to be ignored).

A set of parameters can be ignored during processing by setting the status value in the array pointed to by SQL_DESC_ARRAY_STATUS_PTR in the APD to SQL_PARAM_IGNORE. A set of parameters is processed if its status value is set to SQL_PARAM_PROCEED, or if no elements in the array are set.

This statement attribute can be set to a null pointer, in which case DB2 CLI does not return parameter status values. This attribute can be set at any time, but the new value is not used until the next time SQLExecDirect() or SQLExecute() is called.

Setting this statement attribute sets the SQL_DESC_ARRAY_STATUS_PTR field in the APD.

SQL_ATTR_PARAM_STATUS_PTR (DB2 CLI v5)

A 16-bit unsigned integer * value that points to an array of UWORD values containing status information for each row of parameter values after a call to SQLExecute() or SQLExecDirect(). This field is required only if PARAMSET_SIZE is greater than 1.

The status values can contain the following values:

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 is available in the diagnostics data structure.
SQL_PARAM_ERROR: There was an error in processing this set of parameters. Additional error information is available in the diagnostics data structure.
SQL_PARAM_UNUSED: This parameter set was unused, possibly due to the fact that some previous parameter set caused an error that aborted further processing.
SQL_PARAM_DIAG_UNAVAILABLE: DB2 CLI treats arrays of parameters as a monolithic unit and so does not generate this level of error information.

Setting this statement attribute sets the SQL_DESC_ARRAY_STATUS_PTR field in the IPD header.

SQL_ATTR_PARAMOPT_ATOMIC (DB2 CLI v2)

This is a 32-bit integer value which determines, when SQLParamOptions() has been used to specify multiple values for parameter markers, whether the underlying processing should be done via ATOMIC or NOT-ATOMIC Compound SQL. The possible values are:

SQL_ATOMIC_YES - The underlying processing makes use of ATOMIC Compound SQL. This is the default.
SQL_ATOMIC_NO - The underlying processing makes use of NON-ATOMIC Compound SQL.

ATOMIC Compound SQL is not possible with: DB2 for common server prior to Version 2.1 or DRDA servers. Specifying SQL_ATOMIC_YES when connected to one of the above servers results in an error (SQLSTATE is S1C00).

SQL_ATTR_PARAMS_PROCESSED_PTR (DB2 CLI v5)

A 32-bit unsigned integer * record field that points to a buffer in which to return the current row number. As each row of parameters is processed, this is set to the number of that row. No row number will be returned if this is a null pointer.

Setting this statement attribute sets the SQL_DESC_ROWS_PROCESSED_PTR field in the IPD header.

SQL_ATTR_PARAMSET_SIZE (DB2 CLI v5)

A 32-bit unsigned integer value that specifies the number of values for each parameter. If SQL_ATTR_PARAMSET_SIZE is greater than 1, SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR of the APD point to arrays. The cardinality of each array is equal to the value of this field.

Setting this statement attribute sets the SQL_DESC_ARRAY_SIZE field in the APD header.

SQL_ATTR_PREFETCH (DB2 CLI v6)

This is a 32-bit value which determines if the server will prefetch the next block of data immediately after sending the current block (if supported by the server). This allows the server to get the next block of data while the application is receiving the current block.

This has no effect if the entire result set fits in the first block of data, or if the cursor is a non-blocking cursor (if it is a FOR UPDATE cursor, or if the result contains lob data, for example).

The possible values are:

SQL_PREFETCH_ON - Prefetch will occur if supported by the server
SQL_PREFETCH_OFF - Prefetch will not occur. This is the default.

SQL_ATTR_QUERY_OPTIMIZATION_LEVEL (DB2 CLI v6)

A 32-bit integer value that sets the query optimization level to be used on the next call to SQLPrepare(), SQLExtendedPrepare(), or SQLExecDirect().

Current values used are: 0,1,2,3,5,7, and 9. For more information about query optimization levels see the SET CURRENT QUERY OPTIMIZATION command in the SQL Reference.

SQL_ATTR_QUERY_TIMEOUT (DB2 CLI v2)

A 32-bit integer value that is the number of seconds to wait for an SQL statement to execute between returning to the application. DB2 CLI only supports the value of 0 except on Windows 3.1; 0 means there is no time out.
Note: On Windows 3.1, this option can be set and used to terminate long running queries. If this is specified, the underlying Windows 3.1 connectivity code will display a dialog box to inform the user that the specified number of seconds have elapsed and prompt the user to continue or interrupt the query.
This option is not valid for platforms other than Windows 3.1, S1C00 is returned.

SQL_ATTR_RETRIEVE_DATA (DB2 CLI v2)

A 32-bit integer value:

SQL_RD_ON = SQLFetchScroll() and in DB2 CLI Version 5.2 and later, SQLFetch(), retrieve data after it positions the cursor to the specified location. This is the default.
SQL_RD_OFF = SQLFetchScroll() and in DB2 CLI Version 5.2 and later, SQLFetch(), do not retrieve data after it positions the cursor.

By setting SQL_RETRIEVE_DATA to SQL_RD_OFF, an application can verify if a row exists or retrieve a bookmark for the row without incurring the overhead of retrieving rows.

SQL_ATTR_ROW_ARRAY_SIZE (DB2 CLI v5)

A 32-bit integer value that specifies the number of rows in the rowset. This is the number of rows returned by each call to SQLFetch() or SQLFetchScroll(). The default value is 1.

If the specified rowset size exceeds the maximum rowset size supported by the data source, DB2 CLI substitutes that value and returns SQLSTATE 01S02 (Option value changed).

This option can be specified for an open cursor and can also be set through the RowsetSize argument in SQLSetScrollOptions().

Setting this statement attribute sets the SQL_DESC_ARRAY_SIZE field in the ARD header.

SQL_ATTR_ROW_BIND_OFFSET_PTR (DB2 CLI v5)

A 32-bit integer * value that points to an offset added to pointers to change binding of column data. If this field is non-null, DB2 CLI dereferences the pointer, adds the dereferenced value to each of the deferred fields in the descriptor record (SQL_DESC_DATA_PTR, SQL_DESC_INDICATOR_PTR, and SQL_DESC_OCTET_LENGTH_PTR), and uses the new pointer values when binding. It is set to null by default.

Setting this statement attribute sets the SQL_DESC_BIND_OFFSET_PTR field in the ARD header.

SQL_ATTR_ROW_BIND_TYPE (DB2 CLI v5)

A 32-bit integer value that sets the binding orientation to be used when SQLFetch() or SQLFetchScroll() is called on the associated statement. Column-wise binding is selected by supplying the defined constant SQL_BIND_BY_COLUMN in *ValuePtr. Row-wise binding is selected by supplying a value in *ValuePtr specifying the length of a structure or an instance of a buffer into which result columns will be bound.

The length specified in *ValuePtr must include space for all of the bound columns and any padding of the structure or buffer to ensure that when the address of a bound column is incremented with the specified length, the result will point to the beginning of the same column in the next row. When using the sizeof operator with structures or unions in ANSI C, this behavior is guaranteed.

Column-wise binding is the default binding orientation for SQLFetch() and SQLFetchScroll().

Setting this statement attribute sets the SQL_DESC_BIND_TYPE field in the ARD header.

SQL_ATTR_ROW_NUMBER (DB2 CLI v5)

A 32-bit integer value that is the number of the current row in the entire result set. If the number of the current row cannot be determined or there is no current row, DB2 CLI returns 0.

This attribute can be retrieved by a call to SQLGetStmtAttr(), but not set by a call to SQLSetStmtAttr().

SQL_ATTR_ROW_OPERATION_PTR (DB2 CLI v5)

A 16-bit unsigned integer * value that points to an array of UDWORD values used to ignore a row during a bulk operation using SQLSetPos(). Each value is set to either SQL_ROW_PROCEED (for the row to be included in the bulk operation) or SQL_ROW_IGNORE (for the row to be excluded from the bulk operation).

This statement attribute can be set to a null pointer, in which case DB2 CLI does not return row status values. This attribute can be set at any time, but the new value is not used until the next time SQLFetch(), SQLFetchScroll(), or SQLSetPos() is called.

Setting this statement attribute sets the SQL_DESC_ARRAY_STATUS_PTR field in the ARD.

SQL_ATTR_ROW_STATUS_PTR (DB2 CLI v5)

A 16-bit unsigned integer * value that points to an array of UWORD values containing row status values after a call to SQLFetch() or SQLFetchScroll(). The array has as many elements as there are rows in the rowset.

Setting this statement attribute sets the SQL_DESC_ARRAY_STATUS_PTR field in the IRD header.

SQL_ATTR_ROWS_FETCHED_PTR (DB2 CLI v5)

A 32-bit unsigned integer * value that points to a buffer in which to return the number of rows fetched after a call to SQLFetch() or SQLFetchScroll().

Setting this statement attribute sets the SQL_DESC_ROWS_PROCESSED_PTR field in the IRD header.

This attribute is mapped by DB2 CLI to the RowCountPtr array in a call to SQLExtendedFetch().

SQL_ATTR_ROWSET_SIZE (DB2 CLI v2)

DB2 CLI applications should now use SQLFetchScroll() rather than SQLExtendedFetch(). Applications should also use the statement attribute SQL_ATTR_ROW_ARRAY_SIZE to set the number of rows in the rowset. See Specifying the Rowset Returned from the Result Set for more information.

A 32-bit integer value that specifies the number of rows in the rowset. A rowset is the array of rows returned by each call to SQLExtendedFetch(). The default value is 1, which is equivalent to making a single SQLFetch(). This option can be specified even when the cursor is open and becomes effective on the next SQLExtendedFetch() call.

SQL_ATTR_SIMULATE_CURSOR (DB2 CLI v5)

This statement attribute is not supported by DB2 CLI but is defined by ODBC.

A 32-bit integer value that specifies whether simulated positioned update and delete statements guarantee that such statements affect only one single row.

SQL_ATTR_STMTTXN_ISOLATION (DB2 CLI v2)

See SQL_ATTR_TXN_ISOLATION below.

SQL_ATTR_TXN_ISOLATION (DB2 CLI v2)

A 32-bit integer value that sets the transaction isolation level for the current StatementHandle.

This option cannot be set if there is an open cursor on this statement handle (SQLSTATE 24000).

The value SQL_ATTR_STMTTXN_ISOLATION is synonymous with SQL_ATTR_TXN_ISOLATION. However, since the ODBC Driver Manager will reject the setting of SQL_ATTR_TXN_ISOLATION as a statement option, ODBC applications that need to set translation isolation level on a per statement basis must use the manifest constant SQL_ATTR_STMTTXN_ISOLATION instead on the SQLSetStmtAttr() call.

The transaction isolation level can also be set using the TXNISOLATION DB2 CLI/ODBC configuration keyword. See Configuring db2cli.ini for more information.

This attribute (or corresponding keyword) is only applicable if the default isolation level is used. If the application has specifically set the isolation level then this attribute will have no effect.
Note: It is an IBM extension to allow setting this option at the statement level.

SQL_ATTR_USE_BOOKMARKS (DB2 CLI v5)

A 32-bit integer value that specifies whether an application will use bookmarks with a cursor:

SQL_UB_OFF = Off (the default)
SQL_UB_VARIABLE = An application will use bookmarks with a cursor, and DB2 CLI will provide variable-length bookmarks if they are supported.

To use bookmarks with a cursor, the application must specify this option with the SQL_UB_VARIABLE value before opening the cursor.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 171. SQLSetStmtAttr SQLSTATEs

SQLSTATE Description Explanation
01000 Warning. Informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
01S02 Option value changed. DB2 CLI did not support the value specified in *ValuePtr, or the value specified in *ValuePtr was invalid because of SQL constraints or requirements, so DB2 CLI substituted a similar value. (Function returns SQL_SUCCESS_WITH_INFO.)
08S01 Communication link failure. The communication link between DB2 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 was unable to allocate memory required to support execution or completion of the function.
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. (DB2 CLI returns this SQLSTATE only for connection and statement attributes that accept a discrete set of values, such as SQL_ATTR_ACCESS_MODE or SQL_ATTR_ASYNC_ENABLE. For all other connection and statement attributes, the driver must verify the value specified in *ValuePtr.)
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 DB2 CLI.
HYC00 Driver not capable. The value specified for the argument Attribute was a valid connection or statement attribute for the version of the DB2 CLI driver, but was not supported by the data source.

SQLSTATE	Description	Explanation
01000	Warning.	Informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
01S02	Option value changed.	DB2 CLI did not support the value specified in `ValuePtr`, or the value specified in `ValuePtr` was invalid because of SQL constraints or requirements, so DB2 CLI substituted a similar value. (Function returns SQL_SUCCESS_WITH_INFO.)
08S01	Communication link failure.	The communication link between DB2 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 was unable to allocate memory required to support execution or completion of the function.
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`. (DB2 CLI returns this SQLSTATE only for connection and statement attributes that accept a discrete set of values, such as SQL_ATTR_ACCESS_MODE or SQL_ATTR_ASYNC_ENABLE. For all other connection and statement attributes, the driver must verify the value specified in `ValuePtr`.)
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 DB2 CLI.
HYC00	Driver not capable.	The value specified for the argument `Attribute` was a valid connection or statement attribute for the version of the DB2 CLI driver, but was not supported by the data source.

Restrictions

None.

Example

See SQLFetchScroll().

References

[ Top of Page | Previous Page | Next Page ]