| Specification: | DB2 CLI 5.0 | ODBC 3.0 | ISO CLI |
SQLFetchScroll() fetches the specified rowset of data from the result set and returns data for all bound columns. Rowsets can be specified at an absolute or relative position or by bookmark.
Syntax
SQLRETURN SQLFetchScroll (SQLHSTMT StatementHandle,
SQLSMALLINT FetchOrientation,
SQLINTEGER FetchOffset);
Function Arguments
Table 70. SQLFetchScroll Arguments
| Data Type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | input | Statement handle. |
| SQLUSMALLINT | FetchOrientation | input | Type of fetch:
|
| SQLINTEGER | FetchOffset | input | Number of the row to fetch. The interpretation of this argument depends on the value of the FetchOrientation argument. For more information, see Positioning the Cursor. |
Usage
Overview
SQLFetchScroll() returns a specified rowset from the result set. Rowsets can be specified by absolute or relative position or by bookmark. SQLFetchScroll() can be called only while a result set exists-that is, after a call that creates a result set and before the cursor over that result set is closed. If any columns are bound, it returns the data in those columns. If the application has specified a pointer to a row status array or a buffer in which to return the number of rows fetched, SQLFetchScroll() returns this information as well. Calls to SQLFetchScroll() can be mixed with calls to SQLFetch() but cannot be mixed with calls to SQLExtendedFetch().
When the result set is created, the cursor is positioned before the start of the result set. SQLFetchScroll() positions the block cursor based on the values of the FetchOrientation and FetchOffset arguments as shown in the following table. The exact rules for determining the start of the new rowset are shown in the next section.
The SQL_ATTR_ROW_ARRAY_SIZE statement attribute specifies the number of rows in the rowset. If the rowset being fetched by SQLFetchScroll() overlaps the end of the result set, SQLFetchScroll() 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.
After SQLFetchScroll() returns, the rowset cursor is positioned on the first row of the result set.
The following sections describe the exact rules for each value of FetchOrientation. These rules use the following notation:
SQL_FETCH_NEXT rules:
Table 71. SQL_FETCH_NEXT Rules:
| Condition | First row of new rowset |
|---|---|
| Before start | 1 |
| CurrRowsetStart + RowsetSize <= LastResultRow | CurrRowsetStart + RowsetSize |
| CurrRowsetStart + RowsetSize > LastResultRow | After end |
| After end | After end |
SQL_FETCH_PRIOR rules:
Table 72. SQL_FETCH_PRIOR Rules:
| Condition | First row of new rowset |
|---|---|
| Before start | Before start |
| CurrRowsetStart = 1 | Before start |
| 1 < CurrRowsetStart <= RowsetSize | 1 a |
| CurrRowsetStart > RowsetSize | CurrRowsetStart - RowsetSize |
| After end AND LastResultRow < RowsetSize | 1 a |
| After end AND LastResultRow >= RowsetSize | LastResultRow - RowsetSize + 1 |
SQL_FETCH_RELATIVE rules:
Table 73. SQL_FETCH_RELATIVE Rules:
| Condition | First row of new rowset |
|---|---|
| (Before start AND FetchOffset > 0) OR (After end AND FetchOffset < 0) | -- a |
| Before start AND FetchOffset <= 0 | Before start |
| CurrRowsetStart = 1 AND FetchOffset < 0 | Before start |
| CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND |FetchOffset| > RowsetSize | Before start |
| CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND |FetchOffset| <= RowsetSize | 1 b |
| 1 <= CurrRowsetStart + FetchOffset <= LastResultRow | CurrRowsetStart + FetchOffset |
| CurrRowsetStart + FetchOffset > LastResultRow | After end |
| After end AND FetchOffset >= 0 | After end |
SQL_FETCH_ABSOLUTE rules:
Table 74. SQL_FETCH_ABSOLUTE Rules:
| Condition | First row of new rowset |
|---|---|
| FetchOffset < 0 AND |FetchOffset| <= LastResultRow | LastResultRow + FetchOffset + 1 |
| FetchOffset < 0 AND |FetchOffset| > LastResultRow AND |FetchOffset| > RowsetSize | Before start |
| FetchOffset < 0 AND |FetchOffset| > LastResultRow AND |FetchOffset| <= RowsetSize | 1 a |
| FetchOffset = 0 | Before start |
| 1 <= FetchOffset <= LastResultRow | FetchOffset |
| FetchOffset > LastResultRow | After end |
SQL_FETCH_FIRST rules:
Table 75. SQL_FETCH_FIRST Rules:
| Condition | First row of new rowset |
|---|---|
| Any | 1 |
SQL_FETCH_LAST rules:
Table 76. SQL_FETCH_LAST Rules:
| Condition | First row of new rowset |
|---|---|
| RowsetSize <= LastResultRow | LastResultRow - RowsetSize + 1 |
| RowsetSize > LastResultRow | 1 |
SQL_FETCH_BOOKMARK rules:
Table 77. SQL_FETCH_BOOKMARK Rules:
| Condition | First row of new rowset |
|---|---|
| BookmarkRow + FetchOffset < 1 | Before start |
| 1 <= BookmarkRow + FetchOffset <= LastResultRow | BookmarkRow +FetchOffset |
| BookmarkRow + FetchOffset > LastResultRow | After end |
Returning Data in Bound Columns
SQLFetchScroll() returns data in bound columns in the same way as SQLFetch(). For more information see SQLFetch - Fetch Next Row.
If no columns are bound, SQLFetchScroll() does not return data but does move the block cursor to the specified position. As with SQLFetch(), you can use SQLGetData() to retrieve the information in this case.
Buffer Addresses
SQLFetchScroll() uses the same formula to determine the address of data and length/indicator buffers as SQLFetch(). For more information, see "Buffer Addresses" in SQLBindCol().
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. The array is allocated by the application and must have as many elements as are specified by the SQL_ATTR_ROW_ARRAY_SIZE statement attribute. Its values are set by SQLFetch(), SQLFetchScroll() SQLSetPos() (except when they have been called after the cursor has been positioned by SQLExtendedFetch()). If the value of the SQL_ATTR_ROW_STATUS_PTR statement attribute is a null pointer, these functions do not return the row status.
The contents of the row status array buffer are undefined if SQLFetch() or SQLFetchScroll() does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO.
The following values are returned in the row status array.
This value is not set by SQLFetch() or SQLFetchScroll().
The rows fetched buffer is used to return the number of rows fetched, including those rows for which no data was returned because an error occurred while they were being fetched. In other words, it is the number of rows for which the value in the row status array is not SQL_ROW_NOROW. The address of this buffer is specified with the SQL_ATTR_ROWS_FETCHED_PTR statement attribute. The buffer is allocated by the application. It is set by SQLFetch() and SQLFetchScroll(). If the value of the SQL_ATTR_ROWS_FETCHED_PTR statement attribute is a null pointer, these functions do not return the number of rows fetched. To determine the number of the current row in the result set, an application can call SQLGetStmtAttr() with the SQL_ATTR_ROW_NUMBER attribute.
The contents of the rows fetched buffer are undefined if SQLFetch() or SQLFetchScroll() does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, except when SQL_NO_DATA is returned, in which case the value in the rows fetched buffer is set to 0.
Error Handling
SQLFetchScroll() returns errors and warnings in the same manner as SQLFetch(); for more information see SQLFetch(), Error Handling. SQLExtendedFetch() returns errors in the same manner as SQLFetch() with the following exceptions:
Descriptors and SQLFetchScroll()
SQLFetchScroll() interacts with descriptors in the same manner as SQLFetch(). For more information see SQLFetch(), Descriptors and SQLFetch.
Return Codes
Diagnostics
The return code associated with each SQLSTATE value is SQL_ERROR, unless
noted otherwise. If an error occurs on a single column,
SQLGetDiagField() can be called with a DiagIdentifier of
SQL_DIAG_COLUMN_NUMBER to determine the column the error occurred on; and
SQLGetDiagField() can be called with a DiagIdentifier of
SQL_DIAG_ROW_NUMBER to determine the row containing that column.
Table 78. SQLFetchScroll SQLSTATEs
| SQLSTATE | Description | Explanation |
|---|---|---|
| 01000 | Warning. | Informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
| 01004 | Data truncated. | String or binary data returned for a column resulted in the truncation of non-blank character or non-NULL binary data. String values are right 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.) (This SQLSTATE is only returned when connected to DB2 CLI v2.) |
| 01S06 | Attempt to fetch before the result set returned the first rowset. | The requested rowset overlapped the start of the result set when the current position was beyond the first row, and either FetchOrientation was SQL_PRIOR, or FetchOrientation was SQL_RELATIVE with a negative FetchOffset whose absolute value was less than or equal to the current SQL_ATTR_ROW_ARRAY_SIZE. (Function returns SQL_SUCCESS_WITH_INFO.) |
| 01S07 | Fractional truncation. | The data returned for a column was truncated. For numeric data types, the fractional part of the number was truncated. For time, timestamp, and interval data types containing a time component, the fractional portion of the time was truncated. |
| 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. | A data value of a column in the result set could not be converted to the C data type specified by TargetType in SQLBindCol(). |
| 07009 | Invalid descriptor index. | Column 0 was bound and the SQL_USE_BOOKMARKS statement attribute was set to SQL_UB_OFF. |
| 08S01 | Communication link failure. | The communication link between DB2 CLI and the data source to which it was connected failed before the function completed processing. |
| 22001 | String data right truncation. | A variable-length bookmark returned for a row was truncated. |
| 22002 | Invalid output or indicator buffer specified. | NULL data was fetched into a column whose StrLen_or_IndPtr set by SQLBindCol() (or SQL_DESC_INDICATOR_PTR set by SQLSetDescField() or SQLSetDescRec()) was a null pointer. |
| 22003 | Numeric value out of range. | Returning the numeric value (as numeric or string) for one or more bound columns would have caused the whole (as opposed to fractional) part of the number to be truncated. |
| 22007 | Invalid datetime format. | A character column in the result set was bound to a date, time, or timestamp C structure, and a value in the column was, respectively, an invalid date, time, or timestamp. |
| 22012 | Division by zero is invalid. | A value from an arithmetic expression was returned which resulted in division by zero. |
| 22018 | Invalid character value for cast specification. | A character column in the result set was bound to a character C buffer and the column contained a character for which there was no representation in the character set of the buffer. A character column in the result set was bound to an approximate numeric C buffer and a value in the column could not be cast to a valid approximate numeric value. A character column in the result set was bound to an exact numeric C buffer and a value in the column could not be cast to a valid exact numeric value. A character column in the result set was bound to a datetime or interval C buffer and a value in the column could not be cast to a valid datetime or interval value. |
| 24000 | Invalid cursor state. | The StatementHandle was in an executed state but no result set was associated with the StatementHandle. |
| 40001 | Transaction rollback. | The transaction in which the fetch was executed was terminated to prevent deadlock. |
| HY000 | General error. | An error occurred for which there was no specific SQLSTATE. The error message returned by SQLGetDiagRec() in the *MessageText buffer describes the error and its cause. |
| HY001 | Memory allocation failure. | DB2 CLI was unable to allocate memory required to support execution or completion of the function. |
| HY008 | Operation was cancelled |
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. | The specified StatementHandle was not in an executed
state. The function was called without first calling
SQLExecDirect(), SQLExecute(), or a catalog
function.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called. SQLExecute() or SQLExecDirect() was called for the StatementHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns. SQLFetchScroll() was called for the StatementHandle after SQLFetch() was called, and was connected to a DB2 v2 or earlier server, and either before SQLFreeStmt() was called with the SQL_CLOSE option, or before SQLMoreResults() was called. SQLFetchScroll() was called for a StatementHandle after SQLExtendedFetch() was called and before SQLFreeStmt() with SQL_CLOSE was called. |
| HY106 | Fetch type out of range. | The value specified for the argument FetchOrientation was
invalid.
The argument FetchOrientation was SQL_FETCH_BOOKMARK, and the SQL_ATTR_USE_BOOKMARKS statement attribute was set to SQL_UB_OFF. The value of the SQL_CURSOR_TYPE statement attribute was SQL_CURSOR_FORWARD_ONLY and the value of argument FetchOrientation was not SQL_FETCH_NEXT. |
| HY107 | Row value out of range. | The value specified with the SQL_ATTR_CURSOR_TYPE statement attribute was SQL_CURSOR_KEYSET_DRIVEN, but the value specified with the SQL_ATTR_KEYSET_SIZE statement attribute was greater than 0 and less than the value specified with the SQL_ATTR_ROW_ARRAY_SIZE statement attribute. |
| HY111 | Invalid bookmark value. | The argument FetchOrientation was SQL_FETCH_BOOKMARK and the bookmark pointed to by the value in the SQL_ATTR_FETCH_BOOKMARK_PTR statement attribute was not valid or was a null pointer. |
| HYC00 | Driver not capable. | The specified fetch type is not supported.
The conversion specified by the combination of the TargetType in SQLBindCol() and the SQL data type of the corresponding column is not supported. |
Restrictions
None.
Example
See the README file in the sqllib\samples\cli (or sqllib/samples/cli) subdirectory for a list of appropriate samples.
References