SQLExtendedFetch - Fetch array of rows

SQLExtendedFetch - Fetch array of rows

SQLExtendedFetch() extends the function of SQLFetch() by returning a block of data that contains multiple rows (called a rowset) in the form of an 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().

Syntax
SQLRETURN   SQLExtendedFetch (SQLHSTMT          StatementHandle,
                              SQLSMALLINT       FetchOrientation,
                              SQLINTEGER        FetchOffset,
                              SQLINTEGER        *RowCountPtr,
                              SQLSMALLINT       *RowStatusArray);
Function arguments

Table 1. SQLExtendedFetch arguments
Data type Argument Use Description

SQLHSTMT StatementHandle Input Statement handle.
SQLSMALLINT FetchOrientation Input Fetch orientation. See Table 2 for possible values.
SQLINTEGER FetchOffset Input Row offset for relative positioning.
SQLINTEGER * 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.
SQLSMALLINT * 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 (that is, less than the rowset size), the remaining status elements are set to SQL_ROW_NOROW. DB2^® UDB CLI cannot detect whether a row has been updated or deleted since the start of the fetch. Therefore, the following ODBC defined status values are not 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 by the SQLBindCol() function, DB2 UDB CLI converts the data for the bound columns as necessary and stores it in the locations bound to these columns. The result set must be bound in a row-wise fashion. This means that the values for all the columns of the first row are contiguous, followed by the values of the second row, and so on. Also, if indicator variables are used, they are all returned in one contiguous storage location.
When using this procedure to retrieve multiple rows, all columns must be bound, and the storage must be contiguous. When using this function to retrieve rows from an SQL procedure result set, only the SQL_FETCH_NEXT orientation is supported. The user is responsible for allocating enough storage for the number of rows that are specified in SQL_ROWSET_SIZE.
The cursor must be a scrollable cursor for SQLExtendedFetch() to use any orientation other than SQL_FETCH_NEXT. See SQLSetStmtAttr - Set a statement attribute for information about setting the SQL_ATTR_CURSOR_SCROLLABLE attribute.

Return codes

SQL_SUCCESS

SQL_SUCCESS_WITH_INFO

SQL_ERROR

SQL_INVALID_HANDLE

SQL_NO_DATA_FOUND

Error conditions

Table 2. SQLExtendedFetch SQLSTATEs
SQLSTATE Description Explanation

HY009 Argument value that is not valid
The argument value RowCountPtr or RowStatusArray is a null pointer.
The value specified for the argument FetchOrientation is not recognized.
HY010 Function sequence error SQLExtendedFetch() is called for an StatementHandle after SQLFetch() is called and before SQLFreeStmt() has been called with the SQL_CLOSE option.
The function is called before calling SQLPrepare() or SQLExecDirect() for the StatementHandle.
The function is called while in a data-at-processing (SQLParamData(), SQLPutData()) operation.

Restrictions

None.

References

SQLBindCol - Bind a column to an application variable

SQLExecute - Execute a statement

SQLExecDirect - Execute a statement directly

SQLFetch - Fetch next row

Parent topic:
DB2 UDB CLI functions

Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLSMALLINT	FetchOrientation	Input	Fetch orientation. See Table 2 for possible values.
SQLINTEGER	FetchOffset	Input	Row offset for relative positioning.
SQLINTEGER *	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.
SQLSMALLINT *	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 (that is, less than the rowset size), the remaining status elements are set to SQL_ROW_NOROW. DB2^® UDB CLI cannot detect whether a row has been updated or deleted since the start of the fetch. Therefore, the following ODBC defined status values are not reported: SQL_ROW_DELETED SQL_ROW_UPDATED

SQLSTATE	Description	Explanation
HY009	Argument value that is not valid	The argument value RowCountPtr or RowStatusArray is a null pointer. The value specified for the argument FetchOrientation is not recognized.
HY010	Function sequence error	`SQLExtendedFetch()` is called for an StatementHandle after `SQLFetch()` is called and before `SQLFreeStmt()` has been called with the SQL_CLOSE option. The function is called before calling `SQLPrepare()` or `SQLExecDirect()` for the StatementHandle. The function is called while in a data-at-processing (`SQLParamData()`, `SQLPutData()`) operation.