IBM Books

Call Level Interface Guide and Reference

SQLFetch - Fetch Next Row

Purpose


Specification: DB2 CLI 1.1 ODBC 1.0 ISO CLI

SQLFetch() advances the cursor to the next row of the result set, and retrieves any bound columns.

Columns may be bound to:

When SQLFetch() is called, the appropriate data transfer is performed, along with any data conversion if conversion was indicated when the column was bound. The columns can also be received individually after the fetch, by calling SQLGetData().

SQLFetch() can only be called after a result set has been generated (using the same statement handle) by either executing a query, calling SQLGetTypeInfo() or calling a catalog function.

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

Syntax

SQLRETURN   SQLFetch         (SQLHSTMT          StatementHandle);  /* hstmt */

Function Arguments

Table 67. SQLFetch Arguments
Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle

Usage

SQLFetch() can only be called after a result set has been generated on the same statement handle. Before SQLFetch() is called 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() will fail.

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() could be called to obtain all of the columns individually. If the cursor is a multirow cursor (that is, the SQL_ATTR_ROW_ARRAY_SIZE is greater than 1), SQLGetData() can be called only if SQL_GD_BLOCK is returned when SQLGetInfo() is called with an InfoType of SQL_GETDATA_EXTENSIONS. Data in unbound columns is discarded when SQLFetch() advances the cursor to the next row. For fixed length data types, or small variable length data types, binding columns provides better performance than using SQLGetData().

Columns may be bound to:

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

If any bound storage buffer are not large enough to hold the data returned by SQLFetch(), the data will be 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 will contain the actual length 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 have been 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 (refer to the diagnostics section).

Truncation of graphic data types is treated the same as character data types, except that the rgbValue buffer is filled to the nearest multiple of two bytes that is still less than or equal to the cbValueMax specified in SQLBindCol(). Graphic (DBCS) data transferred between DB2 CLI and the application is not null-terminated if the C buffer type is SQL_C_CHAR (unless indicated by the PATCH1 initialization keyword, refer to Configuration Keywords for more information). If the buffer type is SQL_C_DBCHAR, then null-termination of graphic data does occur.

Truncation is also affected by the SQL_ATTR_MAX_LENGTH statement attribute. The application can specify that DB2 CLI should not report truncation by calling SQLSetStmtAttr() with SQL_ATTR_MAX_LENGTH and a value for the maximum length to return for any one column, and by allocating a rgbValue buffer of the same size (plus the null-terminator). If the column data is larger than the set maximum length, SQL_SUCCESS will be returned and the maximum length, not the actual length will be returned in pcbValue.

When all the rows have been retrieved from the result set, or the remaining rows are not needed, SQLFreeStmt() should be called to close the cursor and discard the remaining data and associated resources.

To retrieve multiple rows at a time, use SQLFetchScroll(). An application cannot mix SQLFetch() with SQLExtendedFetch() calls on the same statement handle. It can, however, mix SQLFetch() with SQLFetchScroll() calls on the same statement handle.

Positioning the Cursor

When the result set is created, the cursor is positioned before the start of the result set. SQLFetch() fetches the next rowset. It is equivalent to calling SQLFetchScroll() with FetchOrientation set to SQL_FETCH_NEXT. For more information see Scrollable Cursors.

The SQL_ATTR_ROW_ARRAY_SIZE statement attribute specifies the number of rows in the rowset. If the rowset being fetched by SQLFetch() overlaps the end of the result set, SQLFetch() returns a partial rowset. That is, if S + R-1 is greater than L, where S is the starting row of the rowset being fetched, R is the rowset size, and L is the last row in the result set, then only the first L-S+1 rows of the rowset are valid. The remaining rows are empty and have a status of SQL_ROW_NOROW.

See SQLFetchScroll(), Cursor Positioning Rules under SQL_FETCH_NEXT for more information.

After SQLFetch() returns, the current row is the first row of the rowset.

Row Status Array

SQLFetch() sets values in the row status array in the same manner as SQLFetchScroll(). For more information see SQLFetchScroll(), Row Status.

Rows Fetched Buffer

SQLFetch() returns the number of rows fetched in the rows fetched buffer in the same manner as SQLFetchScroll(). For more information see SQLFetchScroll(), Rows Fetched Buffer.

Error Handling

Errors and warnings can apply to individual rows or to the entire function. For more information about diagnostic records see SQLGetDiagField - Get a Field of Diagnostic Data.

Errors and Warnings on the Entire Function

If an error applies to the entire function, such as SQLSTATE HYT00 (Timeout expired) or SQLSTATE 24000 (Invalid cursor state), SQLFetch() returns SQL_ERROR and the applicable SQLSTATE. The contents of the rowset buffers are undefined and the cursor position is unchanged.

