Steps for retrieving data with scrollable cursors in a Db2 ODBC application

To use a scrollable cursor in a Db2 ODBC application, you must set the rowset size, specify the scrollable cursor type, set up areas to contain the results of data retrieval, bind the application data, determine the result set size, fetch data, move the cursor multiple times, and close the cursor.

Procedure

To retrieve data with scrollable cursors:

Call SQLSetStmtAttr() to specify the size of the rowset that is returned from the result set.
Set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows that are returned for each fetch operation. The default rowset size is 1.
For example, this call declares a rowset size of 35 rows:
```
#define ROWSET_SIZE 35 
/* ... */ 
rc = SQLSetStmtAttr(hstmt, 
                    SQL_ATTR_ROW_ARRAY_SIZE, 
                    (SQLPOINTER) ROWSET_SIZE, 
                    0); 
```
Call SQLSetStmtAttr() to specify whether to use a static or dynamic scrollable cursor. Set the SQL_ATTR_CURSOR_TYPE statement attribute to SQL_CURSOR_STATIC for a static read-only cursor, or to SQL_CURSOR_DYNAMIC for a dynamic cursor.
The default cursor type is SQL_CURSOR_FORWARD_ONLY.
For example, this call specifies a static cursor:
```
rc = SQLSetStmtAttr(hstmt, 
                    SQL_ATTR_CURSOR_TYPE, 
                    (SQLPOINTER) SQL_CURSOR_STATIC, 
                    0); 
```
Declare a storage area of type SQLUINTEGER to contain the number or rows that are returned in the rowset from each call to SQLFetchScroll(). Call SQLSetStmtAttr() to specify the location of that storage area.
Set the SQL_ATTR_ROWS_FETCHED_PTR statement attribute as a pointer to the storage area.
For example, this code sets up the rowsFetchedNb variable as the storage area for the number of rows that are returned:
```
/* ... */ 
SQLUINTEGER rowsFetchedNb; 
/* ... */ 
rc = SQLSetStmtAttr(hstmt, 
                    SQL_ATTR_ROWS_FETCHED_PTR, 
                    &rowsFetchedNb, 
                    0); 
```
Declare a storage area that is an array of SQLUSMALLINT values, to contain the row status array. Call SQLSetStmtAttr() to specify the location of the row status array.
Set the SQL_ATTR_ROW_STATUS_PTR statement attribute as a pointer to the row status array.
The size of the row status array must be equal to the rowset size that is defined with the SQL_ATTR_ROW_ARRAY_SIZE statement attribute.
For example, this code sets up array row_status as a row status array:
```
/* ... */ 
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); 
```
Execute a SQL SELECT statement that defines the result set. Bind the results using column-wise or row-wise binding.
This step is the same as for non-scrollable cursors.
Call SQLRowCount() to determine the number of rows in the result set.
Fetch rowsets of rows from the result set. To do that:
1. Call SQLFetchScroll() to fetch a rowset of data from the result set.
  Set the FetchOrientation and FetchOffset arguments to indicate the location of the rowset in the result set.
2. Determine the number of rows that were returned in the result set.
  Db2 ODBC sets this value after each call to SQLFetchScroll(), in the location to which statement attribute SQL_ATTR_ROWS_FETCHED_PTR points, and in the row status array.
  For example, this call sets the cursor at the eleventh row of the result set, and fetches a rowset:
```
rc = SQLFetchScroll(hstmt,              /* Statement handle */ 
                    SQL_FETCH_ABSOLUTE, /* FetchOrientation value */ 
                    11);                /* Offset value */ 
```
3. Display or manipulate the retrieved rows.
4. Repeat the previous substeps in this step to scroll and fetch more rowsets.
After you have retrieved all rowsets, close the cursor by calling SQLCloseCursor(), or free the statement handle by calling SQLFreeHandle() with a HandleType value of SQL_HANDLE_STMT. When you free the statement handles, the result set closes.