SQLSetStmtAttr() - Set statement attributes

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

ODBC specifications for SQLSetStmtAttr()

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

Syntax

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

Function arguments

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

Table 2. SQLSetStmtAttr() arguments
Data type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLINTEGER Attribute input Statement attribute to set. Refer to Table 3 for a complete list of attributes.
SQLPOINTER ValuePtr input Pointer to the value to be associated with Attribute. Depending on the value of Attribute, ValuePtr will be a 32-bit unsigned integer value or point to a nul-terminated character string. If the Attribute argument is a driver-specific value, the value in ValuePtr might be a signed integer.
SQLINTEGER StringLength input Information about the *ValuePtr argument.
  • For ODBC-defined attributes:
    • If ValuePtr points to a character string, this argument should be the length of *ValuePtr.
    • If ValuePtr points to an integer, BufferLength is ignored.
  • For driver-defined attributes (IBM® extension):
    • If ValuePtr points to a character string, this argument should be the length of *ValuePtr or SQL_NTS if it is a nul-terminated string.
    • If ValuePtr points to an integer, BufferLength is ignored.

Usage

Statement attributes for a statement remain in effect until they are changed by another call to SQLSetStmtAttr() or until the statement is dropped by calling SQLFreeHandle(). Calling SQLFreeStmt() with the SQL_CLOSE, SQL_UNBIND or the SQL_RESET_PARAMS attribute 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 ODBC returns SQL_SUCCESS_WITH_INFO and SQLSTATE 01S02 (attribute value changed). To determine the substituted value, an application calls SQLGetStmtAttr().

The format of the information set with ValuePtr depends on the specified Attribute. SQLSetStmtAttr() accepts attribute information either in the format of a nul-terminated character string or a 32-bit integer value. The format of each ValuePtr value is noted in the attribute descriptions shown in Table 3. This format applies to the information returned for each attribute in SQLGetStmtAttr(). Character strings that the ValuePtr argument of SQLSetStmtAttr() point to have a length of StringLength.

Db2 ODBC supports all of the ODBC 2.0 Attribute values that are renamed in ODBC 3.0. For a summary of the Attribute values renamed in ODBC 3.0, refer to "Changes to SQLSetStmtAttr() attributes".

Overriding Db2 CCSIDs from DSNHDECP: Db2 ODBC extensions to SQLSetStmtAttr() allow an application to override the Unicode, EBCDIC, or ASCII CCSID settings of the Db2 subsystem to which they are currently attached, using the statement attributes SQL_CCSID_CHAR and SQL_CCSID_GRAPHIC. This extension is intended for applications that are attempting to send and receive data to and from Db2 in a CCSID that differs from the default settings in the Db2 DSNHDECP.

The CCSID override applies only to input data bound to parameter markers through SQLBindParameter() or SQLBindFileToParam(), output data that is bound to columns through SQLBindCol() or SQLBindFileToCol(), and output data that is retrieved through SQLGetData().

The CCSID override applies on a statement level only. Db2 will continue to use the default CCSID settings in the Db2 DSNHDECP after the statement is dropped or if SQL_CCSID_DEFAULT is specified.

You can use SQLGetStmtAttr() to query the settings of the current statement handle CCSID override.

The following table lists each Attribute value SQLSetStmtAttr() can set. Values shown in bold are default values.

Table 3. Statement attributes
Attribute ValuePtr contents
SQL_ATTR_BIND_TYPE or SQL_ATTR_ROW_BIND_TYPE 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 (in bytes) of the structure or an instance of a buffer into which result columns are bound.

For row-wise binding, the length (in bytes) 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 points 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.)
SQL_CCSID_CHAR A 32-bit integer value that specifies the CCSID of:
  • Data that is bound to parameter markers with SQLBindParameter() or SQLBindFileToParam()
  • Data that is bound to columns of a result set with SQLBindCol() or SQLBindFileToCol()
  • Data that is retrieved with SQLGetData()
