SQLExtendedFetch() - Fetch an array of rows

SQLExtendedFetch() extends the function of SQLFetch() by returning a row set array for each bound column. The value the SQL_ATTR_ROWSET_SIZE statement attribute determines the size of the row set that SQLExtendedFetch() returns.

ODBC specifications for SQLExtendedFetch()

Table 1. SQLExtendedFetch() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
1.0 (Deprecated) No No

Syntax

For 31-bit applications, use the following syntax:

SQLRETURN SQLExtendedFetch  (SQLHSTMT           hstmt,
                             SQLUSMALLINT       fFetchType,
                             SQLINTEGER             irow,
                             SQLUINTEGER  FAR   *pcrow,
                             SQLUSMALLINT FAR   *rgfRowStatus);

For 64-bit applications, use the following syntax:

SQLRETURN SQLExtendedFetch  (SQLHSTMT           hstmt,
                             SQLUSMALLINT       fFetchType,
                             SQLLEN             irow,
                             SQLULEN      FAR   *pcrow,
                             SQLUSMALLINT FAR   *rgfRowStatus);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. SQLExtendedFetch() arguments
Data type Argument Use Description
SQLHSTMT hstmt input Specifies the statement handle from which you retrieve an array data.
SQLUSMALLINT fFetchType input Specifies the direction and type of fetch. Db2 ODBC supports only the fetch direction SQL_FETCH_NEXT (that is, forward-only cursor direction). The next array (row set) of data is always retrieved.
SQLINTEGER (31-bit) or SQLLEN (64-bit) 1 irow input Reserved for future use. Use any integer for this argument.
SQLUINTEGER *(31-bit) or SQLULEN * (64-bit)2 pcrow output Returns the number of the rows that are actually fetched. If an error occurs during processing, the pcrow argument points to the ordinal position of the row (in the row set) that precedes the row where the error occurred. If an error occurs retrieving the first row, the pcrow argument points to the value 0.
SQLUSMALLINT * rgfRowStatus output Returns an array of status values. The number of elements must equal the number of rows in the row set (as defined by the SQL_ATTR_ROWSET_SIZE attribute). A status value for each row that is fetched is returned:
  • SQL_ROW_SUCCESS

If the number of rows fetched is less than the number of elements in the status array (that is, less than the row set size), the remaining status elements are set to SQL_ROW_NOROW.

Db2 ODBC cannot detect whether a row has been updated or deleted since the start of the fetch. Therefore, the following ODBC-defined status values are not reported:
  • SQL_ROW_DELETED
  • SQL_ROW_UPDATED
Notes:
  1. For 64-bit applications, the data type SQLINTEGER, which was used in previous versions of Db2, is still valid. However, for maximum application portability, using SQLLEN is recommended.
  2. For 64-bit applications, the data type SQLUINTEGER, which was used in previous versions of Db2, is still valid. However, for maximum application portability, using SQLULEN is recommended.

Usage

SQLExtendedFetch() performs an array fetch of a set of rows. An application specifies the size of the array by calling SQLSetStmtAttr() with the SQL_ROWSET_SIZE attribute.

You cannot mix SQLExtendedFetch() with SQLFetch() when you retrieve results.

Before SQLExtendedFetch() is called the first time, the cursor is positioned before the first row. After SQLExtendedFetch() is called, the cursor is positioned on the row in the result set corresponding to the last row element in the row set that was just retrieved.

To fetch one row of data at a time, call SQLFetch() instead of SQLExtendedFetch().

The number of elements in the rgfRowStatus array output buffer must equal the number of rows in the row set (as defined by the SQL_ROWSET_SIZE statement attribute). If the number of rows fetched is less than the number of elements in the status array, the remaining status elements are set to SQL_ROW_NOROW.

For any columns in the result set that are bound using the SQLBindCol() function, Db2 ODBC converts the data for the bound columns as necessary and stores it in the locations that are bound to these columns. The result set can be bound in a column-wise or row-wise fashion.

Column-wise binding: To bind a result set in column-wise fashion, an application specifies SQL_BIND_BY_COLUMN for the SQL_BIND_TYPE statement attribute. (This is the default value.) Then the application calls the SQLBindCol() function. To bind LOB column values to files, the application can call the SQLBindFileToCol() function.

When you call SQLExtendedFetch(), data for the first row is stored at the start of the buffer. Each subsequent row of data is stored at an offset of the number of bytes that you specify with the cbValueMax argument in the SQLBindCol() call. If, however, the associated C buffer type is fixed-width (such as SQL_C_LONG), the data is stored at an offset corresponding to that fixed-length from the data for the previous row.

For each bound column, the number of bytes that are available to return for each element is stored in the array buffer that the pcbValue argument on SQLBindCol() specifies. The number of bytes that are available to return for the first row of that column is stored at the start of the buffer. The number of bytes available to return for each subsequent row is stored at an offset equal to the value that the following C function returns: 
sizeof(SQLINTEGER)
If the data in the column is null for a particular row, the associated element in the array that the pcbValue argument in SQLBindCol() points to is set to SQL_NULL_DATA.

