Retrieving data with scrollable cursors in a CLI application

Scrollable cursors allow you to move throughout a result set. You can make use of this feature when retrieving data. This topic describes how to use scrollable cursors to retrieve data.

To use scrollable cursors to retrieve data:

1. Specify the size of the rowset returned by setting the statement attribute SQL_ATTR_ROW_ARRAY_SIZE to the number of rows in the rowset. The default number of rows is 1. For example, to declare a rowset size of 35 rows, issue the following call:

     #define ROWSET_SIZE 35
     /* ... */
     rc = SQLSetStmtAttr(hstmt,
                         SQL_ATTR_ROW_ARRAY_SIZE,
                         (SQLPOINTER) ROWSET_SIZE,
                         0);

2. Specify the type of scrollable cursor to use. Using SQLSetStmtAttr(), set the SQL_ATTR_CURSOR_TYPE statement attribute to SQL_CURSOR_STATIC for a static read-only cursor or to SQL_CURSOR_KEYSET_DRIVEN for a keyset-driven cursor. For example:

     sqlrc = SQLSetStmtAttr (hstmt,
                             SQL_ATTR_CURSOR_TYPE,
                             (SQLPOINTER) SQL_CURSOR_STATIC,
                             0);

   If the type of cursor is not set, the default forward-only non-scrollable cursor will be used.

3. Set up a variable that will store the number of rows returned. Declare a variable of type SQLUINTEGER and set the SQL_ATTR_ROWS_FETCHED_PTR statement attribute to point to this variable. In the following example, rowsFetchedNb will hold the number of rows returned in the rowset after each call to SQLFetchScroll():

     /* ... */
     
     SQLUINTEGER rowsFetchedNb;
   
     /* ... */
     
     rc = SQLSetStmtAttr(hstmt,
                         SQL_ATTR_ROWS_FETCHED_PTR,
                         &rowsFetchedNb,
                         0);

4. Set up the row status array. Declare an array of type SQLUSMALLINT with the same number of rows as the size of the rowset (as determined in Step 1). Then specify the address of this array with the statement attribute SQL_ATTR_ROW_STATUS_PTR. For example:

     /* ... */
     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);

   The row status array provides additional information about each row in the rowset. After each call to SQLFetchScroll(), the array is updated. If the call to SQLFetchScroll() does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, then the contents of the row status array are undefined. Otherwise, any of the row status array values will be returned (refer to the row status array section of the SQLFetchScroll() documentation for a complete list of values).
5. Optional: If you want to use bookmarks with the scrollable cursor, set 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);

6. Issue an SQL SELECT statement.
7. Execute the SQL SELECT statement.
8. Bind the result set using either column-wise or row-wise binding.
9. Fetch a rowset of rows from the result set.
   1. Call SQLFetchScroll() to fetch a rowset of data from the result set. Position the rowset within the result set indicating the position you want the rowset to begin. Specify this position by calling SQLFetchScroll() with FetchOrientation and FetchOffset values. For example, the following call generates a rowset starting on the 11th row in the result set:

        SQLFetchScroll(hstmt,               /* Statement handle */
                       SQL_FETCH_ABSOLUTE,  /* FetchOrientation value */
                       11);                 /* Offset value */		    
        

   2. Check the row status array after each rowset is created to determine the number of rows returned, because there are instances where the rowset will not contain a complete set of rows. The application cannot assume that the entire rowset will contain data.

      For instance, consider the case where the rowset size is set to 10, and SQLFetchScroll() is called using SQL_FETCH_ABSOLUTE and FetchOffset is 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.

   3. Display or manipulate the data in the rows returned.
10. Close the cursor by calling SQLCloseCursor() or free the statement handle by calling SQLFreeHandle() with a HandleType of SQL_HANDLE_STMT.

    Freeing the statement handles is not required every time retrieval has finished. The statement handles can be freed at a later time, when the application is freeing other handles.