The CCSID applies to data for which the C symbolic data type is SQL_C_CHAR and the SQL data type is one of the following types:
  • SQL_CHAR
  • SQL_VARCHAR
  • SQL_LONGVARCHAR
  • SQL_CLOB
  • SQL_TYPE_DATE
  • SQL_TYPE_TIME
  • SQL_TYPE_TIMESTAMP
  • Start of changeSQL_TYPE_TIMESTAMP_WITH_TIMEZONEEnd of change
SQL_CCSID_GRAPHIC A 32-bit integer value that specifies the CCSID of:
  • Data that is bound to parameter markers with SQLBindParameter() or SQLBindFileToParam()
  • Data that is bound to columns of a result set with SQLBindCol() or SQLBindFileToCol()
  • Data that is retrieved with SQLGetData()
The CCSID applies to data for which the C symbolic data type is SQL_C_DBCHAR and the SQL data type is one of the following types:
  • SQL_GRAPHIC
  • SQL_VARGRAPHIC
  • SQL_LONGVARGRAPHIC
  • SQL_DBCLOB
  • SQL_TYPE_DATE
  • SQL_TYPE_TIME
  • SQL_TYPE_TIMESTAMP
  • Start of changeSQL_TYPE_TIMESTAMP_WITH_TIMEZONEEnd of change
Start of changeSQL_ATTR_CLIENT_TIME_ZONEEnd of change Start of changeA null-terminated character string in the format ±hh:mm, containing the time zone information with values ranging from -12:59 and +14:00. Specifying this attribute overrides the client's OS default time zone. End of change
SQL_ATTR_CLOSE_BEHAVIOR A 32-bit integer value that forces the release of locks upon an underlying CLOSE CURSOR operation. The possible values are:
  • SQL_CC_NO_RELEASE: locks are not released when the cursor on this statement handle is closed.
  • SQL_CC_RELEASE: locks are released when the cursor on this statement handle is closed.
Typically cursors are explicitly closed when the function SQLFreeStmt() is called with the fOption argument set to SQL_CLOSE or SQLCloseCursor() is called. In addition, the end of the transaction (when a commit or rollback is issued) can also close the cursor (depending on the WITH HOLD attribute currently in use).
SQL_ATTR_CONCURRENCY
A 32-bit integer value that specifies the cursor concurrency:
  • SQL_CONCUR_READ_ONLY - Cursor is read-only. No updates are allowed. Supported for forward-only and static cursors.
  • SQL_CONCUR_LOCK - Cursor uses the lowest level of locking sufficient to ensure that the row can be updated. Supported for forward-only and dynamic cursors.

The default value for SQL_ATTR_CONCURRENCY is SQL_CONCUR_READ_ONLY for static and forward-only cursors. The default for dynamic cursors is SQL_CONCUR_LOCK.

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 is changed at execution time, and a warning is issued when SQLExecDirect() or SQLPrepare() is called.

If a SELECT FOR UPDATE statement is executed when the value of SQL_ATTR_CONCURRENCY is set to SQL_CONCUR_READ_ONLY, an error is 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 is changed at execution time, and a warning is issued when SQLExecDirect() or SQLPrepare() is called.

If the specified concurrency is not supported by the data source, Db2 ODBC substitutes a different concurrency and returns a warning. The order of substitution depends on the cursor type:
  • Forward-only: SQL_CONCUR_LOCK is substituted for SQL_CONCUR_ROWVER or SQL_CONCUR_VALUES.
  • Static: SQL_CONCUR_READ_ONLY is substituted for SQL_CONCUR_ROWVER or SQL_CONCUR_VALUES.
  • Dynamic: SQL_CONCUR_LOCK is substituted for SQL_CONCUR_ROWVER or SQL_CONCUR_VALUES.
Unsupported attribute values: ODBC architecture defines the following values, which are not supported by Db2 ODBC:
  • SQL_CONCUR_VALUES - Cursor uses optimistic concurrency control, comparing values.
  • SQL_CONCUR_ROWVER - Cursor uses optimistic concurrency control.