If a warning applies to the entire function, SQLFetch() returns SQL_SUCCESS_WITH_INFO and the applicable SQLSTATE. The status records for warnings that apply to the entire function are returned before the status records that apply to individual rows.

Errors and Warnings in Individual Rows

If an error (such as SQLSTATE 22012 (Division by zero)) or a warning ( such as SQLSTATE 01004 (Data truncated)) applies to a single row, SQLFetch():

SQLFetch() continues fetching rows until it has fetched all of the rows in the rowset. It returns SQL_SUCCESS_WITH_INFO unless an error occurs in every row of the rowset (not counting rows with status SQL_ROW_NOROW), in which case it returns SQL_ERROR. In particular, if the rowset size is 1 and an error occurs in that row, SQLFetch() returns SQL_ERROR.

SQLFetch() returns the status records in row number order. That is, it returns all status records for unknown rows (if any), then all status records for the first row (if any), then all status records for the second row (if any), and so on. The status records for each individual row are ordered according to the normal rules for ordering status records; for more information, see SQLGetDiagField(), Sequence of Status Records.

Descriptors and SQLFetch

The following sections describe how SQLFetch() interacts with descriptors.

Argument Mappings

The driver does not set any descriptor fields based on the arguments of SQLFetch().

Other Descriptor Fields

The following descriptor fields are used by SQLFetch():

Table 68. Descriptor Fields
Descriptor field Desc. Location Set through
SQL_DESC_ARRAY_SIZE ARD header SQL_ATTR_ROW_ARRAY_SIZE statement attribute
SQL_DESC_ARRAY_STATUS_PTR IRD header SQL_ATTR_ROW_STATUS_PTR statement attribute
SQL_DESC_BIND_OFFSET_PTR ARD header SQL_ATTR_ROW_BIND_OFFSET_PTR statement attribute
SQL_DESC_BIND_TYPE ARD header SQL_ATTR_ROW_BIND_TYPE statement attribute
SQL_DESC_COUNT ARD header ColumnNumber argument of SQLBindCol()
SQL_DESC_DATA_PTR ARD records TargetValuePtr argument of SQLBindCol()
SQL_DESC_INDICATOR_PTR ARD records StrLen_or_IndPtr argument in SQLBindCol()
SQL_DESC_OCTET_LENGTH ARD records BufferLength argument in SQLBindCol()
SQL_DESC_OCTET_LENGTH_PTR ARD records StrLen_or_IndPtr argument in SQLBindCol()
SQL_DESC_ROWS_PROCESSED_PTR IRD header SQL_ATTR_ROWS_FETCHED_PTR statement attribute
SQL_DESC_TYPE ARD records TargetType argument in SQLBindCol()

All descriptor fields can also be set through SQLSetDescField().

Separate Length and Indicator Buffers

Applications can bind a single buffer or two separate buffers to be used to hold length and indicator values. When an application calls SQLBindCol(), SQL_DESC_OCTET_LENGTH_PTR and SQL_DESC_INDICATOR_PTR fields of the ARD are set to the same address, which is passed in the StrLen_or_IndPtr argument. When an application calls SQLSetDescField() or SQLSetDescRec(), it can set these two fields to different addresses.

SQLFetch() determines whether the application has specified separate l ength and indicator buffers. In this case, when the data is not NULL, SQLFetch() sets the indicator buffer to 0 and returns the length in the length buffer. When the data is NULL, SQLFetch() sets the indicator buffer to SQL_NULL_DATA and does not modify the length buffer.

Return Codes

SQL_NO_DATA_FOUND is returned if there are no rows in the result set, or previous SQLFetch() calls have fetched all the rows from the result set.

If all the rows have been fetched, the cursor is positioned after the end of the result set.

Diagnostics

Table 69. SQLFetch SQLSTATEs
SQLSTATE Description Explanation
07009 Invalid descriptor index Column 0 was bound but bookmarks are not being used (the SQL_ATTR_USE_BOOKMARKS statement attribute was set to SQL_UB_OFF).
01004 Data truncated. The data returned for one or more columns was truncated. String values or numeric values are right truncated. (SQL_SUCCESS_WITH_INFO is returned if no error occurred.)
07002 Too many columns. A column number specified in the binding for one or more columns was greater than the number of columns in the result set.
07006 Invalid conversion. The data value could not be converted in a meaningful manner to the data type specified by fCType in SQLBindCol()
22002 Invalid output or indicator buffer specified. The pointer value specified for the argument pcbValue in SQLBindCol() was a null pointer and the value of the corresponding column is null. There is no means to report SQL_NULL_DATA. The pointer specified for the argument IndicatorValue in SQLBindFileToCol() was a null pointer and the value of the corresponding LOB column is NULL. There is no means to report SQL_NULL_DATA.
22003 Numeric value out of range. Returning the numeric value (as numeric or string) for one or more columns would have caused 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 was returned which resulted in division by zero.
Note:The associated cursor is undefined if this error is detected by DB2 Universal Database. If the error was detected by DB2 CLI or by other IBM RDBMSs, the cursor will remain open and continue to advance on subsequent fetch calls.

