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()

Table 1. SQLFetchScroll() specifications
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.

Table 2. SQLFetchScroll() arguments
Data type Argument Use Description
SQLHSTMT hstmt input Specifies the statement handle from which to fetch data.
SQLUSMALLINT FetchOrientation input Type of fetch:
  • SQL_FETCH_NEXT
  • SQL_FETCH_PRIOR
  • SQL_FETCH_FIRST
  • SQL_FETCH_LAST
  • SQL_FETCH_ABSOLUTE
  • SQL_FETCH_RELATIVE

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:
  1. For 64-bit applications, the data type SQLINTEGER, which was used in previous versions of Db2, is still valid. However, for maximum application portability, using SQLLEN is recommended.

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.

Table 3. Meanings of 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.

Cursor positioning rules: The following information describes the rules for determining the start of the new rowset for each value of FetchOrientation. These rules use the following notation:
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.
The following table describes the rules for determining the start of the new rowset when the FetchOrientation value is SQL_FETCH_NEXT.
Table 4. Cursor position when SQLFetchScroll() parameter FetchOrientation is SQL_FETCH_NEXT
Condition First row of the new rowset
BeforeStart 1
CurrRowsetStart + RowsetSize <= LastResultRow CurrRowsetStart + RowsetSize
CurrRowsetStart + RowsetSize > LastResultRow AfterEnd
AfterEnd AfterEnd
The following table describes the rules for determining the start of the new rowset when the FetchOrientation value is SQL_FETCH_PRIOR.
Table 5. Cursor position when SQLFetchScroll() parameter FetchOrientation is SQL_FETCH_PRIOR
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:
  1. SQLFetchScroll() returns SQLSTATE 01S06 (attempt to fetch before the result set returned the first rowset) and SQL_SUCCESS_WITH_INFO.
The following table describes the rules for determining the start of the new rowset when the FetchOrientation value is SQL_FETCH_RELATIVE.
Table 6. Cursor position when SQLFetchScroll() parameter FetchOrientation is SQL_FETCH_RELATIVE
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:
  1. SQLFetchScroll() returns the same rowset that is returned when it is called with FetchOrientation set to SQL_FETCH_ABSOLUTE.
  2. SQLFetchScroll() returns SQLSTATE 01S06 (attempt to fetch before the result set returned the first rowset) and SQL_SUCCESS_WITH_INFO.
  3. This is a special command to fetch data again. If the cursor is a sensitive cursor, data is refetched from the base table. If the cursor is an insensitive cursor, the buffer remains unchanged. A cursor is insensitive for one of the following reasons:
    • The statement attribute for the associated statement is SQL_ATTR_CURSOR_SENSITIVITY or SQL_INSENSITIVE.
    • The query is read-only.
The following table describes the rules for determining the start of the new rowset when the FetchOrientation value is SQL_FETCH_ABSOLUTE.
Table 7. Cursor position when SQLFetchScroll() parameter FetchOrientation is SQL_FETCH_ABSOLUTE
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:
  1. SQLFetchScroll() returns SQLSTATE 01S06 (attempt to fetch before the result set returned the first rowset) and SQL_SUCCESS_WITH_INFO.
The following table describes the rules for determining the start of the new rowset when the FetchOrientation value is SQL_FETCH_FIRST.
Table 8. Cursor position when SQLFetchScroll() parameter FetchOrientation is SQL_FETCH_FIRST
Condition First row of the new rowset
Any 1
The following table describes the rules for determining the start of the new rowset when the FetchOrientation value is SQL_FETCH_LAST.
Table 9. Cursor position when SQLFetchScroll() parameter FetchOrientation is SQL_FETCH_LAST
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

After you call 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.

Table 10. SQLFetchScroll SQLSTATEs
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:
  • FetchOrientation was SQL_PRIOR
  • 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 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:
  • 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 C buffer and a value in the column could not be cast to a valid datetime 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 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:
  • The specified StatementHandle was not in an executed state. The function was called without a previous call of SQLExecDirect(), SQLExecute(), or a catalog function.
  • SQLExecute() or SQLExecDirect() was called for the StatementHandle and returned SQL_NEED_DATA. SQLFetchScroll() was called before data was sent for all data-at-execution parameters or columns.
  • SQLFetchScroll() was called for a StatementHandle after SQLFetch() was called, and before SQLFreeStmt() was called with the SQL_CLOSE option, or before SQLMoreResults() was called. The connection was to a down-level server.
  • 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 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.