InformIT: Visual C++ 6 Unleashed > Working with Result Sets

Table of Contents

Working with Result Sets

Okay, so you can send SQL to the database with SQLExecDirect() or SQLExecute(), but how do you get your hands on the data the SQL returns? In this section, you will look at how to work with the data (called the result set) returned by calls like SQLExecDirect().

The result set returned by a query is like a temporary table—it includes rows of columns. Rows are retrieved from the result set using cursors, which come in several different flavors. The default cursor used in ODBC is a forward-only cursor, which allows you to access the rows in the result set only one row at a time. Furthermore, if you want to back up in the result set, you close the cursor and start over at the first row. You look at the forward-only cursor first because it is supported in all drivers. You look at other cursor types, which can be used to access more than one row at a time, later on in this chapter.

In most cases, the best way to retrieve data from a result set is to bind the columns of the result set to specific memory locations ahead of time. Then, when you call SQLFetch(), the data for each column is copied into the memory location that you have bound for that column. You will also see how you can use SQLGetData() to retrieve one column at a time from the current row, after it has been fetched.

Binding Columns

There are two ways to access the data from individual rows of a result set. The first involves binding a column to a location in memory with SQLBindCol() before calling SQLFetch(), at which point the data from the row will be copied into the assigned locations. The second method, which you will see in just a bit, involves calling SQLGetData() to copy a column's data into a memory location after the call to SQLFetch().

`SQLBindCol()`

To assign the memory location that a column's data should be copied to when a row is fetched, you use the SQLBindCol() function:

SQLRETURN SQLBindCol( SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber,
    SQLSMALLINT TargetType, SQLPOINTER TargetValuePtr, SQLINTEGER BufferLength,
    SQLINTEGER * StrLen_or_IndPtr);

The StatementHandle parameter should be the statement handle on which you are performing SQLExecDirect() and SQLFetch().

The ColumnNumber parameter specifies the number of the column to bind, starting with 1 and numbering left to right (column 0 is used for retrieving bookmarks, as you will see later).

The TargetType parameter is used to specify the desired C data type (for example, SQL_C_CHAR) that the data will be returned as. You saw the available data types in the beginning of this chapter.

When SQLFetch() is called to fetch the next row in the result set, ODBC will attempt to convert the data for each bound column from SQL format to the type specified by the TargetType parameter of SQLBindCol(). This may include some rather elaborate conversions, such as converting a numeric value into an ASCII string or converting character strings into various numeric formats.

If errors occur in the conversion process, or if certain events such as data truncation occur, SQLFetch() will return SQL_ERROR or SQL_SUCCESS_WITH_INFO, and the specific conversion errors (or warnings) can be retrieved with SQLGetDiagRec().

Remember that SQLGetDiagRec() should be called repeatedly until it returns SQL_NO_DATA_FOUND. This allows you to retrieve multiple errors for the same call to SQLFetch().

The TargetValuePtr parameter is a pointer to the location in memory where you want ODBC to place the data, and BufferLength should be passed the maximum size of this data buffer. (For strings, you should include space for the null terminator.)

The memory location at StrLen_or_IndPtr will receive the length of the actual data returned for the bound column each time SQLFetch() is called. In the event a column's data is NULL, this value will be set to SQL_NULL_DATA.

`SQLFetch()`

To access each row of the result set, including the first row, you call the SQLFetch() function:

SQLRETURN SQLFetch(SQLHSTMT StatementHandle);

This function simply makes the next row in the result set the current row. It will also copy the data from any bound columns into the memory locations assigned with SQLBindCol(). When there are no more rows available, a call to SQLFetch() will return SQL_NO_DATA_FOUND.

The best method of getting the number of rows in a result set is simply scrolling through them with SQLFetch() until no more are found. You can retrieve the count of rows in a result set with SQLRowCount(), but this isn't supported in all drivers.

The menu resource and the ClassWizard were used to develop a "Fetch Results" menu option. The following example shows the use of SQLExecDirect(), SQLBindCol(), and SQLFetch() to retrieve a single row of data:

void CODBCTestView::OnViewFetchresults() {


    //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;
    char message[4096];
    strcpy (message, "Fetch Results:\n"); //Initialize
    // 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 OnViewfetchresults");
    // Execute SQL statement
    sr = SQLExecDirect(hstmt, SQL, SQL_NTS);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
 "Error executing statement in OnViewfetchresults");
    // Bind each column
    sr = SQLBindCol(hstmt, 1, SQL_C_CHAR,
        row.name, sizeof(row.name), &row.nameLength);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
         "Error in Binding 1 in OnViewfetchresults");
    sr = SQLBindCol(hstmt, 2, SQL_C_DOUBLE,
     &row.salary, sizeof(row.salary), &row.salaryLength);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
         "Error in Binding 2 in OnViewfetchresults");
    sr = SQLBindCol(hstmt, 3, SQL_C_CHAR,
        row.dept, sizeof(row.dept), &row.deptLength);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
          "Error in Binding 3 in OnViewfetchresults");
    // Start fetching records
    while (SQLFetch(hstmt) == SQL_SUCCESS) {
        sprintf(message,
          "%s\tName: %s \tSalary: %g \t Department: %s\n",
          message,
          row.name, row.salary, row.dept);
    }
    MessageBox(message);
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

