| 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 166. 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:
For additional information, see Operation Argument. ODBC also specifies the following operations for backwards compatibility only, which DB2 CLI also supports:
While DB2 CLI does support SQL_ADD in SQLSetPos() calls, it is suggested that you use SQLBulkOperations() with the Operation argument set to SQL_ADD. See SQLBulkOperations - Add, Update, Delete or Fetch a Set of Rows for more information. |
| 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. 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 an RowNumber of 5.
All operations position the cursor on the row specified by RowNumber. 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().
To determine which options are supported by a data source, an application calls SQLGetInfo() with one of the following information types, depending on the type of cursor:
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 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.
DB2 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.
DB2 CLI positions the cursor on the row specified by RowNumber and deletes the underlying row of data. It changes the corresponding element of the row status array to SQL_ROW_DELETED. After the row has been deleted, the following are not valid for the row:
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 DB2 CLI also supports. It is suggested, however, that you use SQLBulkOperations() with the Operation argument set to SQL_ADD.
See SQLBulkOperations - Add, Update, Delete or Fetch a Set of Rows for more information.
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. |
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 168. 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.
(The complete sample tbread.c is also available here .)
/* From the CLI sample TBREAD.C */
/* ... */
sqlrc = SQLSetPos( hstmt, 3, SQL_POSITION, SQL_LOCK_NO_CHANGE);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLGetData(hstmt, 0, SQL_C_LONG, bookmark.val, 4, &bookmark.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_FETCH_BOOKMARK_PTR,
(SQLPOINTER) bookmark.val,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
/* ... */
sqlrc = SQLSetPos( hstmt, 3, SQL_POSITION, SQL_LOCK_NO_CHANGE);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLGetData(hstmt, 0, SQL_C_LONG, bookmark.val, 4, &bookmark.ind);
STMT_HANDLE_CHECK( hstmt, sqlrc);
sqlrc = SQLSetStmtAttr( hstmt,
SQL_ATTR_FETCH_BOOKMARK_PTR,
(SQLPOINTER) bookmark.val,
0);
STMT_HANDLE_CHECK( hstmt, sqlrc);
References