If one of these values is used, SQL_SUCCESS_WITH_INFO (SQLSTATE 01S02) is returned and the option value is changed.
SQL_ATTR_CURSOR_HOLD1 A 32-bit integer which specifies whether the cursor associated with this statement handle 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
  • SQL_CURSOR_HOLD_OFF

The default value when a statement handle is first allocated is SQL_CURSOR_HOLD_ON.

This attribute cannot be specified while there is an open cursor on this statement handle.
SQL_ATTR_CURSOR_SCROLLABLE 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.
  • SQL_SCROLLABLE - Scrollable cursors are required on the statement handle. When the application calls SQLFetchScroll(), it can specify any valid value of FetchOrientation, for cursor positioning in modes other than the sequential mode.
SQL_ATTR_CURSOR_SENSITIVITY A 32-bit integer that specifies whether changes that are made by other cursors are visible to the cursors on the statement handle. Setting this attribute affects subsequent calls to SQLExecDirect() and SQLExecute(). The supported values are:
  • SQL_UNSPECIFIED - The cursor type, and whether changes that are made by other cursors are visible to the cursors on the statement handle, are unspecified. Cursors on the statement handle can make visible none, some or all such changes.
  • SQL_INSENSITIVE - All cursors on the statement handle show the result set without reflecting any changes that are made to it by any other cursor. Insensitive cursors are read-only. This attribute corresponds to a static cursor that has a concurrency that is read-only.
  • SQL_SENSITIVE - Corresponds to a static cursor that has a read-only concurrency.
SQL_ATTR_CURSOR_TYPE
A 32-bit integer value that specifies the cursor type. The supported values are:
  • SQL_CURSOR_FORWARD_ONLY - Cursor behaves as a forward only scrolling cursor.
  • SQL_CURSOR_STATIC - The data in the result set is static.
  • SQL_CURSOR_DYNAMIC - The cursor detects all changes in the result set.
These options cannot be set if there is an open cursor on the associated statement handle.

If the specified cursor type is not supported by the data source, Db2 ODBC substitutes a different cursor type and returns a warning. For a dynamic cursor, Db2 ODBC substitutes a different cursor type, in the following order: a static cursor or a forward-only cursor.

Unsupported attribute values: ODBC architecture defines the SQL_CURSOR_KEYSET_DRIVEN value, which is not supported by Db2 ODBC. If this value is specified, Db2 ODBC sets the statement attribute to SQL_CURSOR_STATIC or SQL_CURSOR_FORWARD_ONLY, and returns SQLSTATE 01S02 (Option value changed). In this case the application needs to call SQLGetStmtAttr() to query the value that is set.
SQL_ATTR_MAX_LENGTH 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_ATTR_MAX_LENGTH is less than the amount of data available, an SQLGetData() call or fetch returns 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 ODBC attempts to return all available data for character or binary type data.
SQL_ATTR_MAX_ROWS 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_NODESCRIBE
A 32-bit integer which specifies whether Db2 ODBC should automatically describe the column attributes of the result set or wait to be informed by the application using SQLSetColAttributes().
  • SQL_NODESCRIBE_OFF
  • SQL_NODESCRIBE_ON

This attribute cannot be specified while there is an open cursor on this statement handle.

This attribute is used in conjunction with the function SQLSetColAttributes() by an application which has prior knowledge of the exact nature of the result set to be returned and which does not want to incur the extra network traffic associated with the descriptor information needed by Db2 ODBC to provide client side processing.

IBM specific: This attribute is an IBM-defined extension.
SQL_ATTR_NOSCAN A 32-bit integer value that specifies whether Db2 ODBC will scan SQL strings for escape clauses. The two permitted values are:
  • SQL_NOSCAN_OFF - SQL strings are scanned for escape clause sequences.
  • 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 eliminates some of the overhead processing associated with scanning.