22005 Error in assignment. A returned value was incompatible with the data type of binding.

A returned LOB locator was incompatible with the data type of the bound column.

22007 Invalid datetime format. Conversion from character a string to a datetime format was indicated, but an invalid string representation or value was specified, or the value was an invalid date.

The value of a date, time, or timestamp does not conform to the syntax for the specified data type.

22008 Datetime field overflow. Datetime field overflow occurred; for 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 was returned which resulted in division by zero.
24000 Invalid cursor state. The previous SQL statement executed on the statement handle was not a query.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
428A1 Unable to access a file referenced by a host file variable. This can be raised for any of the following scenarios. The associated reason code in the text identifies the particular error:
  • 01 - The file name length is invalid or the file name and/or the path has an invalid format.
  • 02 - The file option is invalid. It must have one of the following values:
      SQL_FILE_READ      -read from an existing file
      SQL_FILE_CREATE    -create a new file for write
      SQL_FILE_OVERWRITE -overwrite an existing file.
                          If the file does not exist,
                          create the file.
      SQL_FILE_APPEND    -append to an existing file.
                          If the file does not exist,
                          create the file.
    
  • 03 - The file cannot be found.
  • 04 - The SQL_FILE_CREATE option was specified for a file with the same name as an existing file.
  • 05 - Access to the file was denied. The user does not have permission to open the file.
  • 06 - Access to the file was denied. The file is in use with incompatible modes. Files to be written to are opened in exclusive mode.
  • 07 - Disk full was encountered while writing to the file.
  • 08 - Unexpected end of file encountered while reading from the file.
  • 09 - A media error was encountered while accessing the file.
54028 The maximum number of concurrent LOB handles has been reached. Maximum LOB locator assigned.

The maximum number of concurrent LOB locators has been reached. A new locator can not be assigned.

56084 LOB data is not supported in DRDA. LOBs not supported on DRDA.

LOB columns cannot either be selected or updated when connecting to DRDA servers (using DB2 Connect).

58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008 Operation canceled.

Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle.

The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application.

HY010 Function sequence error. SQLFetch() was called for an StatementHandle after SQLExtendedFetch() was called and before SQLFreeStmt() had been called with the SQL_CLOSE option.

The function was called prior to calling SQLPrepare() or SQLExecDirect() for the StatementHandle.

The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.

The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.

HY013 Unexpected memory handling error. DB2 CLI was unable to access memory required to support execution or completion of the function.
HY092 Option type out of range. The FileOptions argument of a previous SQLBindFileToCol() operation was not valid.
HYC00 Driver not capable. DB2 CLI or the data source does not support the conversion specified by the combination of the fCType in SQLBindCol() or SQLBindFileToCol() and the SQL data type of the corresponding column.

A call to SQLBindCol() was made for a column data type which is not supported by DB2 CLI.

HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

Restrictions

None.

CLI Sample fetch.c

/* From CLI sample fetch.c */
/* ... */
/*******************************************************************
** main
*******************************************************************/
int main( int argc, char * argv[] ) {
 
    SQLHANDLE henv, hdbc, hstmt ;
    SQLRETURN rc ;
 
    SQLCHAR * sqlstmt =
       "SELECT deptname, location from org where division = 'Eastern'" ;
 
    struct { SQLINTEGER ind ;
             SQLCHAR s[15] ;
           } deptname, location ;
 
/* ... */
 
    /* macro to initalize 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 ) ) ;
 
    /* allocate a connect handle, and connect */
    rc = DBconnect( henv, &hdbc ) ;
    if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
 
    rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
 
    rc = SQLExecDirect( hstmt, sqlstmt, SQL_NTS ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol( hstmt, 1, SQL_C_CHAR, deptname.s, 15, &deptname.ind ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol( hstmt, 2, SQL_C_CHAR, location.s, 15, &location.ind ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    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, rc ) ;
 
    /*  Commit the changes.  */
    rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
 
    /*  Disconnect and free up CLI resources.  */
 
    rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    printf( "\n>Disconnecting .....\n" ) ;
    rc = SQLDisconnect( hdbc ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
 
    rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ;
    CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
 
    rc = SQLFreeHandle( SQL_HANDLE_ENV,  henv ) ;
    if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
 
    return( SQL_SUCCESS ) ;
 
}                                  /* end main */
 

References


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]