SQLFetchScroll--Fetch row set and return data for all bound columns

Purpose

Specification:

DB2 CLI 5.0

ODBC 3.0

SQLFetchScroll() fetches the specified row set of data from the result set and returns data for all bound columns. Row sets can be specified at an absolute or relative position.

Syntax

SQLRETURN   SQLFetchScroll  (
                  SQLHSTMT          StatementHandle,
                  SQLSMALLINT       FetchOrientation,
                  SQLINTEGER        FetchOffset);

Function arguments

ǥ 50. SQLFetchScroll arguments

Data type	Argument	Use	Description
SQLHSTMT	`StatementHandle`	input	Statement handle
SQLSMALLINT	`FetchOrientation`	input	Type of fetch: SQL_FETCH_NEXT SQL_FETCH_PRIOR SQL_FETCH_FIRST SQL_FETCH_LAST SQL_FETCH_ABSOLUTE SQL_FETCH_RELATIVE
SQLINTEGER	`FetchOffset`	input	Number of the row to fetch. The interpretation of this argument depends on the value of the `FetchOrientation` argument.

Usage

SQLFetchScroll() returns a specified row set from the result set. Row sets can be specified by absolute or relative position. 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().

Positioning the cursor

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 list. The exact rules for determining the start of the new row set are shown in the next section.

FetchOrientation: Meaning
SQL_FETCH_NEXT: Return the next row set. This is equivalent to calling SQLFetch(). SQLFetchScroll() ignores the value of FetchOffset.
SQL_FETCH_PRIOR: Return the prior row set. SQLFetchScroll() ignores the value of FetchOffset.
SQL_FETCH_RELATIVE: Return the row set FetchOffset from the start of the current row set.
SQL_FETCH_ABSOLUTE: Return the row set starting at row FetchOffset.
SQL_FETCH_FIRST: Return the first row set in the result set. SQLFetchScroll() ignores the value of FetchOffset.
SQL_FETCH_LAST: Return the last complete row set in the result set. SQLFetchScroll() ignores the value of FetchOffset.

The SQL_ATTR_ROW_ARRAY_SIZE statement attribute specifies the number of rows in the row set. If the row set being fetched by SQLFetchScroll() overlaps the end of the result set, SQLFetchScroll() returns a partial row set. That is, if S + R - 1 is greater than L, where S is the starting row of the row set being fetched, R is the row set size, and L is the last row in the result set, then only the first L - S + 1 rows of the row set are valid. The remaining rows are empty and have a status of SQL_ROW_NOROW.

After SQLFetchScroll() returns, the row set cursor is positioned on the first row of the result set.

Cursor positioning rules

The following sections describe the exact rules for each value of FetchOrientation. These rules use the following notation:

Before start: The block cursor is positioned before the start of the result set. If the first row of the new row set is before the start of the result set, SQLFetchScroll() returns SQL_NO_DATA.
After end: The block cursor is positioned after the end of the result set. If the first row of the new row set is after the end of the result set, SQLFetchScroll() returns SQL_NO_DATA.
CurrRowsetStart: This is the number of the first row in the current row set.
LastResultRow: This is the number of the last row in the result set.
RowsetSize: This is the row set size.
FetchOffset: This is the value of the FetchOffset argument.

SQL_FETCH_NEXT rules

ǥ 51. SQL_FETCH_NEXT rules:

Condition	First row of new row set
Before start	1
CurrRowsetStart + RowsetSize <= LastResultRow	CurrRowsetStart + RowsetSize
CurrRowsetStart + RowsetSize > LastResultRow	After end
After end	After end

SQL_FETCH_PRIOR rules

ǥ 52. SQL_FETCH_PRIOR rules:

Condition	First row of new row set
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	LastResult - RowRowsetSize + 1

a: SQLFetchScroll() returns SQLSTATE 01S06 (Attempt to fetch before the result set returned the first row set) and SQL_SUCCESS_WITH_INFO.