SQL_ATTR_PARAMOPT_ATOMIC A 32-bit integer value that determines whether the application uses atomic or non-atomic SQL for the underlying processing of multi-row insert operations. The attribute value takes effect after SQLSetStmtAttr() is used to specify multiple values for parameter markers for an SQL INSERT statement. Possible values are:
SQL_ATOMIC_YES
The application uses atomic SQL for the underlying processing of multi-row insert operations. This is the default.

Specification of SQL_ATOMIC_YES for a connection to a server that does not support multi-row inserts results in SQLSTATE 01S02 (option value changed). The attribute value is set to SQL_ATOMIC_NO.

SQL_ATOMIC_NO
The application uses non-atomic SQL for the underlying processing of multi-row insert operations.
SQL_ATTR_PARAMSET_SIZE A 32-bit unsigned integer value that specifies the number of values for each parameter. If SQL_ATTR_PARAMSET_SIZE is greater than 1, the rgbValue argument in SQLBindParameter() points to an array of parameter values and pcbValue argument points to an array of lengths. The cardinality of each array is equal to the value of this field.
SQL_ATTR_PARAMS_PROCESSED_PTR

A 32-bit unsigned integer * 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 is returned if this is a null pointer.

If the call to SQLExecDirect() or SQLExecute() that fills in the buffer pointed to by this attribute does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, the contents of the buffer are undefined.
SQL_ATTR_RETRIEVE_DATA A 32-bit integer value that indicates whether Db2 ODBC should retrieve data from the database when SQLFetchScroll() or SQLExtendedFetch() is called. The possible values are:
  • SQL_RD_ON: SQLFetchScroll() or SQLExtendedFetch() retrieves data after it positions the cursor to the specified location.
  • SQL_RD_OFF: SQLFetchScroll() or SQLExtendedFetch() does not retrieve data after it positions the cursor. By setting SQL_RETRIEVE_DATA to SQL_RD_OFF, an application can verify whether a row exists, or retrieve a bookmark for the row without incurring the overhead of retrieving rows.

This attribute cannot be set if the cursor is open.
SQL_ATTR_ROW_ARRAY_SIZE A 32-bit integer value that specifies the number of rows in the row set. This is the number of rows that are returned by each call toSQLFetchScroll(). The default value is 1.

If the specified rowset size exceeds the maximum rowset size that is supported by the data source, Db2 ODBC substitutes the maximum supported value and returns SQLSTATE 01S02 (Option value changed).
SQL_ATTR_ROW_NUMBER 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 ODBC returns 0. This attribute can be retrieved by a call to SQLGetStmtAttr(), but not set by a call to SQLSetStmtAttr().
SQL_ATTR_ROW_OPERATION_POINTER A 16-bit unsigned integer * value that points to an array of UDWORD values that are used to ignore a row when SQLSetPos() is used to perform a bulk operation. Each value is set to 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). During calls to SQLBulkOperations(), rows cannot be ignored by using this array.

If SQL_ATTR_ROW_OPERATION_POINTER is set to a null pointer, Db2 ODBC 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 SQLFetchScroll() or SQLSetPos() is called.
SQL_ATTR_ROW_STATUS_PTR A 16-bit unsigned integer * value that points to an array of UWORD values that contain row status values after a call to SQLFetch() or SQLFetchScroll(). The array has as many elements as there are rows in the rowset.

If SQL_ATTR_ROW_STATUS_PTR is set to a null pointer, Db2 ODBC 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.
Start of changeSQL_ATTR_ROWS_FETCHED_PTREnd of change Start of changeA 32-bit unsigned integer * value that points to a buffer that contains the number of rows that were fetched after a call to SQLFetch() or SQLFetchScroll(). The array has as many elements as there are rows in the rowset.

Db2 ODBC maps SQL_ATTR_ROWS_FETCHED_PTR to the RowCountPtr array in a call to SQLExtendedFetch().

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

