Purpose
| Specification: | DB2 CLI 5.0 | ODBC 1 |
SQLSetPos() sets the cursor position in a rowset.
Syntax
SQLRETURN SQLSetPos (SQLHSTMT StatementHandle,
SQLUSMALLINT RowNumber,
SQLUSMALLINT Operation,
SQLUSMALLINT LockType);
Function Arguments
Table 165. SQLSetPos Arguments
| Data Type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | input | Statement handle. |
| SQLUSMALLINT | RowNumber | input |
Position of the row in the rowset on which to perform the operation specified with the Operation argument. If RowNumber is 0, the operation applies to every row in the rowset. For additional information, see RowNumber Argument.
|
| SQLUSMALLINT | Operation | input | Operation to perform:
ODBC also specifies the following operations which DB2 CLI does not support:
|
| SQLUSMALLINT | LockType | input | Specifies how to lock the row after performing the operation specified in
the Operation argument.
|
Usage
The RowNumber argument specifies the number of the row in the rowset on which to perform the operation specified by the Operation argument. If RowNumber is 0, the operation applies to every row in the rowset. Except for the SQL_ADD, SQL_DELETE_BY_BOOKMARK, SQL_UPDATE_BY_BOOKMARK, and SQL_REFRESH_BY_BOOKMARK operations, RowNumber must be a value from 0 to the number of rows in the rowset. For the SQL_ADD operation, RowNumber can be any value; generally it is either 0 (to add as many rows as there are in the rowset) or the number of rows in the rowset plus 1 (to add the data from an extra row of buffers allocated for this purpose).
Note In the C language, arrays are 0-based, while the RowNumber argument is 1-based. For example, to update the fifth row of the rowset, an application modifies the rowset buffers at array index 4, but specifies an RowNumber of 5.
All operations position the cursor on the row specified by RowNumber (except for SQL_ADD, SQL_UPDATE_BY_BOOKMARK, SQL_DELETE_BY_BOOKMARK, and SQL_REFRESH_BY_BOOKMARK, which DB2 CLI does not support, and which do not change the cursor position). The following operations require a cursor position:
An application can specify a cursor position when it calls SQLSetPos(). Generally, it calls SQLSetPos() with the SQL_POSITION or SQL_REFRESH operation to position the cursor before executing a positioned update or delete statement or calling SQLGetData().
DB2 CLI does not support SQL_ADD, SQL_UPDATE or SQL_DELETE, which are defined by ODBC. The following operations, however, are supported. To determine which options are supported by a data source, an application calls SQLGetInfo() with the SQL_POS_OPERATIONS information type.
DB2 CLI positions the cursor on the row specified by RowNumber.
The contents of the row status array pointed to by the SQL_ATTR_ROW_OPERATION_PTR statement attribute are ignored for the SQL_POISTION Operation.
DB2 CLI positions the cursor on the row specified by RowNumber and refreshes data in the rowset buffers for that row. For more information about how DB2 CLI returns data in the rowset buffers, see the descriptions of row-wise and column-wise binding in SQLFetchScroll().
SQLSetPos() with an Operation of SQL_REFRESH simply updates the status and content of the rows within the current fetched rowset. This includes refreshing the bookmarks. The data in the buffers is refreshed, but not refetched, so the membership in the rowset is fixed.
A successful refresh with SQLSetPos() will change a row status of SQL_ROW_ADDED to SQL_ROW_SUCCESS (if the row status array exists).
A refresh with SQLSetPos() will change a row status of SQL_ROW_UPDATED to the row's new status (if the row status array exists).
If an error occurs in a SQLSetPos() operation on a row, the row status is set to SQL_ROW_ERROR (if the row status array exists).
For a cursor opened with a SQL_ATTR_CONCURRENCY statement attribute of SQL_CONCUR_ROWVER or SQL_CONCUR_VALUES, a refresh with SQLSetPos() will update the optimistic concurrency values used by the data source to detect that the row has changed. This occurs for each row that is refreshed.
The contents of the row status array are ignored for the SQL_REFRESH Operation.
The LockType argument provides a way for applications to control concurrency. Generally, data sources that support concurrency levels and transactions will only support the SQL_LOCK_NO_CHANGE value of the LockType argument.
Although the LockType argument is specified for a single statement, the lock accords the same privileges to all statements on the connection. In particular, a lock that is acquired by one statement on a connection can be unlocked by a different statement on the same connection.
ODBC defines the following LockType arguments. DB2 CLI supports
SQL_LOCK_NO_CHANGE. To determine which locks are supported by a data source,
an application calls SQLGetInfo() with the SQL_LOCK_TYPES
information type.
| LockType Argument | Lock Type |
|---|---|
| SQL_LOCK_NO_CHANGE | Ensures that the row is in the same locked or unlocked state as it was before SQLSetPos() was called. This value of LockType allows data sources that do not support explicit row-level locking to use whatever locking is required by the current concurrency and transaction isolation levels. |
| SQL_LOCK_EXCLUSIVE | Not supported by DB2 CLI. Locks the row exclusively. |
| SQL_LOCK_UNLOCK | Not supported by DB2 CLI. Unlocks the row. |
Status and Operation Arrays
The following status and operation arrays are used when calling SQLSetPos():
The number of elements in the status and operation arrays must equal the number of rows in the rowset (as defined by the SQL_ATTR_ROW_ARRAY_SIZE statement attribute).
For information about the row status array, see SQLFetch - Fetch Next Row.
Return Codes
Diagnostics
Table 167. SQLSetPos SQLSTATEs
| SQLSTATE | Description | Explanation |
|---|---|---|
| 01000 | Warning. | Informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
| 01004 | Data truncated. | The Operation argument was SQL_REFRESH, and string or binary data returned for a column or columns with a data type of SQL_C_CHAR or SQL_C_BINARY resulted in the truncation of non-blank character or non-NULL binary data |
| 01S01 | Error in row. | The RowNumber argument was 0 and an error occurred in one or
more rows while performing the operation specified with the Operation
argument.
(SQL_SUCCESS_WITH_INFO is returned if an error occurs on one or more, but not all, rows of a multirow operation, and SQL_ERROR is returned if an error occurs on a single-row operation.) |
| 01S07 | Fractional truncation. | The Operation argument was SQL_REFRESH, the data type of the application buffer was not SQL_C_CHAR or SQL_C_BINARY, and the data returned to application buffers for one or more columns was truncated. For numeric data types, the fractional part of the number was truncated. For time and timestamp data types, the fractional portion of the time was truncated. |
| 07006 | Invalid conversion. | The data value of a column in the result set could not be converted to the data type specified by TargetType in the call to SQLBindCol(). |
| 07009 | Invalid descriptor index. | The argument Operation was SQL_REFRESH or SQL_UPDATE and a column was bound with a column number greater than the number of columns in the result set. |
| 21S02 | Degrees of derived table does not match column list. | The argument Operation was SQL_UPDATE and no columns were updateable because all columns were either unbound, read-only, or the value in the bound length/indicator buffer was SQL_COLUMN_IGNORE. |
| 22001 | String data right truncation. | The assignment of a character or binary value to a column resulted in the truncation of non-blank (for characters) or non-null (for binary)characters or bytes. |
| 22003 | Numeric value out of range. | The argument Operation was SQL_UPDATE and the assignment of a
numeric value to a column in the result set caused the whole (as opposed to
fractional) part of the number to be truncated.
The argument Operation was SQL_REFRESH, and returning the numeric value for one or more bound columns would have caused a loss of significant digits. |
| 22007 | Invalid datetime format. | The argument Operation was SQL_UPDATE, and the assignment of a
date or timestamp value to a column in the result set caused the year, month,
or day field to be out of range.
The argument Operation was SQL_REFRESH, and returning the date or timestamp value for one or more bound columns would have caused the year, month, or day field to be out of range. |
| 22008 | Datetime field overflow. | The Operation argument was SQL_UPDATE, and the performance of
datetime arithmetic on data being sent to a column in the result set resulted
in a datetime field (the year, month, day, hour, minute, or second field) of
the result being outside the permissible range of values for the field, or
being invalid based on the natural rules for datetimes based on the Gregorian
calendar.
The Operation argument was SQL_REFRESH, and the performance of datetime arithmetic on data being retrieved from the result set resulted in a datetime field (the year, month, day, hour, minute, or second field) of the result being outside the permissible range of values for the field, or being invalid based on the natural rules for datetimes based on the Gregorian calendar. |
| 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. |
| HY008 | Operation was cancelled |
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle. The function was called and, before it completed execution,
SQLCancel() was called on the StatementHandle from a
different thread in a multithread application.
|
| HY010 | Function sequence error. | The specified StatementHandle was not in an executed state. The
function was called without first calling SQLExecDirect(),
SQLExecute(), or a catalog function.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called. SQLExecute(), SQLExecDirect(), or SQLSetPos() 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. A Version 2 DB2 CLI application called SQLSetPos() for a StatementHandle before SQLFetchScroll() was called or after SQLFetch() was called, and before SQLFreeStmt() was called with the SQL_CLOSE option. |
| HY011 | Operation invalid at this time. | A Version 2 DB2 CLI application set the SQL_ATTR_ROW_STATUS_PTR statement attribute; then SQLSetPos() was called before SQLFetch(), SQLFetchScroll(), or SQLExtendedFetch() was called. |
| HY090 | Invalid string or buffer length. | The Operation argument was SQL_ADD, SQL_UPDATE, or
SQL_UPDATE_BY_BOOKMARK, a data value was a null pointer, and the column length
value was not 0, SQL_DATA_AT_EXEC, SQL_COLUMN_IGNORE, SQL_NULL_DATA, or less
than or equal to SQL_LEN_DATA_AT_EXEC_OFFSET.
The Operation argument was SQL_ADD, SQL_UPDATE, or SQL_UPDATE_BY_BOOKMARK, a data value was not a null pointer, and the column length value was less than 0, but not equal to SQL_DATA_AT_EXEC, SQL_COLUMN_IGNORE, SQL_NTS, or SQL_NULL_DATA, or less than or equal to SQL_LEN_DATA_AT_EXEC_OFFSET. A value in a length/indicator buffer was SQL_DATA_AT_EXEC; the SQL type was either SQL_LONGVARCHAR, SQL_LONGVARBINARY, or a other, data-source-specific data type; and the SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo() was "Y". |
| HY092 | Option type out of range. | The Operation argument was SQL_UPDATE_BY_BOOKMARK, SQL_DELETE_BY_BOOKMARK, or SQL_REFRESH_BY_BOOKMARK, and the SQL_ATTR_USE_BOOKMARKS statement attribute was set to SQL_UB_OFF. |
| HY107 | Row value out of range. | The value specified for the argument RowNumber was greater than the number of rows in the rowset. |
| HY109 | Invalid cursor position. | The cursor associated with the StatementHandle was defined as
forward only, so the cursor could not be positioned within the rowset. See the
description for the SQL_ATTR_CURSOR_TYPE attribute in
SQLSetStmtAttr().
The Operation argument was SQL_UPDATE, SQL_DELETE, or SQL_REFRESH, and the row identified by the RowNumber argument had been deleted or had not be fetched. The RowNumber argument was 0 and the Operation argument was SQL_POSITION. |
| HYC00 | Driver not capable. | DB2 CLI or the data source does not support the operation requested in the Operation argument or the LockType argument. |
| HYT00 | Timeout expired | The query timeout period expired before the data source returned the result set. The timeout period is set through SQLSetStmtAttr() with an Attribute of SQL_ATTR_QUERY_TIMEOUT. |
Restrictions
None.
Example
See the README file in the sqllib\samples\cli (or sqllib/samples/cli) subdirectory for a list of appropriate samples.
References
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 168. 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:
|
| 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 169. 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_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_BIND_OFFSET_PTR | SQL_DESC_BIND_OFFSET_PTR | ARD |
| SQL_ATTR_ROW_BIND_TYPE | SQL_DESC_BIND_TYPE | ARD |
| SQL_ATTR_ROW_STATUS_PTR | SQL_DESC_ARRAY_STATUS_PTR | IRD |
| SQL_ATTR_ROWS_FETCHED_PTR | SQL_DESC_ROWS_PROCESSED_PTR | IRD |
| SQL_ATTR_ROWSET_SIZE | SQL_DESC_ARRAY_SIZE | ARD |
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_ |
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.
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 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.
A 32-bit integer value that specifies whether a function called with the specified statement is executed asynchronously:
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.
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.
A 32-bit integer value that specifies the cursor concurrency:
The default value for SQL_ATTR_CONCURRENCY is SQL_CONCUR_READ_ONLY.
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 the driver supports the SELECT FOR UPDATE statement, and such a 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 the driver supports 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, the DB2 CLI substitutes a different concurrency and returns SQLSTATE 01S02 (Option value changed). For SQL_CONCUR_VALUES, DB2 CLI substitutes SQL_CONCUR_ROWVER, and vice versa. For SQL_CONCUR_LOCK, the DB2 CLI substitutes, in order, SQL_CONCUR_ROWVER or SQL_CONCUR_VALUES. The validity of the substituted value is not checked until execution time.
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.
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. |
A 32-bit integer value that specifies the cursor type. The supported values are:
The default value is SQL_CURSOR_FORWARD_ONLY.
This option cannot be specified for an open cursor.
| Note: | The following values have also been defined by ODBC, but are not supported by
DB2 CLI:
|
Specifies whether the PREPARE request is deferred until the corresponding execute request is issued.
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. |
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.
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 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. |
A 32-bit integer value that specifies whether automatic population of the IPD is performed:
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.
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.
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().
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().
A 32-bit integer value that specifies the number of rows in the keyset for a keyset-driven cursor. If the keyset size is 0 (the default), the cursor is fully keyset-driven. If the keyset size is greater than 0, the cursor is mixed (keyset-driven within the keyset and dynamic outside of the keyset). The default keyset size is 0.
If the specified size exceeds the maximum keyset size, DB2 CLI substitutes that size and returns SQLSTATE 01S02 (Option value changed).
SQLFetchScroll() returns an error if the keyset size is greater than 0 and less than the rowset size.
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.
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.
A 32-bit integer value that determines how the string arguments of catalog functions are treated.
This is the default value.
The TableType argument of SQLTables(), which takes a list of values, is not affected by this attribute.
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. |
A 32-bit integer value that specified whether DB2 CLI will scan SQL strings for escape clauses. The two permitted values are:
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.
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.
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.
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.
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:
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 SQLFetch(), SQLFetchScroll(), or SQLSetPos() is called.
Setting this statement attribute sets the SQL_DESC_ARRAY_STATUS_PTR field in the IPD header.
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:
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).
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.
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.
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. |
A 32-bit integer value:
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.
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.
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.
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.
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().
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.
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.
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 IRD header.
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().
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.
This statement attribute is not supported by DB2 CLI but is defined by ODBC.
A 32-bit integer value that specifies whether drivers that simulate positioned update and delete statements guarantee that such statements affect only one single row.
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.
| Note: | It is an IBM extension to allow setting this option at the statement level. |
A 32-bit integer value that specifies whether an application will use bookmarks with a cursor:
To use bookmarks with a cursor, the application must specify this option with the SQL_UB_VARIABLE value before opening the cursor.
Return Codes
Diagnostics
Table 170. 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. |
Restrictions
None.
Example
See SQLFetchScroll().
References