InformIT: Visual C++ 6 Unleashed > Retrieving More Than One Row at a Time

Table of Contents

Retrieving More Than One Row at a Time

Earlier in this chapter, you saw how to access a result set one row at a time by calling SQLFetch() to access each row. You can also fetch a group of rows, or rowset, at the same time by using the SQLFetchScroll() function instead of SQLFetch(). This is done by using cursors. In this section, you will be looking at block cursors, which enable you to retrieve groups of rows at a time, as well as scrollable cursors, which allow additional navigation within the result set. (In most cases, a scrollable cursor is a block cursor with additional functionality.)

Earlier, when you were retrieving one row at a time, you were also using a cursor, but it was a special single-row, forward-only case of a nonscrollable block cursor.

Block Cursors

Earlier in this chapter, you saw how to retrieve a single row. As you might guess, this is generally not the most efficient way to retrieve large amounts of data—in addition to the overhead of a greater number of function calls, there can be a great deal more network overhead involved in requesting rows individually. To make your apps more efficient, you can use a block cursor, which retrieves a block of rows, or rowset, in a single request. You will see how to use block cursors soon.

Scrollable Cursors

With block cursors and forward-only cursors, to return to a row in a previous rowset, you must close the cursor and start again from the top. Scrollable cursors allow your application to more easily navigate in any direction in a rowset. However, this additional functionality can introduce some significant additional overhead. In general, if you simply need to retrieve all the data in a rowset for report generation, block cursors are best. If you need to provide more flexible scrolling ability, such as in interactive applications, go ahead and use scrollable cursors.

The ODBC Cursor Library

For database drivers that support cursors, the ODBC SDK provides a cursor library (ODBCCR32.DLL) that implements blocks, static cursors, and positioned updates and deletes. The cursor library sits between the application and the driver that actually connects to the data source. The cursor library may be distributed with your applications and is enabled by setting the SQL_ATTR_ODBC_CURSORS attribute on a connection before connecting to the data source.

Using Block Cursors

Using block cursors to retrieve data from a result set is similar to retrieving one row at a time, although there are a few extra things you must do. To use block cursors, your application should do the following:

Set the number of rows to be retrieved in each rowset.
Bind columns to memory locations.
Call SQLFetchScroll() to retrieve each rowset.

Setting the Size of a Rowset

The size of the rowset returned by calls to SQLFetchScroll() is set by calling SQLSetStmtOption() to set the SQL_ATTR_ROW_ARRAY_SIZE option to the number of rows you want to receive in a rowset. By setting this attribute to a value greater than one, you tell ODBC to use a block cursor.

Binding Columns for a Rowset

If you are using SQLFetchScroll() to retrieve more than one row at a time, you can set up column-wise binding, as you saw with SQLFetch(), or you can use row-wise binding, which binds rows to a structure holding all the columns.

Column-Wise Binding

By default, a statement handle is set to use column-wise binding, as you saw earlier. The one difference when binding columns for use with block cursors, is that instead of defining a single variable to receive data for a single row and column, the buffer pointer you specify in SQLBindCol() should point to an array of buffers that is long enough to receive a whole rowset worth of data for that column.

Row-Wise Binding

In addition, it is often useful to bind a rowset to memory in a row-wise fashion when using block cursors. This presents the data to your application as a single array of a structure that contains all the columns of the rowset, rather than a separate variable for each column. Row-wise binding may also provide greater efficiency, depending on the driver you are using.

To implement row-wise binding, you must do the following:

Define a structure to hold a single row's data. This structure should have a field for each column to be bound, as well as a field containing the length of each column.
Allocate an array of this structure. The allocated array should contain as many elements as the size of your rowset. You should also include an additional element if your application will append new rows of data or search for key values.
Enable row-wise binding for the statement handle. This is done by calling SQLSetStmtAttr() twice, once with Attribute set to SQL_ATTR_ROW_BIND_TYPE and ValuePtr set to the size of the structure you have defined to receive the data, and once with Attribute set to SQL_ATTR_ROW_ARRAY_SIZE and ValuePtr set the number of elements in the array you will be using.
Call SQLBindCol() for each column to be bound. When calling SQLBindCol(), the TargetValuePtr parameter should point to the data field corresponding to the column in the first element of the array, and StrLen_or_IndPtr should point to the corresponding size field in the first element (element 0) of the array.
Change the original SQLFETCH code to use an array structure rather than a non-array structure.

The following gray lines below show how you can alter the previous SQLFETCH example to use row-wise binding:

