Effect of Deleted, Added, and Error Rows on Cursor Movement
Static and keyset-driven cursors sometimes detect rows added to the result set and remove rows deleted from the result set. By calling SQLGetInfo with the SQL_STATIC_CURSOR_ATTRIBUTES2 and SQL_KEYSET_CURSOR_ATTRIBUTES2 options and looking at the SQL_CA2_SENSITIVITY_ADDITIONS, SQL_CA2_SENSITIVITY_DELETIONS, and SQL_CA2_SENSITIVITY_UPDATES bitmasks, an application determines whether the cursors implemented by a particular driver do this. For drivers that can detect deleted rows and remove them, the following paragraphs describe the effects of this behavior. For drivers that can detect deleted rows but cannot remove them, deletions have no effect on cursor movements, and the following paragraphs do not apply.
If the cursor detects rows added to the result set or removes rows deleted from the result set, it appears as if it detects these changes only when it fetches data. This includes the case when SQLFetchScroll is called with FetchOrientation set to SQL_FETCH_RELATIVE and FetchOffset set to 0 to refetch the same rowset, but does not include the case when SQLSetPos is called with fOption set to SQL_REFRESH. In the latter case, the data in the rowset buffers is refreshed, but not refetched, and deleted rows are not removed from the result set. Thus, when a row is deleted from or inserted into the current rowset, the cursor does not modify the rowset buffers. Instead, it detects the change when it fetches any rowset that previously included the deleted row or now includes the inserted row.
For example:
// Fetch the next rowset.
SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
// Delete third row of the rowset. Does not modify the rowset buffers.
SQLSetPos(hstmt, 3, SQL_DELETE, SQL_LOCK_NO_CHANGE);
// The third row has a status of SQL_ROW_DELETED after this call.
SQLSetPos(hstmt, 3, SQL_REFRESH, SQL_LOCK_NO_CHANGE);
// Refetch the same rowset. The third row is removed, replaced by what
// was previously the fourth row.
SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 0);
When SQLFetchScroll returns a new rowset that has a position relative to the current rowset — that is, FetchOrientation is SQL_FETCH_NEXT, SQL_FETCH_PRIOR, or SQL_FETCH_RELATIVE — it does not include changes to the current rowset when calculating the starting position of the new rowset. However, it does include changes outside the current rowset if it is capable of detecting them. Furthermore, when SQLFetchScroll returns a new rowset that has a position independent of the current rowset — that is, FetchOrientation is SQL_FETCH_FIRST, SQL_FETCH_LAST, SQL_FETCH_ABSOLUTE, or SQL_FETCH_BOOKMARK — it includes all changes it is capable of detecting, even if they are in the current rowset.
When determining whether newly added rows are inside or outside the current rowset, a partial rowset is considered to end at the last valid row; that is, the last row for which the row status is not SQL_ROW_NOROW. For example, suppose the cursor is capable of detecting newly added rows, the current rowset is a partial rowset, the application adds new rows, and the cursor adds these rows to the end of the result set. If the application calls SQLFetchScroll with FetchOrientation set to SQL_FETCH_NEXT, SQLFetchScroll returns the rowset starting with the first newly added row.
For example, suppose the current rowset comprises rows 21 to 30, the rowset size is 10, the cursor removes rows deleted from the result set, and the cursor detects rows added to the result set. The following table shows the rows SQLFetchScroll returns in various situations.
|
Change
|
Fetch type
|
FetchOffset
|
New rowset[1] |
| Delete row 21 | NEXT | 0 | 31 to 40 |
| Delete row 31 | NEXT | 0 | 32 to 41 |
| Insert row between rows 21 and 22 | NEXT | 0 | 31 to 40 |
| Insert row between rows 30 and 31 | NEXT | 0 | Inserted row, 31 to 39 |
| Delete row 21 | PRIOR | 0 | 11 to 20 |
| Delete row 20 | PRIOR | 0 | 10 to 19 |
| Insert row between rows 21 and 22 | PRIOR | 0 | 11 to 20 |
| Insert row between rows 20 and 21 | PRIOR | 0 | 12 to 20, inserted row |
| Delete row 21 | RELATIVE | 0 | 22 to 31[2] |
| Delete row 21 | RELATIVE | 1 | 22 to 31 |
| Insert row between rows 21 and 22 | RELATIVE | 0 | 21, inserted row, 22 to 29 |
| Insert row between rows 21 and 22 | RELATIVE | 1 | 22 to 31 |
| Delete row 21 | ABSOLUTE | 21 | 22 to 31[2] |
| Delete row 22 | ABSOLUTE | 21 | 21, 23 to 31 |
| Insert row between rows 21 and 22 | ABSOLUTE | 22 | Inserted row, 22 to 29 |
[1] This column uses the row numbers before any rows were inserted or deleted.
[2] In this case, the cursor attempts to return rows starting with row 21. Because row 21 has been deleted, the first row it returns is row 22.
Error rows (that is, rows with a status of SQL_ROW_ERROR) do not affect cursor movement. For example, if the current rowset starts with row 11 and the status of row 11 is SQL_ROW_ERROR, calling SQLFetchScroll with FetchOrientation set to SQL_FETCH_RELATIVE and FetchOffset set to 5 returns the rowset starting with row 16, just as it would if the status for row 11 was SQL_SUCCESS.