SQLSetPos

SQLSetPos

This function specifies the cursor position in a rowset.

Syntax

SQLRETURN  SQLSetPos (
    SQLHSTMT    stmt,
    SQLSETPOSIROW   rowNumber,
    SQLUSMALLINT    operation,
    SQLUSMALLINT    lockType);

Arguments

Data Type	Argument	In/Output	Description
SQLHSTMT	stmt	Input	Statement handle
SQLSETPOSIROW	rowNumber	Input	Position of the row in the rowset on which the operation specified by the operation argument is to be performed. Starts from 1. For 0, the operation applies to every row of the rowset.
SQLUSMALLINT	operation	Input	Operation to be performed. One of the following can be used: - SQL_POSITION - SQL_REFRESH - SQL_UPDATE - SQL_DELETE
SQLUSMALLINT	lockType	Input	Lock type. Currently not supported

Return Values

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_NEED_DATA
SQL_STILL_EXECUTING
SQL_INVALID_HANDLE
SQL_ERROR 

Description

SQLSetPos specifies the cursor position in the rowset, and enables an application to refresh, update or delete data of that position. rowNumber specifies the number of the row in the rowset on which the operation is to be performed. If rowNumber is 1, the operation is performed on the first row in the rowset; if rowNumber is 0, the operation is performed on every row in the rowset. If operation is SQL_POSITION, however, rowNumber cannot be 0.

A cursor position is required for the following operations:

• Calls to SQLGetData
• Calls to SQLSetPos with the SQL_DELETE, SQL_REFRESH, or SQL_UPDATE option

For the initial fetch after executing a SELECT statement, the cursor position in the rowset is 1.

The following options are provided for the operation argument of the SQLSetPos function:

Option	Description
SQL_POSITION	The driver positions the cursor on the row specified by rowNumber.
SQL_REFRESH	The driver positions the cursor on the row specified by rowNumber, and refreshes data in the rowset buffer for that row. If 0 is specified for rowNumber, data of every row in the rowset is refreshed. If the driver detects a HOLE, SQL_ROW_DELETED is returned as the row status value. For SENSITIVE type cursors, the latest data from the database is retrieved; for non-SENSITIVE type cursors, existing data from the cache is retrieved.
SQL_UPDATE	The driver positions the cursor on the row specified by rowNumber, and updates data of the database with the data of that row in the rowset buffer. If 0 is specified for rowNumber, the database is updated with all of the data in the rowset. If the length/indicator argument of SQLBindCol is set to SQL_COLUMN_IGNORE, the column is not updated. If a row which is specified as SQL_ROW_IGNORE for the SQL_ATTR_ROW_OPERATION_PTR statement attribute exists in the row operation array, that row is ignored and operations are performed on the next row.
SQL_DELETE	The driver positions the cursor on the row specified by rowNumber, and deletes the row. If rowNumber is set to 0, every row in the rowset is deleted. If a row which is specified as SQL_ROW_IGNORE for the SQL_ATTR_ROW_OPERATION_PTR statement attribute exists in the row operation array, that row is ignored and operations are performed on the next row.

Using SQLSetPos

When calling SQLSetPos, write the program in the following order:

1. Call SQLBindCol to bind buffers

2. Execute a SELECT query statement with SQLExecDirect, etc.

3. To call SQLSetPos with the SQL_UPDATE option,

   • Change the data of the bound buffer to the desired value.

   • To ignore a certain column, call SQLBindCol again with the length/indicator set to SQL_COLUMN_IGNORE.

   • To exclude a certain row from the operation, set the SQL_ATTR_ROW_OPERATION statement attribute and its corresponding array element to SQL_ROW_IGNORE

4. Call SQLSetPos

   • If the SQL_ATTR_ROW_STATUS_PTR statement attribute is set to a status array, the operational result of each row (whether or not it is SQL_ROW_DELETED) can be checked.

Diagnosis

SQLSTATE	Description	Comments
HY000	General error
HY001	Memory allocation error	Memory required for Altibase CLI driver to process and complete the function cannot be allocated.
08S01	Communication channel error(data transmission failure)	Communication channel error occurred before function process between Altibase CLI driver and DB completed.
08003		stmt was released or connection was not in a connected state.
HY010	Function sequence error	Given stmt cannot process the function. Asynchronous operations are unsupported.
24519	Hole detected but no indicator variable provided
01S01	Error(s) in row(s)	The rowNumber value was 0, and an error occurred in one or more rows during operation.
HY107	Row value out of range	The value specified for rowNumber is larger than the number of rows in the rowset.
HY109	Invalid cursor position
01001	Cursor operation conflict
HY024	Invalid array size
21S02	Type and number of column in derived table does not match column list
HY092	Invalid attribute/option
24000	Invalid cursor state

Related Functions

SQLBindCol
SQLBulkOperations
SQLCancel
SQLFetchScroll
SQLGetDescField
SQLGetDescRec
SQLSetDescField
SQLSetDescRec
SQLSetStmtAttr