Recommendation: Use SQLFetchScroll() rather than SQLExtendedFetch(). Use the statement attribute SQL_ATTR_ROW_ARRAY_SIZE rather than SQL_ATTR_ROWSET_SIZE to set the number of rows in the rowset.
SQL_ATTR_STMTTXN_ISOLATION or SQL_ATTR_TXN_ISOLATION2 A 32-bit integer value that sets the transaction isolation level for the current statement handle. This overrides the default value set at the connection level. For the permitted values, refer to the function SQLSetConnectOption()

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

IBM specific: The value SQL_ATTR_STMTTXN_ISOLATION is synonymous with SQL_ATTR_TXN_ISOLATION. SQL_ATTR_STMTTXN_ISOLATION is an IBM extension to allow setting this attribute at the statement level.

For more information about setting this attribute, refer to Isolation levels for maximum concurrency and data consistency.
SQL_ATTR_USE_BOOKMARKS A 32-bit integer value that specifies whether an application uses bookmarks with a cursor. The only supported attribute value is SQL_UB_OFF, which indicates that an application does not use bookmarks with a cursor.

Unsupported attribute value: ODBC architecture defines the SQL_UB_VARIABLE value, which is not supported by Db2 ODBC. SQL_UB_VARIABLE indicates that an application uses bookmarks with a cursor, and that the ODBC driver provides variable-length bookmarks, if they are supported.
Notes:
  1. You can change the default value for this attribute with the CURSORHOLD keyword in the ODBC initialization file.
  2. You can change the default value for this attribute with the TXNISOLATION keyword in the ODBC initialization file.

Return codes

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

Diagnostics

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

Table 4. SQLSetStmtAttr() SQLSTATEs
SQLSTATE Description Explanation
01000 Warning. Informational message. (SQLSetStmtAttr() returns SQL_SUCCESS_WITH_INFO for this SQLSTATE.)
01S02 Option value changed. Db2 did not support the value specified in *ValuePtr, or the value specified in *ValuePtr is invalid due to SQL constraints or requirements. Therefore, Db2 ODBC substituted a similar value. (SQLSetStmtAttr() returns SQL_SUCCESS_WITH_INFO for this SQLSTATE.)
08S01 Unable to connect to data source. The communication link between the application and the data source failed before the function completed.
24000 Invalid cursor state. The Attribute is SQL_ATTR_CONCURRENCY and the cursor is open.
HY000 General error. An error occurred for which no specific SQLSTATE exists. The error message returned by SQLGetDiagRec() in the *MessageText buffer describes the error and its cause.
HY001 Memory allocation failure. Db2 ODBC is not able to allocate memory for the specified handle.
HY009 Invalid use of a null pointer. A null pointer is passed for ValuePtr and the value in *ValuePtr is a string value.
HY010 Function sequence error. SQLExecute() or SQLExecDirect() is called with the statement handle, and returns SQL_NEED_DATA. This function is called before data is sent for all data-at-execution parameters or columns. Invoke SQLCancel() to cancel the data-at-execution condition.
HY011 Operation invalid at this time. The Attribute is SQL_ATTR_CONCURRENCY and the statement is prepared.
HY024 Invalid attribute value. Given the specified Attribute value, an invalid value is specified in *ValuePtr.
HY090 Invalid string or buffer length. The StringLength argument is less than 0, but is not SQL_NTS.
HY092 Option type out of range. The value specified for the argument Attribute is not valid for this version of Db2 ODBC.
HYC00 Driver not capable. The value specified for the argument Attribute is a valid connection or statement attribute for the version of the Db2 ODBC driver, but is not supported by the data source.

Example

The following example uses SQLSetStmtAttr() to set statement attributes: 
rc = SQLSetStmtAttr( hstmt,
                     SQL_ATTR_CURSOR_HOLD,
                     ( void * ) SQL_CURSOR_HOLD_OFF,
                     0 ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;