SQLFetchScroll()
- Fetch the next row
SQLFetchScroll()
fetches the specified
rowset of data from the result set of a query and returns data for
all bound columns. Rowsets can be specified at an absolute position
or a relative position.
ODBC specifications for SQLFetchScroll()
ODBC specification level | In X/Open CLI CAE specification? | In ISO CLI specification? |
---|---|---|
3.0 | Yes | Yes |
Syntax
For 31-bit applications, use the following syntax:
SQLRETURN SQLFetchScroll (
SQLHSTMT StatementHandle,
SQLUSMALLINT FetchOrientation,
SQLINTEGER FetchOffset);
For 64-bit applications, use the following syntax:
SQLRETURN SQLFetchScroll (
SQLHSTMT StatementHandle,
SQLUSMALLINT FetchOrientation,
SQLLEN FetchOffset);
Function arguments
The following table lists the data type, use, and description for each argument in this function.
Data type | Argument | Use | Description |
---|---|---|---|
SQLHSTMT | hstmt | input | Specifies the statement handle from which to fetch data. |
SQLUSMALLINT | FetchOrientation | input | Type of fetch:
Db2 ODBC does not support SQL_FETCH_BOOKMARK. See Usage for more information. |
SQLINTEGER (31-bit) or SQLLEN (64-bit) 1 | FetchOffset | input | Number of the row to fetch. The interpretation
of this argument depends on the value of the FetchOrientation argument. See Usage for more information. |
Notes:
|
Usage
SQLFetchScroll()
returns
a specified rowset from a result set. Rowsets can be specified by
absolute or relative position. SQLFetchScroll()
can
be called only after a call that creates a result set and before the
cursor over that result set is closed. If any columns are bound, SQLFetchScroll()
returns
the data in those columns. If the application specifies a pointer
to a row status array or a buffer in which to return the number of
rows that were fetched, SQLFetchScroll()
returns
this information . Calls to SQLFetchScroll()
can
be mixed with calls to SQLFetch()
. An SQLFetch()
call
is equivalent to SQLFetchScroll()
with a FetchOrientation value
of SQL_FETCH_NEXT. SQLFetchScroll()
calls cannot
be mixed with SQLExtendedFetch()
calls.
How
to position the cursor: When a 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.
The rules for determining the start of the new rowset are shown in
the next section.
The following table defines the FetchOrientation values.
FetchOrientation value | Meaning |
---|---|
SQL_FETCH_NEXT | Return the next rowset. This is equivalent to
calling SQLFetch() . SQLFetchScroll() ignores
the value of FetchOffset. |
SQL_FETCH_PRIOR | Return the prior rowset. SQLFetchScroll() ignores
the value of FetchOffset. |
SQL_FETCH_RELATIVE | Return the rowset that is FetchOffset from the start of the current rowset. |
SQL_FETCH_ABSOLUTE | Return the rowset that starts at row FetchOffset. |
SQL_FETCH_FIRST | Return the first rowset in the result set. SQLFetchScroll() ignores
the value of FetchOffset. |
SQL_FETCH_LAST | Return the last complete rowset 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 rowset. If the rowset that is being fetched
by SQLFetchScroll()
goes beyond the end of the result
set, SQLFetchScroll()
returns a partial rowset. That
is, if S
is the starting row of the rowset, R
is
the rowset size, and L
is the length of the result
set, and S+R-1
is greater than L
,
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()
completes,
the rowset cursor is positioned on the first row of the rowset.
Fetch orientation notation | Meaning |
---|---|
BeforeStart | The block cursor is positioned before the start
of the result set. If the first row of the rowset is before the start
of the result set, SQLFetchScroll() returns SQL_NO_DATA. |
AfterEnd | The block cursor is positioned after the end
of the result set. If the first row of the rowset is after the end
of the result set, SQLFetchScroll() returns SQL_NO_DATA. |
CurrRowsetStart | The number of the first row in the current rowset. |
LastResultRow | The number of the last row in the result set. |
RowsetSize | The rowset size. |
FetchOffset | The value of the FetchOffset argument
in the SQLFetchScroll() call. |
Condition | First row of the new rowset |
---|---|
BeforeStart | 1 |
CurrRowsetStart + RowsetSize <= LastResultRow | CurrRowsetStart + RowsetSize |
CurrRowsetStart + RowsetSize > LastResultRow | AfterEnd |
AfterEnd | AfterEnd |
Condition | First row of the new rowset |
---|---|
BeforeStart | BeforeStart |
CurrRowsetStart =1 | BeforeStart |
1<CurrRowsetStart<=RowsetSize | 11 |
CurrRowsetStart>RowsetSize | CurrRowsetStart-RowsetSize |
AfterEnd and LastResultRow<RowsetSize | 11 |
AfterEnd and LastResultRow>=RowsetSize | LastResultRow-RowsetSize+1 |
Note:
|
Condition | First row of the new rowset |
---|---|
BeforeStart AND FetchOffset>0 | 1 |
AfterEnd ANDFetchOffset<0 | 1 |
BeforeStart ANDFetchOffset<=0 | BeforeStart |
CurrRowsetStart=1 ANDFetchOffset< 0 | BeforeStart |
CurrRowsetStart>1 ANDCurrRowsetStart+FetchOffset< 1 AND ABS(FetchOffset)>RowsetSize | BeforeStart |
CurrRowsetStart>1 ANDCurrRowsetStart+FetchOffset< 1 AND ABS(FetchOffset)<=RowsetSize | 12 |
1<=(CurrRowsetStart + FetchOffset)<=LastResultRow | CurrRowsetStart+FetchOffset |
(CurrRowsetStart + FetchOffset)>LastResultRow | AfterEnd |
AfterEnd AND FetchOffset>=0 | AfterEnd |
FetchOffset=0 | Unchanged3 |
Note:
|
Condition | First row of the new rowset |
---|---|
FetchOffset<0 AND ABS(FetchOffset)<=LastResultRow | LastResultRow+FetchOffset+1 |
FetchOffset<0 AND ABS(FetchOffset)>LastResultRow AND ABS(FetchOffset)>RowSetSize | BeforeStart |
FetchOffset<0 AND ABS(FetchOffset)>LastResultRow AND ABS(FetchOffset)<=RowSetSize | 11 |
FetchOffset=0 | BeforeStart |
1<=FetchOffset<=LastResultRow | FetchOffset |
FetchOffset>LastResultRow | AfterEnd |
Note:
|
Condition | First row of the new rowset |
---|---|
Any | 1 |
Condition | First row of the new rowset |
---|---|
RowsetSize<=LastResultRow | LastResultRow-RowsetSize+1 |
RowsetSize>LastResultRow | 1 |
Data in bound columns: SQLFetchScroll()
returns
data in bound columns in the same way as SQLFetch()
.
If no columns are bound, SQLFetchScroll()
does not
return data, but moves the block cursor to the specified position.
As with SQLFetch()
, you can use SQLGetData()
to
retrieve the values for each column.
Buffer addresses: SQLFetchScroll()
uses
the same formula to determine the address of data and length and indicator
buffers as SQLFetch()
.
Error handling: SQLFetchScroll()
returns
errors and warnings in the same manner as SQLFetch()
.
Return codes
SQLFetchScroll()
,
it returns one of the following values: - SQL_SUCCESS
- SQL_SUCCESS_WITH_INFO
- SQL_ERROR
- SQL_INVALID_HANDLE
- SQL_NO_DATA_FOUND
Diagnostics
The return code that is associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.
The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.
SQLSTATE | Description | Explanation |
---|---|---|
01000 | Warning. | Informational message. (Function returns SQL_SUCCESS_WITH_INFO.) |
01004 | Data truncated. | String or binary data that was returned for a column resulted in the truncation of non-blank character data or non-NULL binary data. String values are right truncated. (Function returns SQL_SUCCESS_WITH_INFO.) |
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 of
the following conditions was true:
|
01S07 | Fractional truncation. | The data that was returned for a column was truncated. For numeric data types, the fractional part of the number was truncated. For time or timestamp data types, or interval data types with a time component, the fractional portion of the time was truncated. |
07002 | Too many columns. | A column number that was specified in the binding of 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 that was specified by TargetType in SQLBindCol() . |
08S01 | Communication link failure. | The communication link between Db2 ODBC and the data source to which it was connected failed before the function completed processing. |
22001 | String data right truncation. | A variable-length bookmark that was returned for a row was truncated. |
22002 | Invalid output or indicator buffer specified. | NULL data was fetched into a column whose pcbValue,
which was set by SQLBindCol() , was a null pointer. |
22003 | Numeric value out of range. | Data was not returned because 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 an invalid date, time, or timestamp. |
22012 | Division by zero is invalid. | An arithmetic expression resulted in division by zero. |
22018 | Invalid character value for cast specification. | One of the following conditions occurred:
|
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 that was returned by SQLGetDiagRec() in
the *MessageText buffer describes the error
and its cause. |
HY001 | Memory allocation failure. | Db2 ODBC was unable to allocate memory required to support execution or completion of the function. Process-level memory might have been exhausted for the application process. Consult the operating system configuration for information on process-level memory limitations. |
HY008 | Operation was canceled. | Before the function completed execution, SQLCancel() was
called on StatementHandle from a different
thread in a multithreaded application. |
HY010 | Function sequence error. | One of the following conditions occurred:
|
HY106 | Fetch type out of range. | The value that was specified for the argument FetchOrientation was invalid. 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. |
HYC00 | Driver not capable. | The specified fetch type is not supported. The conversion
that is specified by the combination of TargetType in SQLBindCol() and
the SQL data type of the corresponding column is not supported. |