Specifying the rowset returned from the result set

Before you begin to retrieve data, you need to establish the rowset that will be returned. This topic describes the steps associated with setting up the rowset.

To effectively work with a rowset, an application should perform the following steps:

1. Specify the size of the rowset returned from calls to SQLFetch() or SQLFetchScroll() 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. 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);

3. 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 SQLFetch() or SQLFetchScroll(), the array is updated. If the call to SQLFetch() or 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).
4. Position the rowset within the result set, indicating the position you want the rowset to begin. Specify this position by calling SQLFetch(), or 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 */		    

   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().

   Note: If the application can buffer data in the display and regenerate the result set to see updates, then use a forward-only cursor instead. This yields better performance for small result sets.

   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	Application generated

5. Check the rows fetched pointer after each rowset is created to determine the number of rows returned. Check the row status array for the status of each row, 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.