Figure 20.6 shows the output from the OnViewFetchresults() function.

Figure 20.6 SQLFETCH allows you to process multiple results from an SQL query.

Multiple Result Sets

It is possible for a single call to SQLExecute() to generate several different result sets, as is the case when you execute a batch of SQL statements or call a procedure that returns multiple result sets. After you are finished with the current result set, you can call SQLMoreResults() to move on to the next result set. Generally, you will then want to rebind columns and fetch rows from the new result set with SQLFetch().

Closing the Cursor

When you call a function that creates a result set, such as SQLExecute(), a cursor is opened for you. When you are finished working with a result set, you should close the cursor that was used to fetch the data by calling SQLCloseCursor().

Reusing Statement Handles

You could allocate a separate statement handle for every SQL statement that your application will execute, although it is often neater (and saves overhead) if you can reuse your statement handles for multiple operations.

Before you can use a statement handle for a new operation, you should first free the parameter and result set bindings that were used for the previous operation by calling SQLFreeStmt(), with the SQL_UNBIND option to free any column bindings and the SQL_RESET_PARAMS option to free any parameter bindings. You can then use the statement handle for a new operation, as if it were newly allocated, although you should make sure that the statement attributes are correct for the new operation.

`SQLGetData()`

ODBCalso offers an alternative to using SQLBindCol() to bind columns to memory locations before calling SQLFetch(). You can call SQLGetData() to retrieve a single column's data, after the current row has been selected with SQLFetch(). This function provides the same sort of data conversion that is set up with SQLBindCol().

In most cases, your application should either bind all columns or retrieve all columns with SQLGetData(). Depending on the driver you are using, there may be some rather strict limitations on support for mixing bound columns and SQLGetData(). For instance, many drivers don't allow you to call SQLGetData() for bound columns, and any columns that you intend to use SQLGetData() on must come after the last bound column.

Here is the prototype for SQLGetData():

SQLRETURN SQLGetData( SQLHSTMT StatementHandle, SQLUSMALLINT ColumnNumber,
    SQLSMALLINT TargetType, SQLPOINTER TargetValuePtr,
    SQLINTEGER BufferLength, SQLINTEGER * StrLen_or_IndPtr);

The parameters used for SQLGetData() are identical to those used for SQLBindCol(). The only difference is when the actual data transfer takes place. When using SQLBindCol(), the data is transferred each time you call SQLFetch(), whereas SQLGetData() is a one-time affair, retrieving the data from a row after it is selected with SQLFetch().

For columns that contain large character-, binary-, or driver-specific data, you can use multiple calls to SQLGetData() to retrieve the information. On the first call to SQLGetData(), ODBC will move up to BufferLength bytes into the buffer at TargetValuePtr. If there is still more data to retrieve, the call to SQLGetData() will return SQL_SUCCESS_WITH_INFO, and a subsequent call to SQLGetDiagRec() will show a SQLSTATE of 01004 (data truncated). You can then make additional calls to SQLGetData() with the same column number to retrieve additional blocks of the data in the column. When all the data has been retrieved, SQLGetData() will return SQL_SUCCESS.

In addition, you can use SQLGetData() to retrieve information from result sets that may have a variable number of columns. In this case, you may find the functions in the next section useful in determining information about the columns in the result set.

Column Information

Although it is generally a good idea for your database application to know what sort of data it will be retrieving ahead of time, you may find cases, such as a general database browser app, where the application doesn't know which columns will be returned in a result set at compile time. You can retrieve the number of columns in a result set by calling SQLNumResultCols() and can get specific information about each column with SQLDescribeCol() and SQLColAttribute().

`SQLDescribeCol()`

After you know how many columns there are, you can call SQLDescribeCol() for each column:

SQLRETURN SQLDescribeCol(SQLHSTMT StatementHandle, SQLSMALLINT ColumnNumber,
    SQLCHAR * ColumnName, SQLSMALLINT BufferLength, SQLSMALLINT * NameLengthPtr,
    SQLSMALLINT * DataTypePtr, SQLUINTEGER * ColumnSizePtr,
    SQLSMALLINT * DecimalDigitsPtr, SQLSMALLINT * NullablePtr);

This will return the column name at ColumnName and the length of this string at NameLengthPtr. The length of the buffer at ColumnName should be passed in BufferLength.

The SQL data type of the column is returned at DataTypePtr. This value will be one of the constants, such as SQL_CHAR, that you saw in the earlier section on ODBC data types.

The precision and scale of the column are returned at ColumnSizePtr and DecimalDigitsPtr, respectively.

The value returned at NullablePtr indicates whether the column allows NULL values. This will be one of the following values:

SQL_NO_NULLS—NULL values aren't allowed.
SQL_NULLABLE—NULL values are allowed.
SQL_NULLABLE_UNKNOWN—indicates the driver cannot determine whether NULL values are allowed.

`SQLColAttribute()`

You can also retrieve various attributes of a result set column by calling SQLColAttribute(), which allows you to get at information such as the format, case-sensitivity, display size, owner, precision, or update permission of a column.

Retrieving More Than One Row at a Time | Next Section

Previous Section