DB2 CLI supports Scrollable Cursors; the ability to scroll through a cursor:
There are two types of scrollable cursors supported by DB2 CLI:
After a description of each cursor type there is the section Deciding on Which Cursor Type to Use.
This type of scrollable cursor is static; once it is created no rows will be added or removed, and no values in any rows will change. The cursor is not affected by other applications accessing the same data.
The cursor is also read-only. It is not possible for the application to change any values. How the rows of the cursor are locked, if at all, is determined by the isolation level of the statement used to create the cursor. Refer to the SQL Reference for a complete discussion of isolation levels and their effect.
This type of scrollable cursor adds two features that static cursors do not have; the ability to detect changes to the underlying data, and the ability to use the cursor to make changes to the underlying data.
When a keyset-driven cursor is first opened, it stores the keys in a keyset for the life of the entire result set. This is used to determine the order and set of rows which are included in the cursor. As the cursor scrolls through the result set, it uses the keys in this keyset to retrieve the current data values for each row. Each time the cursor refetches the row it retrieves the most recent values in the datbase, not the values that existed when the cursor is first opened. For this reason no changes will be reflected in a row until the application scrolls past the row.
There are various types of changes to the underlying data that a keyset-driven cursor may or may not reflect:
Keyset-driven cursors can also be used to modify the rows in the result set with calls to either SQLBulkOperations() or SQLSetPos().
Using SQLBulkOperations() with Keyset-driven Cursors
SQLBulkOperations() can be used to add, update, delete, or fetch a set of rows. The Operation argument is used to indicate how the rows are to be updated. For more information see one of the following sections in SQLBulkOperations - Add, Update, Delete or Fetch a Set of Rows:
Using SQLSetPos() with Keyset-driven Cursors
SQLSetPos() can be used to update or delete a set of rows. The Operation argument is used to indicate how the rows are to be updated. For more information see one of the following sections in SQLSetPos - Set the Cursor Position in a Rowset:
The first decision to make is between a static cursor and a scrollable cursor. If your application does not need the additional features of a scrollable cursor then a static cursor should be used.
If a scrollable cursor is required then you have to decide between a static or keyset-driven cursor. A static cursor involves the least overhead. If the application does not need the additional features of a keyset-driven cursor then a static cursor should be used.
If the application needs to detect changes to the underlying data, or needs to add, update, or delete data from the cursor then it must use a keyset-driven cursor.
To determine the types of cursors supported by the driver and DBMS the application should call SQLGetInfo() with an InfoType of:
It is important to understand the following terms:
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:
/*...*/
#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.
/* ... */
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:
This value is not set by SQLFetch() or SQLFetchScroll().
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.
Each application that will make use of scrollable cursors must complete the following steps, in the following order:
The following additional statement attributes are required when using scrollable cursors in DB2 CLI applications. See SQLSetStmtAttr - Set Options Related to a Statement for complete details.
The default value is 1.
This value must be set or the default value of SQL_CURSOR_FORWARD_ONLY will be used.
Set the SQL_ATTR_ROWS_FETCHED_PTR statement attribute as a pointer to a SQLUINTEGER variable. This variable will then contain the number of rows returned in the rowset after each call to SQLFetchScroll().
For more information see Row Status Array.
The following example demonstrates the required calls to SQLSetStmtAttr():
/* ... */
/* Set the number of rows in the rowset */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
(SQLPOINTER) ROWSET_SIZE,
0);
CHECK_STMT(hstmt, rc);
/* Set the SQL_ATTR_ROWS_FETCHED_PTR statement attribute to */
/* point to the variable numrowsfetched: */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,
&numrowsfetched,
0);
CHECK_STMT(hstmt, rc);
/* Set a pointer to the array to use for the row status */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_ROW_STATUS_PTR,
(SQLPOINTER) row_status,
0);
CHECK_STMT(hstmt, rc);
/* Set the cursor type */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER) SQL_CURSOR_STATIC,
0);
CHECK_STMT(hstmt, rc);
/* Indicate that we will use bookmarks by setting the */
/* SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE: */
rc = SQLSetStmtAttr(
hstmt,
SQL_ATTR_USE_BOOKMARKS,
(SQLPOINTER) SQL_UB_VARIABLE,
0);
CHECK_STMT(hstmt, rc);
/* ... */
Follow the usual DB2 CLI process for executing an SQL statement and binding the result set. The application can call SQLRowCount() to determine the number of rows in the overall result set. Scrollable cursors support the use of both column wise and row wise binding. The CLI sample program sfetch.c demonstrates the use of both methods.
At this point the application can read information from the result set using the following steps:
A typical call to SQLFetchScroll() to retrieve the first rowset of data would be as follows:
SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
If you have set the SQL_ATTR_ROW_STATUS_PTR statement attribute then the row status array will also be updated for each possible row in the rowset. For more information see Row Status Array.
Once the application has finished retrieving information it should follow the usual DB2 CLI process for freeing a statement handle.
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.