SQLSetPos - Set the cursor position in a rowset

SQLSetPos() sets the cursor position in a rowset. Once the cursor is set, the application can refresh, update, and delete data in the rows.

ODBC specifications for `SQLSetPos()`

Table 1. `SQLSetPos()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
1.0	Yes	Yes

Syntax

SQLRETURN   SQLSetPos (
               SQLHSTMT          StatementHandle,
               SQLSETPOSIROW     RowNumber,
               SQLUSMALLINT      Operation,
               SQLUSMALLINT      LockType);

Function arguments

Table 2. SQLSetPos arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLUSMALLINT	RowNumber	input	Position in the rowset of the row on which the operation that is specified by Operation is performed. If RowNumber is 0, the operation applies to every row in the rowset.
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 Db2 ODBC also supports: SQL_ADD Although Db2 ODBC supports SQL_ADD in `SQLSetPos()` calls, this function is deprecated. Use `SQLBulkOperations()` with an Operation value of SQL_ADD instead.
SQLUSMALLINT	LockType	input	Specifies how to lock the row after performing the operation that is specified in the Operation argument. Db2 ODBC supports SQL_LOCK_NO_CHANGE. ODBC also specifies the following operations, which Db2 ODBC does not support: SQL_LOCK_EXCLUSIVE SQL_LOCK_UNLOCK

Usage

RowNumber argument: The RowNumber argument specifies the number of the row in the rowset on which to perform the operation that is specified by the Operation argument. If RowNumber is 0, the operation applies to every row in the rowset. RowNumber must be a value between 0 and the number of rows in the rowset, inclusive.

In the C language, arrays are 0-based, but the RowNumber argument 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, the application 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_FORWARD_ONLY_CURSOR_ATTRIBUTES1
SQL_DYNAMIC_CURSOR_ATTRIBUTES1
SQL_STATIC_CURSOR_ATTRIBUTES1

Operation can have one of the following values:

SQL_POSITION

Db2 ODBC positions the cursor on the row specified by RowNumber.

The contents of the row status array that is pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute are unchanged. The contents of the row operation array that is pointed to by the SQL_ATTR_ROW_OPERATION_PTR statement attribute are ignored.

SQL_REFRESH

Db2 ODBC positions the cursor on the row that is specified by RowNumber, and refreshes data in the rowset buffers for that row. For more information about how Db2 ODBC returns data in the rowset buffers, see the descriptions of row-wise and column-wise binding. Db2 ODBC supports only static cursors on SQL_REFRESH.

SQLSetPos() with an Operation value of SQL_REFRESH updates the status and content of the rows within the current fetched rowset. The data in the buffers is refreshed, but not refetched, so the membership in the rowset is fixed.

The contents of the row status array that is pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute are also refreshed.

SQL_UPDATE

Db2 ODBC positions the cursor on the row that is specified by RowNumber, and updates the underlying row of data with the values in the rowset buffers (the rgbValue argument in SQLBindCol()). SQLSetPos() retrieves the lengths of the data from the length or indicator buffers (the pcbValue argument in SQLBindCol()). If the length of any column is SQL_COLUMN_IGNORE, the column is not updated.

After the row is updated, 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 that is 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.

SQL_DELETE

Db2 ODBC positions the cursor on the row that is specified by RowNumber, and deletes the underlying row of data.

The corresponding element of the row status array, which is pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute, is changed to SQL_ROW_DELETED.

The row operation array that is 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.

SQL_ADD

Db2 ODBC specifies the SQL_ADD value for backward compatibility only.

Recommendation: Instead of calling SQLPos() with the SQL_ADD value, use the ODBC 3.0 function 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 support only 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 ODBC supports only 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 3. 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 the locking that is required by the current concurrency and transaction isolation levels.
SQL_LOCK_EXCLUSIVE	Not supported by Db2 ODBC. Locks the row exclusively.
SQL_LOCK_UNLOCK	Not supported by Db2 ODBC. Unlocks the row.

Status and operation arrays

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

The row status array contains status values for each row of data in the rowset. Status values are set in this array after a call to SQLFetch(), SQLFetchScroll(), or SQLSetPos().
The row operation array contains a value for each row in the rowset, which indicates whether a call to SQLSetPos() for a bulk operation is ignored or performed. Each element in the array is SQL_ROW_PROCEED (the default) or SQL_ROW_IGNORE. The SQL_ATTR_ROW_OPERATION_PTR statement attribute points to the row operation array.

The number of elements in the status and operation arrays should be equal to the number of rows in the rowset, as defined by the SQL_ATTR_ROW_ARRAY_SIZE statement attribute.

Return codes

After you call SQLPos(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_NEED_DATA
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

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

Table 4. 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 that was 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 when the operation that is specified with the Operation argument was performed. SQL_SUCCESS_WITH_INFO is returned if an error occurs at least one, but not all, rows of a multirow operation. 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 that was 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 that was specified by fCType in the call to `SQLBindCol()`.
21S02	Degrees of derived table does not match column list.	The argument Operation was SQL_UPDATE, and no columns could be updated because all columns were either unbound, read-only, or the value in the bound length or 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.	One of the following conditions occurred: The argument Operation was SQL_UPDATE. 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. The numeric value for one or more bound columns could not be returned because significant digits were lost.
22008	Invalid datetime format or datetime field overflow.	One of the following conditions occurred: The Operation argument was SQL_UPDATE. A datetime arithmetic operation on data that was 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 that was outside the permissible range of values for the field, or was invalid based on the natural rules for datetime values for the Gregorian calendar. Alternatively, the assignment of a numeric value to a column in the result set caused the whole part of the number to be truncated. The Operation argument was SQL_REFRESH. A datetime arithmetic operation on data that was retrieved from the result set resulted in a datetime field (the year, month, day, hour, minute, or second field) of the result that was outside the permissible range of values for the field, or was invalid based on the natural rules for datetime values for the Gregorian calendar.
HY000	General error.	An error occurred for which there was no specific SQLSTATE. The error message that was returned by `SQLGetDiagRec()` in the *MessageText buffer describes the error and its cause.
HY001	Memory allocation failure.	Db2 ODBC was unable to allocate memory required to support execution or completion of the function. Process-level memory might have been exhausted for the application process. Consult the operating system configuration for information on process-level memory limitations.
HY010	Function sequence error.	One of the following conditions occurred: The specified StatementHandle was not in an executed state. The function was called without a previous call of `SQLExecDirect()`, `SQLExecute()`, or a catalog function. `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. `SQLSetPos()` was called 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.	The 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.	One of the following conditions occurred: The Operation argument was SQL_ADD or SQL_UPDATE, 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 or SQL_UPDATE, 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.
HY107	Row value out of range.	One of the following conditions occurred: The cursor that was 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 that was identified by the RowNumber argument was deleted or had not been fetched. The Operation argument was SQL_POSITION, and the RowNumber argument was 0.
HYC00	Driver not capable.	Db2 ODBC or the data source does not support the operation that was requested in the Operation argument or the LockType argument.

Restrictions

SQL_REFRESH for dynamic scrollable cursors is not supported by Db2 ODBC.

Example


rc = SQLSetPos(
  hstmt,
  1,                   /* Position at the first row of the rowset. */
  SQL_POSITION,
  SQL_LOCK_NO_CHANGE); /* Do not change the lock state. */