SQL_FETCH_RELATIVE rules

ǥ 53. SQL_FETCH_RELATIVE rules:

Condition	First row of new row set
(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

a: SQLFetchScroll() returns the same row set as if it is called with FetchOrientation set to SQL_FETCH_ABSOLUTE. For more information, see the SQL_FETCH_ABSOLUTE section.
b: SQLFetchScroll() returns SQLSTATE 01S06 (Attempt to fetch before the result set returned the first row set.) and SQL_SUCCESS_WITH_INFO.

SQL_FETCH_ABSOLUTE rules

ǥ 54. SQL_FETCH_ABSOLUTE rules:

Condition	First row of new row set
`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

a: SQLFetchScroll() returns SQLSTATE 01S06 (Attempt to fetch before the result set returned the first row set.) and SQL_SUCCESS_WITH_INFO.

SQL_FETCH_FIRST rules:

ǥ 55. SQL_FETCH_FIRST rules:

Condition	First row of new row set
Any	1

SQL_FETCH_LAST rules

ǥ 56. SQL_FETCH_LAST rules:

Condition	First row of new row set
RowsetSize <= LastResultRow	LastResultRow - RowsetSize + 1
RowsetSize > LastResultRow	1

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 SQLBindCol--Bind a column to an application variable.

Row status array

The row status array is used to return the status of each row in the row set. 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() and SQLFetchScroll(). 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.

Row status array value: Description
SQL_ROW_SUCCESS: The row is successfully fetched.
SQL_ROW_SUCCESS_WITH_INFO: The row is successfully fetched. However, a warning is returned about the row.
SQL_ROW_ERROR: An error occurred while fetching the row.
SQL_ROW_NOROW: The row set overlapped the end of the result set, and no row returns that corresponds to this element of the row status array.

Rows fetched buffer

The rows fetched buffer is used to return the number of rows fetched, including those rows for which no data returns because an error occurred while they were being fetched. 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.

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_NO_DATA
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

ǥ 57. SQLFetchScroll SQLSTATEs

SQLSTATE	Description	Explanation
01000	Warning	Informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
01004	Data truncated.	The data returned for one or more columns is truncated. String values or numeric values are right truncated. (SQL_SUCCESS_WITH_INFO is returned if no error occurred.)
01S06	Attempted to fetch before the result set returned the first row set.	The requested row set overlapped the start of the result set when the current position is beyond the first row, and either FetchOrientation is SQL_PRIOR, or FetchOrientation is SQL_RELATIVE with a negative FetchOffset whose absolute value is less than or equal to the current SQL_ATTR_ROW_ARRAY_SIZE. (Function returns SQL_SUCCESS_WITH_INFO.)
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()` is a null pointer and the value of the corresponding 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 bound columns would have caused the whole (as opposed to fractional) part of the number to be truncated.
24000	Invalid cursor state.	The `StatementHandle` is in an executed state, but no result set is associated with the `StatementHandle`.
HY000	General error.	An error occurred for which there is 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 is unable to allocate memory required to support execution or completion of the function.
HY010	Function sequence error.	The function is called prior to calling `SQLPrepare()` or `SQLExecDirect()` for the `StatementHandle`.
HY106	Fetch type out of range.	The value specified for the argument `FetchOrientation` is not valid. The value of the SQL_CURSOR_TYPE statement attribute is SQL_CURSOR_FORWARD_ONLY and the value of argument `FetchOrientation` is not SQL_FETCH_NEXT.
HY107	Row value out of range.	The value specified with the SQL_ATTR_CURSOR_TYPE statement attribute is SQL_CURSOR_KEYSET_DRIVEN, but the value specified with the SQL_ATTR_KEYSET_SIZE statement attribute is greater than 0 and less than the value specified with the SQL_ATTR_ROW_ARRAY_SIZE statement attribute.

Restrictions

None.

Related reference