| 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
| 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:
SQLBindCol() is used to bind application storage to the column. Data will be transferred from the server to the application at fetch time. Length of the available data to return is also set.
SQLBindCol() is used to bind LOB locators to the column. Only the LOB locator (4 bytes) will be transferred from the server to the application at fetch time.
Once an application receives a locator it can be used 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 were created (even when the cursor moves to another row), or until it is freed using the FREE LOCATOR statement.
SQLBindFileToCol() is used to bind a file to a LOB column. DB2 CLI will write the data directly to a file, and update the StringLength and IndicatorValue buffers specified on SQLBindFileToCol().
If the data value for the column is NULL and SQLBindCol() was used, SQL_NULL_DATA is stored in the pcbValue buffer specified on SQLBindCol().
If the data value for the column is NULL and SQLBindFileToCol() was used, then IndicatorValue will be set to SQL_NULL_DATA and StringLength to 0.
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.
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() 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.
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():
| 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
| 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.
| ||
| 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. | ||
| 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.
/* 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