To fetch and update rowsetsODBC supports two methods for performing positioned updates in a cursor:
The most common approach is to use SQLSetPos, which has the following options:
SQLSetPos can be used with any statement result set when the statement handle cursor attributes are set to use server cursors. The result set columns must be bound to program variables. Once the application has fetched a row it calls SQLSetPos(SQL_POSTION) to position the cursor on the row. The application could then call SQLSetPos(SQL_DELETE) to delete the current row, or it can move new data values into the bound program variables and call SQLSetPos(SQL_UPDATE) to update the current row.
Applications can update or delete any row in the rowset with SQLSetPos. Calling SQLSetPos is a convenient alternative to constructing and executing an SQL statement. SQLSetPos operates on the current rowset and can be used only after a call to SQLFetchScroll.
Rowset size is set by a call to SQLSetStmtAttr with an attribute argument of SQL_ATTR_ROW_ARRAY_SIZE. SQLSetPos uses a new rowset size, however, only after a call to SQLFetch or SQLFetchScroll. For example, if the rowset size is changed, then SQLSetPos is called, and then SQLFetch or SQLFetchScroll is called; the call to SQLSetPos uses the old rowset size, but SQLFetch or SQLFetchScroll uses the new rowset size.
The first row in the rowset is row number 1. The RowNumber argument in SQLSetPos must identify a row in the rowset; that is, its value must be in the range between 1 and the number of rows that were most recently fetched (which may be less than the rowset size). If RowNumber is 0, the operation applies to every row in the rowset.
The delete operation of SQLSetPos makes the data source delete one or more selected rows of a table. To delete rows with SQLSetPos, the application calls SQLSetPos with Operation set to SQL_DELETE and RowNumber set to the number of the row to delete. If RowNumber is 0, all rows in the rowset are deleted.
After SQLSetPos returns, the deleted row is the current row, and its status is SQL_ROW_DELETED. The row cannot be used in any further positioned operations, such as calls to SQLGetData or SQLSetPos.
When deleting all rows of the rowset (RowNumber is equal to 0), the application can prevent the driver from deleting certain rows by using the row operation array in the same way as for the update operation of SQLSetPos.
Every row that is deleted should be a row that exists in the result set. If the application buffers were filled by fetching and if a row status array has been maintained, its values at each of these row positions should not be SQL_ROW_DELETED, SQL_ROW_ERROR, or SQL_ROW_NOROW.
Positioned updates can also be done using the WHERE CURRENT OF clause on UPDATE, DELETE, and INSERT statements. WHERE CURRENT OF requires a cursor name, which ODBC will generate when the SQLGetCursorName function is called, or which you can specify by calling SQLSetCursorName. The general steps to perform a WHERE CURRENT OF update in an ODBC application are:
As an alternative, you could call SQLGetCursorName after executing the SELECT statement instead of calling SQLSetCursorName before executing the SELECT statement. SQLGetCursorName returns a default cursor name assigned by ODBC if you do not set a cursor name using SQLSetCursorName.
SQLSetPos is preferred over WHERE CURRENT OF when using server cursors. If you are using a static, updatable cursor with the ODBC cursor library, the cursor library implements WHERE CURRENT OF updates by adding a WHERE clause with the key values for the underlying table. This can cause unintended updates if the keys in the table are not unique.
To fetch and update rowsets
| SQLFetchScroll | SQLSetStmtAttr |
| SQLGetData | SQLGetCursorName |
| Changing Rows with Positioned Operations |