void CODBCTestView::OnViewRowwisefetch() {
    //Code added by Chuck Wood
    SQLRETURN sr;
    SQLHSTMT hstmt;
    SQLCHAR SQL[] =
        "SELECT EmpName, Salary, Dept FROM Employee";
    // Column Date Variables
    struct {
        SQLCHAR name[51];
        double salary;
        SQLCHAR dept[11];
        SQLINTEGER nameLength;
        SQLINTEGER salaryLength;
        SQLINTEGER deptLength;


    } row [2];  //Make this an array


    char message[4096];
    strcpy (message,
        "Rowwise Fetch Results:\n"); //Initialize message
    // Allocate Statement Handle
    sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &hstmt);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
  "Error in allocating statement in OnViewRowwisefetch");
    // Execute SQL statement
    sr = SQLExecDirect(hstmt, SQL, SQL_NTS);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
  "Error executing statement in OnViewRowwisefetch");


    // Set the number of rows to retrieve
    sr = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
        (void *) 2, SQL_IS_INTEGER);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
  "Error in array size in OnViewRowwisefetch");
    // Set the size of the structure for each row.
    sr = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE,
        (void *) sizeof(row[0]), SQL_IS_INTEGER);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
  "Error in setting bind type in OnViewRowwisefetch");
    // Bind each column
    sr = SQLBindCol(hstmt, 1, SQL_C_CHAR,


     row[0].name, sizeof(row[0].name), &row[0].nameLength);


    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
            "Error in Binding 1 in OnViewRowwisefetch");
    sr = SQLBindCol(hstmt, 2, SQL_C_DOUBLE,


     &row[0].salary, sizeof(row[0].salary),
     &row[0].salaryLength);


    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
            "Error in Binding 2 in OnViewRowwisefetch");
    sr = SQLBindCol(hstmt, 3, SQL_C_CHAR,


     row[0].dept, sizeof(row[0].dept), &row[0].deptLength);


    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
            "Error in Binding 3 in OnViewRowwisefetch");
    // Start fetching records
    SQLFetch(hstmt);


    for (int loop = 0; loop < 2; loop++) {


        sprintf(message,
         "%s\tName: %s \tSalary: %g \t Department: %s\n",
         message,


         row[loop].name, row[loop].salary, row[loop].dept);
    }
    MessageBox(message);
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}

Calling `SQLFetch()` for Block Cursors

After you have your bindings set up, and have set the desired length for the rowset, you can start retrieving rowsets by calling the SQLFetch() function, as you saw previously. The difference is that if you have specified a rowset size of greater than one, more than one row will be retrieved.

Row Status Information

In ODBC 2, the SQLExtendedFetch() function took additional parameters for returning an array of row status values, and a count of rows returned. In ODBC 3, you can specify a location that will receive the number of rows retrieved by SQLFetchScroll() by setting the SQL_ATTR_ROWS_FETCHED_PTR statement attribute to point to the location to receive the number of rows.

In addition, you can specify a value of the SQL_ATTR_ROW_STATUS_PTR statement attribute to point to an array of SQLUSMALLINTs that will receive one of the following values for each row in the rowset. These values reflect the state of the row because it was last retrieved from the data source:

SQL_ROW_SUCCESS—The row is unchanged.
SQL_ROW_SUCCESS_WITH_INFO—The row was fetched successfully, but generated a warning that can be retrieved with SQLGetDiagRec().
SQL_ROW_UPDATED—The row has been updated.
SQL_ROW_DELETED—The row has been deleted.
SQL_ROW_ADDED—The row has been added.
SQL_ROW_ERROR—The row could not be retrieved due to error.
SQL_ROW_NOROW—No row was retrieved for this position in the rowset.

Block Cursors and Single-Row Functions

If you are using a block cursor, you take an extra step before using functions such as SQLGetData() that operate on a single current row. You set the current row pointer by calling SQLSetPos() with the SQL_POSITION option.

Using Scrollable Cursors

In addition to the block cursor operations that you saw previously, many drivers support scrollable cursors, which allow you to use SQLFetchScroll() to move freely around in the result set, without having to start over at the beginning.

When you fetch a new rowset with a scrollable cursor, the new rowset may also include changes that have been made to the result set because it was first selected from the data source. ODBC provides four different types of scrollable cursors—each can detect different sorts of changes in the result set:

Static cursors are the simplest because they don't reflect any changes to the result set once it has been retrieved from the data source.
Dynamic cursors, on the other hand, reflect all changes to the result set, including any changes that may be made by other users of the database.
Keyset-driven cursors generally detect any changes to the values of the rows that were initially selected in the result set, although they may not detect any new rows that are added or any changes to the ordering of the rows in the result set. This type of cursor builds a keyset containing the key for each row in the result set. This is used to determine whether any of the rows in the result set have changed.
Mixed cursors are a combination of keyset-driven and dynamic cursors. They generally will detect only changes to the values for the rows within the current keyset, but will detect any changes when you fetch a rowset outside of the current keyset.

