The position of the rowset within the result set is specified in the call to SQLFetchScroll(). For example, the following call would generate a rowset starting on the 11th row in the result set (step 5 in Figure 7):
SQLFetchScroll(hstmt, /* Statement handle */
SQL_FETCH_ABSOLUTE, /* FetchOrientation value */
11); /* Offset value */
Scroll bar operations of a screen-based application can be mapped directly
to the positioning of a rowset. By setting the rowset size to the number of
lines displayed on the screen, the application can map the movement of the
scroll bar to calls to SQLFetchScroll().
| Rowset Retrieved | FetchOrientation Value | Scroll bar |
|---|---|---|
| First rowset | SQL_FETCH_FIRST | Home: Scroll bar at the top |
| Last rowset | SQL_FETCH_LAST | End: Scroll bar at the bottom |
| Next rowset | SQL_FETCH_NEXT (same as calling SQLFetch()) | Page Down |
| Previous rowset | SQL_FETCH_PRIOR | Page Up |
| Rowset starting on next row | SQL_FETCH_RELATIVE with FetchOffset set to 1 | Line Down |
| Rowset starting on previous row | SQL_FETCH_RELATIVE with FetchOffset set to -1 | Line Up |
| Rowset starting on a specific row | SQL_FETCH_ABSOLUTE with FetchOffset set to an offset from the start (a positive value) or the end (a negative value) of the result set | Application generated |
| Rowset starting on a previously bookmarked row | SQL_FETCH_BOOKMARK with FetchOffset set to a positive or negative offset from the bookmarked row (see "Using Bookmarks with Scrollable Cursors" for more information) | Application generated |
The following figure demonstrates a number of calls to SQLFetchScroll() using various FetchOrientation values. The result set includes all of the rows (from 1 to n), and the rowset size is 3. The order of the calls is indicated on the left, and the FetchOrientation values are indicated on the right.
Figure 7. Example of Retrieving Rowsets
|
For more details see Cursor Positioning Rules in SQLFetchScroll().
The statement attribute SQL_ATTR_ROW_ARRAY_SIZE is used to declare the number of rows in the rowset. For example, to declare a rowset size of 35 rows, the following call would be used:
/* CLI Sample: sfetch.c */
/*...*/
#define ROWSET_SIZE 35
/*...*/
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER) ROWSET_SIZE,
0);
The application cannot assume that the entire rowset will contain data. It must check the rowset size after each rowset is created because there are instances where the rowset will not contain a complete set of rows. For instance, consider the case where the rowset size is set to 10, and SQLFetchScroll() is called using SQL_FETCH_ABSOLUTE and FetchOffset set to -3. This will attempt to return 10 rows starting 3 rows from the end of the result set. Only the first three rows of the rowset will contain meaningful data, however, and the application must ignore the rest of the rows.
Figure 8. Partial Rowset Example
 |
See Setting the Rowset size for more information on using the statement attribute SQL_ATTR_ROW_ARRAY_SIZE.
The row status array provides additional information about each row in the rowset. After each call to SQLFetchScroll() the array is updated. The application must declare an array (of type SQLUSMALLINT) with the same number of rows as the size of the rowset (the statement attribute SQL_ATTR_ROW_ARRAY_SIZE). The address of this array is then specified with the statement attribute SQL_ATTR_ROW_STATUS_PTR.
/* CLI Sample: sfetch.c */
/* ... */
SQLUSMALLINT row_status[ROWSET_SIZE];
/* ... */
/* Set a pointer to the array to use for the row status */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_ROW_STATUS_PTR,
(SQLPOINTER) row_status,
0);
/* ... */
If the call to SQLFetchScroll() does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO the the contents of the row status buffer is undefined, otherwise the following values are returned:
ODBC defines the following values as well, but DB2 CLI does not return them:
In Figure 8, the first 3 rows of the row status array would contain the value SQL_ROW_SUCCESS; the remaining 7 rows would contain SQL_ROW_NOROW.