SQLFetch function (CLI) - Fetch next row

Advances the cursor to the next row of the result set, and retrieves any bound columns.

Specification:

  • CLI 1.1
  • ODBC 1.0
  • ISO CLI
Columns can be bound to:
  • application storage
  • LOB locators
  • LOB file references

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.

Syntax

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

Function arguments

Table 1. 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.

(Not all Db2® data sources support SQL_GD_BLOCK.) 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().

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 is 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 CLI and the application is not null-terminated if the C buffer type is SQL_C_CHAR (unless the CLI/ODBC configuration keyword PATCH1 includes the value 64). 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 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, SQLCloseCursor() or SQLFreeStmt() with an option of SQL_CLOSE or SQL_DROP should be called to close the cursor and discard the remaining data and associated resources.

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. Note that SQLExtendedFetch() has been deprecated and replaced with SQLFetchScroll().

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.

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.

Refer to the cursor positioning rules of SQL_FETCH_NEXT for SQLFetchScroll() 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() and SQLBulkOperations(). The row status array is used to return the status of each row in the rowset. The address of this array is specified with the SQL_ATTR_ROW_STATUS_PTR statement attribute.

Rows fetched buffer

SQLFetch() returns the number of rows fetched in the rows fetched buffer including those rows for which no data was returned. The address of this buffer is specified with the SQL_ATTR_ROWSFETCHED_PTR statement attribute. The buffer is set by SQLFetch() and SQLFetchScroll().

Error handling

Errors and warnings can apply to individual rows or to the entire function. They can be retrieved using the SQLGetDiagField() function.

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.
Note: SQLSTATE HYT00 applies only to .Net applications.

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() returns SQL_SUCCESS_WITH_INFO, unless an error occurs in every row, in which case SQL_ERROR is returned. SQLFetch() also:
  • Sets the corresponding element of the row status array to SQL_ROW_ERROR for errors or SQL_ROW_SUCCESS_WITH_INFO for warnings.
  • Adds zero or more status records containing SQLSTATEs for the error or warning.
  • Sets the row and column number fields in the status records. If SQLFetch() cannot determine a row or column number, it sets that number to SQL_ROW_NUMBER_UNKNOWN or SQL_COLUMN_NUMBER_UNKNOWN. If the status record does not apply to a particular column, SQLFetch() sets the column number to SQL_NO_COLUMN_NUMBER.

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, described in SQLGetDiagField().

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 2. 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 length 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_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_STILL_EXECUTING
  • SQL_ERROR
  • SQL_INVALID_HANDLE
  • SQL_NO_DATA_FOUND

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 3. SQLFetch SQLSTATEs
SQLSTATE Description Explanation
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()
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).
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. If the error was detected by 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, the path or both 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.
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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY008 Operation was Canceled. Asynchronous processing was enabled for StatementHandle. The function was called and before it completed execution, SQLCancel() was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle.
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 before 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. 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 CLI.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetStmtAttr().
Note: This SQLSTATE applies only to .Net applications.

Restrictions

None.

Example

  /* fetch each row and display */
  cliRC = SQLFetch(hstmt);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  if (cliRC == SQL_NO_DATA_FOUND)
  {
    printf("\n  Data not found.\n");
  }
  while (cliRC != SQL_NO_DATA_FOUND)
  {
    printf("    

    /* fetch next row */
    cliRC = SQLFetch(hstmt);
    STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
  }