SQLFetch() - Fetch the next row

SQLFetch() advances the cursor to the next row of the result set and retrieves any bound columns. Columns can be bound to either the application storage or LOB locators.

ODBC specifications for SQLFetch()

Table 1. SQLFetch() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
1.0 Yes Yes

Syntax

SQLRETURN   SQLFetch         (SQLHSTMT          hstmt);

Function arguments

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

Table 2. SQLFetch() arguments
Data type Argument Use Description
SQLHSTMT hstmt input Specifies the statement handle from which to fetch data.

Usage

When you call SQLFetch(), Db2 ODBC performs the appropriate data transfer, along with any data conversion that was indicated when you bound the column. You can call SQLGetData() to retrieve the columns individually after the fetch.

You can call SQLFetch() only after you generate a result set. Any of the following actions generate a result set:
  • Executing a query
  • Calling SQLGetTypeInfo()
  • Calling a catalog function

To retrieve multiple rows at a time, use SQLExtendedFetch().

Call SQLFetch() to retrieve results into bound application variables and to advance the position of the cursor in a result set. You can call SQLFetch() only after a result set is generated on the statement handle. Before you call SQLFetch() for the first time, the cursor is positioned before the start of the result set.

The number of application variables bound with SQLBindCol() must not exceed the number of columns in the result set or SQLFetch() fails.

When you retrieve all the rows from the result set, or do not need the remaining rows, call SQLFreeStmt() or SQLCloseCursor() to close the cursor and discard the remaining data and associated resources.

If SQLBindCol() has not been called to bind any columns, then SQLFetch() does not return data to the application, but just advances the cursor. In this case, SQLGetData() can be called to obtain all of the columns individually. Data in unbound columns is discarded when SQLFetch() advances the cursor to the next row. For fixed-length data types, or small varying-length data types, binding columns provides better performance than using SQLGetData().

Columns can be bound to application storage or you can use LOB locators.

Fetching into application storage: SQLBindCol() binds application storage to the column. You transfer data from the server to the application when you call SQLFetch(). The length of the data that is available to return is also set.

If LOB values are too large to retrieve in one fetch, retrieve these values in pieces either by using SQLGetData() (which can be used for any column type), or by binding a LOB locator and using SQLGetSubString().

Fetching into LOB locators: SQLBindCol() is used to bind LOB locators to the column. Only the LOB locator (4 bytes) is transferred from the server to the application at fetch time.

When your application receives a locator, it can use the locator in SQLGetSubString(), SQLGetPosition(), SQLGetLength(), or as the value of a parameter marker in another SQL statement. SQLGetSubString() can either return another locator, or the data itself. All locators remain valid until the end of the transaction in which they are created (even when the cursor moves to another row), or until they are freed using the FREE LOCATOR statement.

Handling data truncation: If any bound storage buffers are not large enough to hold the data returned by SQLFetch(), the data is truncated. If character data is truncated, SQL_SUCCESS_WITH_INFO is returned, and an SQLSTATE is generated indicating truncation. The SQLBindCol() deferred output argument pcbValue contains the actual length, in bytes, of the column data retrieved from the server. The application should compare the actual output length to the input buffer length (pcbValue and cbValueMax arguments from SQLBindCol()) to determine which character columns are truncated.

Truncation of numeric data types is reported as a warning if the truncation involves digits to the right of the decimal point. If truncation occurs to the left of the decimal point, an error is returned (see Diagnostics).

Truncation of graphic data types is treated the same as character data types, except that the buffer you specify in the rgbValue argument for SQLBindCol(). This buffer is filled to the nearest multiple of two bytes that is less than or equal to the value you specify in the cbValueMax argument for SQLBindCol(). Graphic (DBCS) data transferred between Db2 ODBC and the application is not nul-terminated if the C buffer type is SQL_C_CHAR. If the buffer type is SQL_C_DBCHAR, then nul-termination of graphic data does occur.

To eliminate warnings when data is truncated, call SQLSetStmtAttr() with the SQL_ATTR_MAX_LENGTH attribute set to a maximum length value. Then allocate a buffer for the rgbValue argument that is the same number of bytes (plus the nul-terminator) as the value you specified for SQL_ATTR_MAX_LENGTH. If the column data is larger than the maximum length that you specified for SQL_ATTR_MAX_LENGTH, SQL_SUCCESS is returned. When you specify a maximum length, the length you specify, not the actual length, is returned in the pcbValue argument.

