Fetching Multiple Rows at Once

SQLFetch() retrieves one row of the result set at a time. It can be a significant network overhead for an application accessing the data source through a remote server. To reduce network traffic, SQL API provides a way to tell its driver to retrieve the specified number of rows at a time. An application can call SQLSetStmtAttr() and set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows to retrieve with a single call to SQLFetch(), as shown in Example 1. If the value of the attribute is 0 (as is by default), SQLFetch() retrieves one row at a time.

When an application fetches multiple rows at a time, the variable it binds to a column must be an array whose cardinality should match the number of rows to retrieve. For instance, if 10 rows are to be fetched at a time, the variable bound to an INTEGER column must be declared as "int16_t var[10]".

When one call to SQLFetch() returns multiple rows, the return code of SQLFetch() itself cannot represent the potentially different statuses of the retrieved rows. For instance, one of the 10 rows retrieved may have its data truncated. In a situation like that, where some (but not all) returned rows contain errors or warnings, SQLFetch() returns SQL_SUCCESS_WITH_INFO. An application can call SQLGetDiagRec() to retrieve all the errors and warnings contained in the result set.

An application can store the statuses of all result rows into an array specified by the application. It can be done by setting the SQL_ATTR_ROW_STATUS_PTR statement attribute to the pointer to the array of SQLUSMALLINTs. The cardinality of the array should match the number of result rows to retrieve at once.

Finally, an application can set the SQL_ATTR_ROWS_FETCHED_PTR statement attribute to the pointer to an SQLULEN variable to have the number of returned rows stored in that variable.

The following example illustrates how to retrieve 10 rows at a time from the PERSON table.

Example 1. Retrieving multiple rows

#include "sqlrdm.h"

SQLRETURN runDbOperations (SQLHDBC hDbc)
{
    SQLRETURN rc;
    SQLHSTMT hStmt;
    const char *pszStmt = "SELECT * FROM person";
    char szName[36];
    char szGender[2];
    char szJob[2];
    SQLLEN cb1, cb2, cb;

    /* Allocate a statement handle */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hDbc, &hStmt);
    if (SQL_SUCCEEDED (rc))
    {
        /* Open the NSFAWARDS database first */
        rc = SQLExecDirect (hStmt, (SQLCHAR *) "OPEN nsfawards", SQL_NTS);
        if (SQL_SUCCEEDED (rc))
        {
            /* Execute a SELECT statement */
            rc = SQLExecDirect (hStmt, (SQLCHAR *) pszStmt, SQL_NTS);

            if (SQL_SUCCEEDED (rc))
            {
                /* Bind all the 3 columns */
                (void) SQLBindCol (
                    hStmt 1, SQL_C_CHAR, szName, sizeof (szName), &cb1);
                (void) SQLBindCol (
                    hStmt 2, SQL_C_CHAR, szGender, sizeof (szGender), &cb2);
                (void) SQLBindCol (
                    hStmt 3, SQL_C_CHAR, szJob, sizeof (szJob), &cb3);

                do
                {
                    /* Fetch as many rows as there are */
                    rc = SQLFetch (hStmt);
                    if (SQL_SUCCEEDED (rc))
                    {
                        if (cb1 != SQL_NULL_DATA)
                            printf ("Name = %s\n", szName);
                        if (cb2 != SQL_NULL_DATA)
                            printf ("Gender = %s\n", szGender);
                        if (cb3 != SQL_NULL_DATA)
                            printf ("Jobclass = %s\n", szJob);
                    }
                } while (SQL_SUCCEEDED (rc));

                /* This means fetch completed successfully */
                if (rc == SQL_NO_DATA)
                    rc = SQL_SUCCESS;
            }
        }
        (void) SQLFreeHandle (SQL_HANDLE_STMT, hStmt);
    }

    return rc;
}

The SQL API Driver only supports forward-only cursors. While it provides SQLFetchScroll(), the function only accepts SQL_FETCH_NEXT as the fetch type. It means SQLFetch() and SQLFetchScroll() currently work the same way in SQL API.