IBM Books

Call Level Interface Guide and Reference

SQLFetchScroll - Fetch Rowset and Return Data for All Bound Columns

Purpose


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:
  • SQL_FETCH_NEXT
  • SQL_FETCH_PRIOR
  • SQL_FETCH_FIRST
  • SQL_FETCH_LAST
  • SQL_FETCH_ABSOLUTE
  • SQL_FETCH_RELATIVE
  • SQL_FETCH_BOOKMARK
For more information, see Positioning the Cursor.
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().

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

FetchOrientation
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 FetchOffset from the start of the current rowset.

SQL_FETCH_ABSOLUTE
Return the rowset starting 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.

SQL_FETCH_BOOKMARK
Return the rowset FetchOffset rows from the bookmark specified by the SQL_ATTR_FETCH_BOOKMARK_PTR statement attribute.

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.

Cursor Positioning Rules

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

FetchOrientation
Meaning

Before start
The block cursor is positioned before the start of the result set. If the first row of the new rowset 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 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.

BookmarkRow
The row corresponding to the bookmark specified by the SQL_ATTR_FETCH_BOOKMARK_PTR statement attribute.

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

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

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

a SQLFetchScroll() returns the same rowset as if it was 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 rowset.) and SQL_SUCCESS_WITH_INFO.

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

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

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

Row Status Array

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.

Row status array value
Description

SQL_ROW_SUCCESS
The row was successfully fetched.

SQL_ROW_SUCCESS_WITH_INFO
The row was successfully fetched. However, a warning was returned about the row.

SQL_ROW_ERROR
An error occurred while fetching the row.

SQL_ROW_ADDED
The row was inserted by SQLBulkOperations(). If the row is fetched again, or is refreshed by SQLSetPos() its status is SQL_ROW_SUCCESS.

This value is not set by SQLFetch() or SQLFetchScroll().

SQL_ROW_UPDATED
The row was successfully fetched and has changed since it was last fetched from this result set. If the row is fetched again from this result set, or is refreshed by SQLSetPos(), the status changes to the row's new status.

SQL_ROW_DELETED
The row has been deleted since it was last fetched from this result set.

SQL_ROW_NOROW
The rowset overlapped the end of the result set and no row was returned that corresponded 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 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


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]