Call Level Interface Guide and Reference

Typical Scrollable Cursors Application

Each application that will make use of scrollable cursors must complete the following steps, in the following order:

1. Set Up the Environment

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.

Setting the Rowset size

Set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows that you want returned from each call to SQLFetchScroll().

The default value is 1.

Type of scrollable cursor

DB2 CLI only supports static, read-only cursors. Set the SQL_ATTR_CURSOR_TYPE statement attribute to SQL_CURSOR_STATIC. ODBC defines other scrollable cursors types, but they cannot be used with DB2 CLI.

This value must be set or the default value of SQL_CURSOR_FORWARD_ONLY will be used.

Location to store number of rows returned

The application needs a way to determine how many rows were returned in the rowset from each call to SQLFetchScroll(). The number of rows returned in the rowset can at times be less than the maximum size of the rowset which was set using SQL_ATTR_ROW_ARRAY_SIZE.

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

Array to use for the row status

Set the SQL_ATTR_ROW_STATUS_PTR statement attribute as a pointer to the SQLUSMALLINT array that is used to store the row status. This array will then be updated after each call to SQLFetchScroll().

For more information see "Row Status Array".

WIll bookmarks be used?

If you plan on using bookmarks in your scrollable cursor then you must set the SQL_ATTR_USE_BOOKMARKS statement attribute to SQL_UB_VARIABLE.

The following example demonstrates the required calls to SQLSetStmtAttr():

/* CLI Sample: sfetch.c */
/* ... */
 
    /* 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);
 
/* ... */

2. Execute SQL SELECT Statement and Bind the Results

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.

3. Fetch a Rowset of Rows at a time from the Result Set

At this point the application can read information from the result set using the following steps:

1. Use SQLFetchScroll() to fetch a rowset of data from the result set. The FetchOrientation argument is used to indicate the location of the rowset in the result set. See "Specifying the Rowset Returned from the Result Set" for more details.

   A typical call to SQLFetchScroll() to retrieve the first rowset of data would be as follows:

      SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0);
   

2. Calculate the number of rows returned in the result set. This value is set automatically after each call to SQLFetchScroll(). In the example above we set the statement attribute SQL_ATTR_ROWS_FETCHED_PTR to the variable numrowsfetched which will therefore contain the number of rows fetched after each SQLFetchScroll() call.

   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".

3. Display or manipulate the data in the rows returned.

4. Free the Statement which then Closes the Result Set

Once the application has finished retrieving information it should follow the usual DB2 CLI process for freeing a statement handle.

[ DB2 List of Books | Search the DB2 Books ]