DB2 Version 10.1 for Linux, UNIX, and Windows
DB2 Version 10.1 for Linux, UNIX, and Windows
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.
SQLRETURN SQLFetch (SQLHSTMT StatementHandle); /* hstmt */| Data type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | input | Statement handle |
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().
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.
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.
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().
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().
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
| 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() |
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.
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.
| 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 for Linux, UNIX, and Windows. 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:
|
| 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(). |
None.
/* 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);
}