Cursor positioning rules for SQLFetchScroll() (CLI)

You can use the SQLFetchScroll() function to fetch the specified rowset of data from the result-set and return data for all bound columns. You can specify rowsets at an absolute or relative position or by using a bookmark.
The following sections describe the exact rules for each value of FetchOrientation. These rules use the following notation:
FetchOrientation
Meaning
Before start
The block cursor is positioned before the start of the result set. If the first row of the new rowset is before the start of the result set, SQLFetchScroll() returns SQL_NO_DATA.
After end
The block cursor is positioned after the end of the result set. If the first row of the new rowset is after the end of the result set, SQLFetchScroll() returns SQL_NO_DATA.
CurrRowsetStart
The number of the first row in the current rowset.
LastResultRow
The number of the last row in the result set.
RowsetSize
The rowset size.
FetchOffset
The value of the FetchOffset argument.
BookmarkRow
The row corresponding to the bookmark specified by the SQL_ATTR_FETCH_BOOKMARK_PTR statement attribute.

SQL_FETCH_NEXT rules:

Table 1. SQL_FETCH_NEXT rules:
Condition First row of new rowset
Before start 1
CurrRowsetStart + RowsetSize <= LastResultRow CurrRowsetStart + RowsetSize
CurrRowsetStart + RowsetSize > LastResultRow After end
After end After end

SQL_FETCH_PRIOR rules:

Table 2. SQL_FETCH_PRIOR rules:
Condition First row of new rowset
Before start Before start
CurrRowsetStart = 1 Before start
1 < CurrRowsetStart <= RowsetSize 1 a
CurrRowsetStart > RowsetSize CurrRowsetStart - RowsetSize
After end AND LastResultRow < RowsetSize 1 a
After end AND LastResultRow >= RowsetSize LastResultRow - RowsetSize + 1
  • a SQLFetchScroll() returns SQLSTATE 01S06 (Attempt to fetch before the result set returned the first rowset.) and SQL_SUCCESS_WITH_INFO.

SQL_FETCH_RELATIVE rules:

Table 3. SQL_FETCH_RELATIVE rules:
Condition First row of new rowset
(Before start AND FetchOffset > 0) OR (After end AND FetchOffset 0) -- a
Before start AND FetchOffset <= 0 Before start
CurrRowsetStart = 1 AND FetchOffset < 0 Before start
CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset <1 AND |FetchOffset| > RowsetSize Before start
CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset <1 AND |FetchOffset| <= RowsetSize 1 b
1 <= CurrRowsetStart + FetchOffset <= LastResultRow CurrRowsetStart + FetchOffset
CurrRowsetStart + FetchOffset > LastResultRow After end
After end AND FetchOffset >= 0 After end
  • a SQLFetchScroll() returns the same rowset as if it was called with FetchOrientation set to SQL_FETCH_ABSOLUTE. For more information, see the SQL_FETCH_ABSOLUTE section.
  • b SQLFetchScroll() returns SQLSTATE 01S06 (Attempt to fetch before the result set returned the first rowset.) and SQL_SUCCESS_WITH_INFO.

SQL_FETCH_ABSOLUTE rules:

Table 4. SQL_FETCH_ABSOLUTE rules:
Condition First row of new rowset
FetchOffset <0 AND |FetchOffset| <= LastResultRow LastResultRow + FetchOffset + 1
FetchOffset <0 AND |FetchOffset| > LastResultRow AND |FetchOffset| > RowsetSize Before start
FetchOffset <0 AND |FetchOffset| > LastResultRow AND |FetchOffset| <= RowsetSize 1 a
FetchOffset = 0 Before start
1 <= FetchOffset <= LastResultRow FetchOffset
FetchOffset > LastResultRow After end
  • a SQLFetchScroll() returns SQLSTATE 01S06 (Attempt to fetch before the result set returned the first rowset.) and SQL_SUCCESS_WITH_INFO.

SQL_FETCH_FIRST rules:

Table 5. SQL_FETCH_FIRST rules:
Condition First row of new rowset
Any 1

SQL_FETCH_LAST rules:

Table 6. SQL_FETCH_LAST rules:
Condition First row of new rowset
RowsetSize = LastResultRow LastResultRow - RowsetSize + 1
RowsetSize > LastResultRow 1

SQL_FETCH_BOOKMARK rules:

Table 7. SQL_FETCH_BOOKMARK rules:
Condition First row of new rowset
BookmarkRow + FetchOffset <1 Before start
1 <= BookmarkRow + FetchOffset <= LastResultRow BookmarkRow +FetchOffset
BookmarkRow + FetchOffset > LastResultRow After end