DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
| Specification: | CLI 5.0 | ODBC 1 |
SQLSetPos() sets the cursor position in a rowset.
SQLRETURN SQLSetPos (
SQLHSTMT StatementHandle, /* hstmt */
SQLSETPOSIROW RowNumber, /* irow */
SQLUSMALLINT Operation, /* fOption */
SQLUSMALLINT LockType); /* fLock */| Data type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | input | Statement handle. |
| SQLSETPOSIROW | 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 for backwards
compatibility only, which CLI also
supports:
While CLI does support SQL_ADD in SQLSetPos() calls, it is suggested that you use SQLBulkOperations() with the Operation argument set to SQL_ADD. |
| SQLUSMALLINT | LockType | input | Specifies how to lock the row after performing the operation
specified in the Operation argument.
|
RowNumber argument
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. RowNumber must be a value from 0 to the number of rows in the rowset.
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 a RowNumber of 5.
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().
Operation argument
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_POSITION Operation.
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 CLI returns data in the rowset buffers, see the descriptions of row-wise and column-wise binding.
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 not change a row status of SQL_ROW_DELETED. Deleted rows within the rowset will continue to be marked as deleted until the next fetch. The rows will disappear at the next fetch if the cursor supports packing (in which case a subsequent SQLFetch() or SQLFetchScroll() does not return deleted rows).
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.
CLI positions the cursor on the row specified by RowNumber and updates the underlying row of data with the values in the rowset buffers (the TargetValuePtr argument in SQLBindCol()). It retrieves the lengths of the data from the length/indicator buffers (the StrLen_or_IndPtr argument in SQLBindCol()). If the length of any column is SQL_COLUMN_IGNORE, the column is not updated. After updating the row, the corresponding element of the row status array is updated to SQL_ROW_UPDATED or SQL_ROW_SUCCESS_WITH_INFO (if the row status array exists).
The row operation array pointed to by the SQL_ATTR_ROW_OPERATION_PTR statement attribute can be used to indicate that a row in the current rowset should be ignored during a bulk update. For more information, see Status and operation arrays.
Deleted rows remain visible to static and keyset-driven cursors; however, the entry in the implementation row status array (pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute) for the deleted row is changed to SQL_ROW_DELETED.
The row operation array pointed to by the SQL_ATTR_ROW_OPERATION_PTR statement attribute can be used to indicate that a row in the current rowset should be ignored during a bulk delete. For more information, see Status and operation arrays.
ODBC also specifies the SQL_ADD Operation for backwards compatibility only, which CLI also supports. It is suggested, however, that you use SQLBulkOperations() with the Operation argument set to SQL_ADD.
LockType argument
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.
| 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 CLI. Locks the row exclusively. |
| SQL_LOCK_UNLOCK | Not supported by CLI. Unlocks the row. |
Status and operation arrays
| 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 or a column number less than 0. |
| 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 is unable to allocate memory required to support execution or completion of the function. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information on process-level memory limitations. |
| HY008 | Operation was cancelled. | Asynchronous processing was enabled for StatementHandle. The function was called and before it completed execution, SQLCancel() was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle. |
| 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. An ODBC 2.0 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. | An ODBC 2.0 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. | 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. |
None.
/* set the cursor position in a rowset */
cliRC = SQLSetPos(hstmt, 3, SQL_POSITION, SQL_LOCK_NO_CHANGE);