To retrieve multiple rows at a time, use SQLExtendedFetch(). You cannot mix SQLFetch() calls with SQLExtendedFetch() calls on the same statement handle.

Return codes

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

SQL_NO_DATA_FOUND is returned if no rows are in the result set, or previous SQLFetch() calls have fetched all the rows from the result set. If all the rows are fetched, the cursor is positioned after the end of the result set.

Diagnostics

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

Table 3. SQLFetch() SQLSTATEs
SQLSTATE Description Explanation
01004 Data truncated. The data that is returned for one or more columns is truncated. String values or numeric values are truncated on the right. (SQLFetch() 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 for one or more columns is greater than the number of columns in the result set.
  • The application uses SQLSetColAttributes() to inform Db2 ODBC of the descriptor information of the result set, but does not provide this information for every column in the result set.
07006 Invalid conversion. The data value cannot 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 specified data type.
  • Datetime field overflow occurred.

    Example: An arithmetic operation on a date or timestamp has 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 binding.
  • A returned LOB locator is incompatible with the data type of the bound column.
24000 Invalid cursor state. The previous SQL statement that is executed on the statement handle is not a query.
54028 Maximum LOB locator assigned.

The maximum number of concurrent LOB locators has been reached. A new locator can not be assigned.
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.
HY002 Invalid column number. This SQLSTATE is returned for one or more of the following reasons:
  • The specified column is less than 0 or greater than the number of result columns.
  • The specified column is 0, but Db2 ODBC does not support ODBC bookmarks (icol = 0).
  • SQLExtendedFetch() is called for this result set.
HY010 Function sequence error. This SQLSTATE is returned for one or more of the following reasons:
  • SQLFetch() is called for a statement handle after SQLExtendedFetch() and before SQLCloseCursor().
  • The function is called prior to SQLPrepare() or SQLExecDirect().
  • 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:
  • Returning the numeric value (as numeric or string) for one or more columns causes the whole part of the 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.

Important: The associated cursor is undefined if this error is detected by Db2 for z/OS®. If the error is detected by Db2 for Linux®, UNIX, and Windows or by other IBM® relational database management systems, the cursor remains open and continues to advance on subsequent fetch calls.
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 fCType argument in SQLBindCol() and the SQL data type of the corresponding column require.
  • A call to SQLBindCol() was made for a column data type that is not supported by Db2 ODBC.

Example

The following example shows an application that uses SQLFetch() to retrieve data from bound columns of a result set.

Figure 1. An application that retrieves data from bound columns
/* ... */
/*******************************************************************
** main
*******************************************************************/
int
main( int argc, char * argv[] )
{
    SQLHENV         henv;
    SQLHDBC         hdbc;
    SQLHSTMT        hstmt;
    SQLRETURN       rc;
    SQLCHAR         sqlstmt[] = "SELECT deptname, location from org where
                                        division = 'Eastern'";
    struct { SQLINTEGER ind;
             SQLCHAR  s[15];
           } deptname, location;
  /* Macro to initialize server, uid and pwd */
    INIT_UID_PWD;
    /* Allocate an environment handle    */
    rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);    
    if (rc != SQL_SUCCESS)
        return (terminate(henv, rc));
    rc = DBconnect(henv, &hdbc);/* allocate a connect handle, and connect */
    if (rc != SQL_SUCCESS)
        return (terminate(henv, rc));
    rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
    rc = SQLExecDirect(hstmt, sqlstmt, SQL_NTS);
    rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER) deptname.s, 15,
                    &deptname.ind);
    rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) location.s, 15,
                    &location.ind);
printf("Departments in Eastern division:\n");
    printf("DEPTNAME       Location\n");
    printf("-------------- -------------\n");
    while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
        printf("%-14.14s %-14.14s \n", deptname.s, location.s);
    }
    if (rc != SQL_NO_DATA_FOUND)
        CHECK_HANDLE (SQL_HANDLE_STMT, hstmt, RETCODE);
    rc = SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
    rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
    printf("Disconnecting .....\n");
    rc = SQLDisconnect(hdbc);
    rc = SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
    rc = SQLFreeHandle (SQL_HANDLE_DBC, henv);
    if (rc != SQL_SUCCESS)
        return (terminate(henv, rc));
}                               /* end main */
/* ... */