You can save a pointer to any row in the result set; a bookmark. The application can then use that bookmark as a relative position to retrieve a rowset of information. You can retrieve a rowset starting from the bookmarked row, or specify a positive or negative offset.
Once you have positioned the cursor to a row in a rowset using SQLSetPos(), you can obtain the bookmark value from column 0 using SQLGetData(). In most cases you will not want to bind column 0 and retrieve the bookmark value for every row, but use SQLGetData() to retrieve the bookmark value for the specific row you require.
A bookmark is only valid within the result set in which it was created. The bookmark value will be different if you select the same row from the same result set in two different cursors.
The only valid comparison is a byte-by-byte comparison between two bookmark values obtained from the same result set. If they are the same then they both point to the same row. Any other mathematical calculations or comparisons between bookmarks will not provide any useful information. This includes comparing bookmark values within a result set, and between result sets.
To make use of bookmarks the following steps must be followed in addition to the steps described in "Typical Scrollable Cursors Application".
To use bookmarks you must set the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE. This is in addition to the other statement attributes required for scrollable cursors.
ODBC defines both variable and fixed-length bookmarks. DB2 CLI only supports the newer, variable-length bookmarks.
The application must execute the SQL SELECT statement and use SQLFetchScroll() to retrieve a rowset with the desired row. SQLSetPos() is then used to position the cursor within the rowset. Finally, the bookmark value is obtained from column 0 using SQLGetData() and stored in a variable.
The statement attribute SQL_ATTR_FETCH_BOOKMARK_PTR is used to store the location for the next call to SQLFetchScroll() that uses a bookmark.
Once you have the bookmark value using SQLGetData() (the variable abookmark below), call SQLSetStmtAttr() as follows:
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_FETCH_BOOKMARK_PTR,
(SQLPOINTER) abookmark,
0);
Once the bookmark value is stored, the application can continue to use SQLFetchScroll() to retrieve data from the result set.
The application can then move throughout the result set, but still retrieve a rowset based on the location of the bookmarked row at any point before the cursor is closed.
The following call to SQLFetchScroll() will retrieve a rowset starting with the bookmarked row:
rc = SQLFetchScroll(hstmt, SQL_FETCH_BOOKMARK, 0);The value 0 specifies the offset. You would specify -3 to begin the rowset 3 rows before the bookmarked row, or specify 4 to begin 4 rows after.
Note that the variable used to store the bookmark value is not specified in the SQLFetchScroll() call. It was set in the previous step using the statement attribute SQL_ATTR_FETCH_BOOKMARK_PTR.