Row-wise binding: The application needs to first call SQLSetStmtAttr() with the SQL_BIND_TYPE attribute, with the vParam argument set to the size of the structure capable of holding a single row of retrieved data and the associated data lengths for each column data value.

For each bound column, the first row of data is stored at the address given by the rgbValue argument in SQLBindCol(). Each subsequent row of data is separated by an offset equal to the number of bytes that you specify in the vParam argument in SQLSetStmtAttr() from the data for the previous row.

For each bound column, the number of bytes that are available to return for the first row is stored at the address given by the pcbValue argument in SQLBindCol(). Each subsequent value is separated by an offset equal to the number of bytes you specify in the vParam argument in SQLBindCol().

Error handling: SQLExtendedFetch() returns errors in the same manner as SQLFetch() with the following exceptions:
  • When a warning occurs that applies to a particular row in the rowset, SQLExtendedFetch() sets the corresponding entry in the row status array to SQL_ROW_SUCCESS, not SQL_ROW_SUCCESS_WITH_INFO.
  • If errors occur in every row in the rowset, SQLExtendedFetch() returns SQL_SUCCESS_WITH_INFO, and not SQL_ERROR.
  • In each group of status records that applies to an individual row, the first status record that is returned by SQLExtendedFetch() contains SQLSTATE 01S01 (error in row). If SQLExtendedFetch() cannot return additional SQLSTATEs, it returns only SQLSTATE 01S01.

Handling encoding schemes: The CURRENTAPPENSCH keyword in the initialization file and the fCType argument in SQLBindCol() or SQLGetData() determine the encoding scheme of any character or graphic data in the result set.

Return codes

After you call SQLExtendedFetch(), it returns one of the following values:
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE
  • SQL_NO_DATA_FOUND

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 3. SQLExtendedFetch() SQLSTATEs
SQLSTATE Description Explanation
01004 Data truncated. The data that is returned for one or more columns is truncated. (SQLExtendedFetch() returns SQL_SUCCESS_WITH_INFO for this SQLSTATE.)
01S01 Error in row. An error occurs while fetching one or more rows. (SQLExtendedFetch() returns SQL_SUCCESS_WITH_INFO for this SQLSTATE.)
07002 Too many columns. This SQLSTATE is returned for one or more of the following reasons:
  • A column number that is specified in the bind of one or more columns is greater than the number of columns that are in the result set.
  • The application uses SQLSetColAttributes() to inform Db2 ODBC of the descriptor information of the result set, but it does not provide this information for every column that is in the result set.
07006 Invalid conversion. The data value can not be converted in a meaningful manner to the data type that the fCType argument in SQLBindCol() specifies.
08S01 Communication link failure. The communication link between the application and data source fails before the function completes.
22002 Invalid output or indicator buffer specified. The pcbValue argument in SQLBindCol() specifies a null pointer and the value of the corresponding column is null. The function can not report SQL_NULL_DATA.
22008 Invalid datetime format or datetime field overflow. This SQLSTATE is returned for one or more of the following reasons:
  • Conversion from character string to datetime format is indicated, but an invalid string representation or value is specified, or the value is an invalid date.
  • The value of a date, time, or timestamp does not conform to the syntax for the data type that is specified.
  • Datetime field overflow occurred.

    Example: An arithmetic operation on a date or timestamp produces a result that is not within the valid range of dates, or a datetime value cannot be assigned to a bound variable because it is too small.
22012 Division by zero is invalid. A value from an arithmetic expression is returned that results in division by zero.
22018 Error in assignment. This SQLSTATE is returned for one or more of the following reasons:
  • A returned value is incompatible with the data type of the bound column.
  • A returned LOB locator was incompatible with the data type of the bound column.
24000 Invalid cursor state. The SQL statement that is executed on the statement handle is not a query.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY010 Function sequence error. This SQLSTATE is returned for one or more of the following reasons:
  • SQLExtendedFetch() is called on a statement handle after a SQLFetch() call, and before the SQLFreeStmt() (with the fOption argument set to SQL_CLOSE) call.
  • The function is called prior to calling SQLPrepare() or SQLExecDirect() on the statement handle.
  • The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the SQLParamData() or SQLPutData() functions.)
HY013 Unexpected memory handling error. Db2 ODBC is not able to access the memory that is required to support execution or completion of the function.
HY019 Numeric value out of range. This SQLSTATE is returned for one or more of the following reasons:
  • A numeric value (as numeric or string) that is returned for one or more columns causes the whole part of a number to be truncated either at the time of assignment or in computing an intermediate result.
  • A value from an arithmetic expression is returned that results in division by zero.
HY106 Fetch type out of range. The value that thefFetchType argument specifies is not recognized.
HYC00 Driver not capable. This SQLSTATE is returned for one or more of the following reasons:
  • Db2 ODBC or the data source does not support the conversion that the fCTypeargument in SQLBindCol() and the SQL data type of the corresponding column require.
  • A call to SQLBindCol() is made for a column data type that Db2 ODBC does not support.
  • The specified fetch type is recognized, but it is not supported.

