DB2 10.5 for Linux, UNIX, and Windows

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