Relative and absolute scrolling in Db2 ODBC applications

When you use a scrollable cursor, you call SQLFetchScroll() to move the cursor and fetch rows. The set of rows that you fetch is called a rowset.

The SQLFetchScroll() function supports relative scrolling (moving to the next row, the previous row, or forward or backward by n rows) and absolute scrolling (moving to the first row, the last row, or to row number n). The FetchOrientation parameter of the SQLFetchScroll() call determines the type of scrolling. Possible values are:
  • SQL_FETCH_NEXT
  • SQL_FETCH_PRIOR
  • SQL_FETCH_FIRST
  • SQL_FETCH_LAST
  • SQL_FETCH_ABSOLUTE
  • SQL_FETCH_RELATIVE

For SQL_FETCH_ABSOLUTE, the FetchOffset parameter determines the row to which the cursor moves. For SQL_FETCH_RELATIVE, the FetchOffset parameter determines the number of rows by which the cursor moves.

The following figure demonstrates how the cursor moves within a result set for SQLFetchScroll() calls with various FetchOffset and FetchOrientation parameter values. In this example, the rowset size is 3, and the original cursor position is at three rows from the end of the result set.

Figure 1. Example of cursor movement after SQLFetchScroll() calls
Begin figure summary. This figure shows an example of the resulting cursor position after five calls to SQLFetchScroll(). Detailed description available.

You cannot assume that the entire rowset contains data. Your application must check the rowset size after each fetch, to determine whether the rowset contains a complete set of rows. For example, suppose that you set the rowset size to 10, and you call SQLFetchScroll() using A FetchOrientation value of SQL_FETCH_ABSOLUTE and a FetchOffset value of -3. As the following figure shows, this function call sets the cursor position at three rows from the end of the result set, and attempts to fetch 10 rows.

Figure 2. Example of a SQLFetchScroll() call that returns an incomplete rowset
Begin figure summary. This figure shows an example of the result of executing a call to SQLFetchScroll(). Detailed description available.

After the fetch, only the first three rows of the rowset contain meaningful data, so your application must use the data in only those three rows.