Example

Although this function is deprecated in ODBC 3.0, this function is not deprecated in Db2 ODBC. However, ODBC applications should use SQLFetchScroll(), rather than SQLExtendedFetch().

Example

The following example shows an application that uses SQLExtendedFetch() to perform an array fetch.
Figure 1. An application that performs an array fetch
/* ... */
    "SELECT deptnumb, deptname, id, name FROM staff, org \
                     WHERE dept=deptnumb AND job = 'Mgr'";
    /* Column-wise */
    SQLINTEGER      deptnumb[ROWSET_SIZE];
    SQLCHAR         deptname[ROWSET_SIZE][15];
    SQLINTEGER      deptname_l[ROWSET_SIZE];
    SQLSMALLINT     id[ROWSET_SIZE];
    SQLCHAR         name[ROWSET_SIZE][10];
    SQLINTEGER      name_l[ROWSET_SIZE];
    /* Row-wise (Includes buffer for both column data and length) */
    struct {
        SQLINTEGER      deptnumb_l; /* length */
        SQLINTEGER      deptnumb; /* value  */
        SQLINTEGER      deptname_l;
        SQLCHAR         deptname[15];
        SQLINTEGER      id_l;
        SQLSMALLINT     id;
        SQLINTEGER      name_l;
        SQLCHAR         name[10];
    }               R[ROWSET_SIZE];
    SQLUSMALLINT    Row_Stat[ROWSET_SIZE];
    SQLUINTEGER     pcrow;
    int             i;
/* ... */
    /*********************************************/
    /* Column-wise binding                       */
    /*********************************************/
    rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
    rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWSET_SIZE, (void*) ROWSET_SIZE, 0);
    rc = SQLExecDirect(hstmt, stmt, SQL_NTS);
    rc = SQLBindCol(hstmt, 1, SQL_C_LONG, (SQLPOINTER) deptnumb, 0, NULL);
    rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) deptname,
                    15, deptname_l);
    rc = SQLBindCol(hstmt, 3, SQL_C_SSHORT, (SQLPOINTER) id, 0, NULL);
    rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) name, 10, name_l);
    /* Fetch ROWSET_SIZE rows ast a time, and display */
    printf("\nDEPTNUMB DEPTNAME         ID       NAME\n");
    printf("-------- -------------- -------- ---------\n");
    while ((rc = SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 0, 
            &pcrow, Row_Stat)) == SQL_SUCCESS) {
        for (i = 0; i < pcrow; i++) {
            printf("%8ld %-14s %8ld %-9s\n", deptnumb[i], deptname[i],
                   id[i], name[i]);
        }
        if (pcrow < ROWSET_SIZE)
            break;
    }                           /* endwhile */
    if (rc != SQL_NO_DATA_FOUND && rc != SQL_SUCCESS)
        CHECK_HANDLE(SQL_HANDLE_STMT, hstmt, rc);
    rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
     /*********************************************/
    /* Row-wise binding               */
    /*********************************************/
    rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
    CHECK_HANDLE(SQL_HANDLE_STMT, hstmt, rc);
    /* Set maximum number of rows to receive with each extended fetch */
    rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWSET_SIZE, (void*) ROWSET_SIZE, 0);
    CHECK_HANDLE(SQL_HANDLE_STMT, hstmt, rc);
    /*
     * Set vparam to size of one row, used as offset for each bindcol
     * rgbValue
     */
    /* ie. &(R[0].deptnumb) + vparam = &(R[1].deptnum) */
    rc = SQLSetStmtAttr(hstmt, SQL_ATTR_BIND_TYPE, 
                         (void*) (sizeof(R) / ROWSET_SIZE), 0);
    rc = SQLExecDirect(hstmt, stmt, SQL_NTS);
    rc = SQLBindCol(hstmt, 1, SQL_C_LONG, (SQLPOINTER) &R[0].deptnumb, 0,
                    &R[0].deptnumb_l);
    rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) R[0].deptname, 15,
                    &R[0].deptname_l);
    rc = SQLBindCol(hstmt, 3, SQL_C_SSHORT, (SQLPOINTER) &R[0].id, 0,
                    &R[0].id_l);
    rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) R[0].name, 10, &R[0].name_l);
    /* Fetch ROWSET_SIZE rows at a time, and display */
    printf("\nDEPTNUMB DEPTNAME         ID       NAME\n");
    printf("-------- -------------- -------- ---------\n");
    while ((rc = SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 0, &pcrow, Row_Stat))
           == SQL_SUCCESS) {
        for (i = 0; i < pcrow; i++) {
            printf("%8ld %-14s %8ld %-9s\n", R[i].deptnumb, R[i].deptname,
                R[i].id, R[i].name);
        }
        if (pcrow < ROWSET_SIZE)
            break;
    }                           /* endwhile */
    if (rc != SQL_NO_DATA_FOUND && rc != SQL_SUCCESS)
        CHECK_HANDLE(SQL_HANDLE_STMT, hstmt, rc);
    /* Free handles, commit, exit */
/* ... */