Call Level Interface Guide and Reference

SQLExtendedFetch - Extended Fetch (Fetch Array of Rows)

Status of this Function since DB2 CLI Version 5
Note:
In ODBC version 3, SQLExtendedFetch() has been deprecated and replaced with SQLFetchScroll(); see SQLFetchScroll - Fetch Rowset and Return Data for All Bound Columns for more information.
Although this version of DB2 CLI continues to support SQLExtendedFetch(), we recommend that you begin using SQLFetchScroll() in your DB2 CLI programs so that they conform to the latest standards.
See DB2 CLI Functions Deprecated for Version 5 for more information on this and other deprecated functions.

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

SQLExtendedFetch() extends the function of SQLFetch() by returning a block of data containing multiple rows (called a rowset), in the form of a array, for each bound column. The size of the rowset is determined by the SQL_ROWSET_SIZE attribute on an SQLSetStmtAttr() call.

To fetch one row of data at a time, an application should call SQLFetch().

For more description on block or array retrieval, refer to Retrieving a Result Set into an Array.

Syntax

SQLRETURN   SQLExtendedFetch (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLUSMALLINT      FetchOrientation,  /* fFetchType */
                SQLINTEGER        FetchOffset,       /* irow */
                SQLUINTEGER  *FAR RowCountPtr,       /* pcrow */
                SQLUSMALLINT *FAR RowStatusArray);   /* rgfRowStatus */

Function Arguments

Table 63. SQLExtendedFetch Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle Input Statement handle.
SQLUSMALLINT FetchOrientation Input Direction and type of fetch. DB2 CLI only supports the fetch direction SQL_FETCH_NEXT; that is, forward only cursor direction. The next array (rowset) of data is retrieved.
SQLINTEGER FetchOffset Input Reserved for future use.
SQLUINTEGER * RowCountPtr Output Number of the rows actually fetched. If an error occurs during processing, RowCountPtr points to the ordinal position of the row (in the rowset) that precedes the row where the error occurred. If an error occurs retrieving the first row RowCountPtr points to the value 0.
SQLUSMALLINT * RowStatusArray Output An array of status values. The number of elements must equal the number of rows in the rowset (as defined by the SQL_ROWSET_SIZE attribute). A status value for each row fetched is returned:

SQL_ROW_SUCCESS

If the number of rows fetched is less than the number of elements in the status array (i.e. less than the rowset size), the remaining status elements are set to SQL_ROW_NOROW.
DB2 CLI cannot detect whether a row has been updated or deleted since the start of the fetch. Therefore, the following ODBC defined status values will not be reported:

SQL_ROW_DELETED
SQL_ROW_UPDATED

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLUSMALLINT	FetchOrientation	Input	Direction and type of fetch. DB2 CLI only supports the fetch direction SQL_FETCH_NEXT; that is, forward only cursor direction. The next array (rowset) of data is retrieved.
SQLINTEGER	FetchOffset	Input	Reserved for future use.
SQLUINTEGER *	RowCountPtr	Output	Number of the rows actually fetched. If an error occurs during processing, `RowCountPtr` points to the ordinal position of the row (in the rowset) that precedes the row where the error occurred. If an error occurs retrieving the first row `RowCountPtr` points to the value 0.
SQLUSMALLINT *	RowStatusArray	Output	An array of status values. The number of elements must equal the number of rows in the rowset (as defined by the SQL_ROWSET_SIZE attribute). A status value for each row fetched is returned: SQL_ROW_SUCCESS If the number of rows fetched is less than the number of elements in the status array (i.e. less than the rowset size), the remaining status elements are set to SQL_ROW_NOROW. DB2 CLI cannot detect whether a row has been updated or deleted since the start of the fetch. Therefore, the following ODBC defined status values will not be reported: SQL_ROW_DELETED SQL_ROW_UPDATED

Usage

SQLExtendedFetch() is used to perform an array fetch of a set of rows. An application specifies the size of the array by calling SQLSetStmtAttr() with the SQL_ROWSET_SIZE attribute.

Before SQLExtendedFetch() is called the first time, the cursor is positioned before the first row. After SQLExtendedFetch() is called, the cursor is positioned on the row in the result set corresponding to the last row element in the rowset just retrieved.

For any columns in the result set that have been bound via the SQLBindCol() or SQLBindFileToCol() function, DB2 CLI converts the data for the bound columns as necessary and stores it in the locations bound to these columns. As mentioned in section Retrieving a Result Set into an Array, the result set can be bound in a column-wise or row-wise fashion.