In any case, changes to the result set are detected only when a new rowset is fetched with a call to SQLFetchScroll(). Furthermore, the detection of changes may also be affected by the current transaction isolation level, which you will look at when you talk about transactions.

Cursor Support

The following items returned by SQLGetInfo() give information about the level of cursor support provided by the current driver:

SQL_CURSOR_SENSITIVITY indicates whether a cursor can detect changes made outside of this cursor—for example, by other users.
SQL_SCROLL_OPTIONS indicates only the types of cursors supported (forward-only, static, keyset-driven, dynamic, mixed).
SQL_DYNAMIC_CURSOR_ATTRIBUTES1, SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1, SQL_KEYSET_CURSOR_ATTRIBUTES1, and SQL_STATIC_CURSOR_ATTRIBUTES1 return a bitmap of the fetch type values that may be used in SQLFetchScroll() for the given cursor type.
SQL_KEYSET_CURSOR_ATTRIBUTES2 and SQL_STATIC_CURSOR_ATTRIBUTES2 indicate whether the cursor can detect its own updates, deletes, and inserts.

Setting the Cursor Type

Before executing a statement that returns a result set, you can set the type of cursor that will be used by setting the SQL_ATTR_CURSOR_TYPE statement attribute. In addition, for keyset-driven and mixed cursors, you can set the size of the keyset used by setting the SQL_ATTR_KEYSET_SIZE statement attribute. By default, this attribute is set to 0, in which case the keyset size will be set to the size of the entire result set. To use a mixed cursor, you can specify a value for the keyset size that is smaller than the size of the result set.

As an alternative to setting the SQL_ATTR_CURSOR_TYPE attribute explicitly, you can allow ODBC to select a cursor type based on the values you set for the SQL_ATTR_ CONCURRENCY, SQL_ATTR_CURSOR_SCROLLABLE, or SQL_ATTR_CURSOR_SENSITIVITY attributes. Any time you make a change to one of these four attributes, the other three settings may be changed by the driver to reflect the currently selected cursor type.

Calling `SQLFetchScroll()`

To retrieve a new rowset from the result set using a scrollable cursor, you should use the SQLFetchScroll() function, which allows you to move to random locations within the result set. The following is the prototype for SQLFetchScroll():

SQLRETURN SQLFetchScroll(SQLHSTMT StatementHandle, SQLSMALLINT FetchOrientation,
    SQLINTEGER FetchOffset);

The FetchOrientation parameter specifies how to move through the result set to find the next rowset to retrieve. This may be set to one of these values:

SQL_FETCH_FIRST retrieves the first rowset in the result set.
SQL_FETCH_NEXT retrieves the next rowset in the result set. (If the cursor is positioned before the first row, this is equivalent to SQL_FETCH_FIRST.)
SQL_FETCH_LAST retrieves the last rowset in the result set.
SQL_FETCH_PRIOR retrieves the previous rowset in the result set.
SQL_FETCH_ABSOLUTE retrieves the rowset starting with the row specified in FetchOffset. You can specify a negative value for FetchOffset to retrieve the rowset, starting with the row a given number of rows from the end of the result set.
SQL_FETCH_RELATIVE retrieves the rowset beginning with the row FetchOffset rows from the start of the current rowset. This may include negative values to move backwards.
SQL_FETCH_BOOKMARK retrieves the rowset beginning with the row specified by a bookmark value passed in FetchOffset.

Other than the ability to move at will through the result set, this function works just like SQLFetch()—the rowset that is retrieved will be moved into memory locations, as specified in SQLBindCol() calls.

Using Bookmarks

Many ODBC drivers support the use of bookmarks to directly access a given row in a result set, as you saw in SQLFetchScroll() with the SQL_FETCH_BOOKMARK option.

You can determine what level of support the current driver offers for bookmarks by calling SQLGetInfo() with the SQL_BOOKMARK_PERSISTENCE option.

To use bookmarks, you should use SQLSetStmtAttr() to set the SQL_ATTR_USE_BOOKMARK attribute to SQL_UB_VARIABLE before executing the SQL statement that generates the result set.

ODBC 3 uses only variable length bookmarks, which will be of different sizes for different drivers. After you have retrieved a result set with bookmarks enabled, you can call SQLColAttribute() for the SQL_DESC_OCTET_LENGTH field for column 0 to find the length needed to hold a bookmark.

You can retrieve the bookmark for a given row by either binding column 0 of the result set or using SQLGetData() to retrieve column 0 from a row. These bookmarks can then be used in subsequent calls such as SQLFetchScroll(). Bookmarks are also used to perform updates, deletions, and fetches by bookmark with the SQLBulkOperations() function, which you will see later.