Retrieving data with bookmarks in a CLI application

Bookmarks, available only when scrollable cursors are used, allow you to save a reference to any row in a result set. You can take advantage of this feature when retrieving data. This topic describes how to retrieve data using bookmarks.

To use bookmarks with scrollable cursors to retrieve data:

1. Indicate that bookmarks will be used (if not already done so) by setting the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE.
   For example:

     sqlrc = SQLSetStmtAttr (hstmt,
                             SQL_ATTR_USE_BOOKMARKS,
                             (SQLPOINTER) SQL_UB_VARIABLE,
                             0);
   

2. Get the bookmark value from the required row in the rowset after executing the SELECT statement and retrieving the rowset using SQLFetchScroll(). Do this by calling SQLSetPos() to position the cursor within the rowset. Then call SQLGetData() to retrieve the bookmark value.
   For example:

       sqlrc = SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 15);
       /* ... */
       sqlrc = SQLSetPos(hstmt, 3, SQL_POSITION, SQL_LOCK_NO_CHANGE);
       /* ... */
       sqlrc = SQLGetData(hstmt, 0, SQL_C_LONG, bookmark.val, 4,
                          &bookmark.ind);

   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.

3. Store the bookmark location for the next call to SQLFetchScroll(). Set the SQL_ATTR_FETCH_BOOKMARK statement attribute to the variable that contains the bookmark value. For example, bookmark.val stores the bookmark value, so call SQLSetStmtAttr() as follows:

       sqlrc = SQLSetStmtAttr(hstmt,
                              SQL_ATTR_FETCH_BOOKMARK_PTR,
                              (SQLPOINTER) bookmark.val,
                              0);

4. Retrieve a rowset based on the bookmark. 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() retrieves a rowset starting from the bookmarked row:

       sqlrc = 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. For example, the following call from retrieves a rowset 4 rows after the bookmarked row:

       sqlrc = SQLFetchScroll(hstmt, SQL_FETCH_BOOKMARK, 4);

   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.