For column-wise binding of application variables:
To bind a result set in column-wise fashion, an application specifies SQL_BIND_BY_COLUMN for the SQL_ATTR_BIND_TYPE statement attribute. (This is the default value.) Then the application calls the SQLBindCol() function.
When the application calls SQLExtendedFetch(), data for the first row is stored at the start of the buffer. Each subsequent row of data is stored at an offset of cbValueMax bytes (argument on SQLBindCol() call ) or, if the associated C buffer type is fixed width (such as SQL_C_LONG), at an offset corresponding to that fixed length from the data for the previous row.
For each bound column, the number of bytes available to return for each element is stored in the pcbValue array buffer (deferred output argument on SQLBindCol()) buffer bound to the column. The number of bytes available to return for the first row of that column is stored at the start of the buffer, and the number of bytes available to return for each subsequent row is stored at an offset of sizeof(SQLINTEGER) bytes from the value for the previous row. If the data in the column is NULL for a particular row, the associated element in the pcbValue array is set to SQL_NULL_DATA.
For column-wise binding of file references:
The StringLength and IndicatorValue pointers on SQLBindFileToCol() are pointers to output arrays. The actual length of the file and the associated indicator value for the first row is stored at the start of the StringLength and IndicatorValue arrays respectively. File lengths and indicator values for subsequent rows are written to these arrays at an offset of sizeof(SQLINTEGER) bytes from the previous row.
For row-wise binding of application variables:
The application needs to first call SQLSetStmtAttr() with the SQL_ATTR_BIND_TYPE attribute, with the vParam argument set to the size of the structure capable of holding a single row of retrieved data and the associated data lengths for each column data value.
For each bound column, the first row of data is stored at the address given by the rgbValue supplied on the SQLBindCol() call for the column and each subsequent row of data at an offset of vParam bytes (used on the SQLSetStmtAttr() call) from the data for the previous row.
For each bound column, the number of bytes available to return for the first row is stored at the address given by the pcbValue argument supplied on the SQLBindCol() call, and the number of bytes available to return for each subsequent row at an offset of vParam bytes from address containing the value for the previous row.

Row-wise binding of file references is not supported.

If SQLExtendedFetch() returns an error that applies to the entire rowset, the SQL_ERROR function return code is reported with the appropriate SQLSTATE. The contents of the rowset buffer are undefined and the cursor position is unchanged.

If an error occurs that applies to a single row:

the corresponding element in the RowStatusArray array for the row is set to SQL_ROW_ERROR
an SQLSTATE of 01S01 is added to the list of errors that can be obtained using SQLError()
zero or more additional SQLSTATEs, describing the error for the current row, are added to the list of errors that can be obtained using SQLError()

An SQL_ROW_ERROR in the RowStatusArray array only indicates that there was an error with the corresponding element; it does not indicate how many SQLSTATEs were generated. Therefore, SQLSTATE 01S01 is used as a separator between the resulting SQLSTATEs for each row. DB2 CLI continues to fetch the remaining rows in the rowset and returns SQL_SUCCESS_WITH_INFO as the function return code. After SQLExtendedFetch() returns, for each row encountering an error there is an SQLSTATE of 01S01 and zero or more additional SQLSTATEs indicating the error(s) for the current row, retrievable via SQLError(). Individual errors that apply to specific rows do not affect the cursor which continues to advance.

The number of elements in the RowStatusArray array output buffer must equal the number of rows in the rowset (as defined by the SQL_ROWSET_SIZE statement attribute). If the number of rows fetched is less than the number of elements in the status array, the remaining status elements are set to SQL_ROW_NOROW.

An application cannot mix SQLExtendedFetch() with SQLFetch() calls.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE
SQL_NO_DATA_FOUND

Diagnostics

Table 64. SQLExtendedFetch SQLSTATEs

SQLSTATE Description Explanation
01004 Data truncated. The data returned for one or more columns was truncated. (Function returns SQL_SUCCESS_WITH_INFO.)
01S01 Error in row. An error occurred while fetching one or more rows. (Function returns SQL_SUCCESS_WITH_INFO.)
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 the bound column.
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.
HY010 Function sequence error. SQLExtendedFetch() was called for an StatementHandle after SQLFetch() 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.
HY106 Fetch type out of range. The value specified for the argument FetchOrientation was not recognized.
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.
The specified fetch type is recognized, but not supported.
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.

Example

Refer to SQLFetchScroll - Fetch Rowset and Return Data for All Bound Columns.

References

[ Top of Page | Previous Page | Next Page ]