SQLSetPos function (CLI) - Set the cursor position in a rowset

Sets the cursor position in a rowset.

Specification:

CLI 5.0
ODBC 1

Syntax

SQLRETURN   SQLSetPos (
               SQLHSTMT          StatementHandle,  /* hstmt */
               SQLSETPOSIROW     RowNumber,        /* irow */
               SQLUSMALLINT      Operation,        /* fOption */
               SQLUSMALLINT      LockType);        /* fLock */

Function arguments

Table 1. SQLSetPos arguments
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: SQL_POSITION SQL_REFRESH SQL_UPDATE SQL_DELETE SQL_ADD ODBC also specifies the following operations for backwards compatibility only, which CLI also supports: SQL_ADD 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. SQL_LOCK_NO_CHANGE ODBC also specifies the following operations which CLI does not support: SQL_LOCK_EXCLUSIVE SQL_LOCK_UNLOCK For additional information, see LockType argument.

Usage

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.

All operations position the cursor on the row specified by RowNumber. The following operations require a cursor position:

Positioned update and delete statements.
Calls to SQLGetData().
Calls to SQLSetPos() with the SQL_DELETE, SQL_REFRESH, and SQL_UPDATE options.

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

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:

SQL_DYNAMIC_CURSOR_ATTRIBUTES1
SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1
SQL_KEYSET_CURSOR_ATTRIBUTES1
SQL_STATIC_CURSOR_ATTRIBUTES1

SQL_POSITION

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.

SQL_REFRESH

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.

SQL_UPDATE

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.

SQL_DELETE

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 operations are not valid for the row:

positioned update and delete statements
calls to SQLGetData()
calls to SQLSetPos() with Operation set to anything except SQL_POSITION.

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.

SQL_ADD

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.

ODBC defines the following LockType arguments. 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.

Table 2. Operation values
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

The following status and operation arrays are used when calling SQLSetPos():

The row status array (as pointed to by the SQL_DESC_ARRAY_STATUS_PTR field in the IRD and the SQL_ATTR_ROW_STATUS_ARRAY statement attribute) contains status values for each row of data in the rowset. The status values are set in this array after a call to SQLFetch(), SQLFetchScroll(), or SQLSetPos(). This array is pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute.
The row operation array (as pointed to by the SQL_DESC_ARRAY_STATUS_PTR field in the ARD and the SQL_ATTR_ROW_OPERATION_ARRAY statement attribute) contains a value for each row in the rowset that indicates whether a call to SQLSetPos() for a bulk operation is ignored or performed. Each element in the array is set to either SQL_ROW_PROCEED (the default) or SQL_ROW_IGNORE. This array is pointed to by the SQL_ATTR_ROW_OPERATION_PTR statement attribute.

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).

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_NEED_DATA
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 3. 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 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 about process-level memory limitations.
HY008	Operation was Canceled.	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. Note: This SQLSTATE applies only to .Net applications.

Restrictions

None.

Example

  /* set the cursor position in a rowset */
  cliRC = SQLSetPos(hstmt, 3, SQL_POSITION, SQL_LOCK_NO_CHANGE);