SQLFetch function (CLI) - Fetch next row

Purpose

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

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

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.

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

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

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

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);
  }