MDAC 2.5 SDK - ODBC Programmer's Reference
Chapter 21: ODBC API Reference

SQLFetchScroll

Conformance

Version Introduced: ODBC 3.0
Standards Compliance: ISO 92

Summary

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.

When working with an ODBC 2.x driver, the Driver Manager maps this function to SQLExtendedFetch. For more information, see "Mapping Replacement Functions for Backward Compatibility of Applications" in Chapter 17, "Programming Considerations."

Syntax

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

Arguments

StatementHandle

[Input]
Statement handle.

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" in the "Comments" section.

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" in the "Comments" section.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLFetchScroll returns either SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value can be obtained by calling SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle. The following table lists the SQLSTATE values commonly returned by SQLFetchScroll and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. 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.

For all those SQLSTATEs that can return SQL_SUCCESS_WITH_INFO or SQL_ERROR (except 01xxx SQLSTATEs), SQL_SUCCESS_WITH_INFO is returned if an error occurs on one or more, but not all, rows of a multirow operation, and SQL_ERROR is returned if an error occurs on a single-row operation.

Comments

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.

For more information, see "Using Block Cursors" and "Using Scrollable Cursors" in Chapter 11, "Retrieving Results (Advanced)."

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.

Drivers are not required to support all fetch orientations; an application calls SQLGetInfo with an information type of SQL_DYNAMIC_CURSOR_ATTRIBUTES1, SQL_KEYSET_CURSOR_ATTRIBUTES1, or SQL_STATIC_CURSOR_ATTRIBUTES1 (depending on the type of the cursor) to determine which fetch orientations are supported by the driver. The application should look at the SQL_CA1_NEXT, SQL_CA1_RELATIVE, SQL_CA1_ABSOLUTE, and WQL_CA1_BOOKMARK bitmasks in these information types. Furthermore, if the cursor is forward-only and FetchOrientation is not SQL_FETCH_NEXT, SQLFetchScroll returns SQLSTATE HY106 (Fetch type out of range).

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 current row is the first row of the rowset.

Cursor Positioning Rules

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

SQL_FETCH_NEXT

The following rules apply.

[1]   If the rowset size has been changed since the previous call to fetch rows, this is the rowset size that was used with the previous call.

SQL_FETCH_PRIOR

The following rules apply.

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

[2]   If the rowset size has been changed since the previous call to fetch rows, this is the new rowset size.

SQL_FETCH_RELATIVE

The following rules apply.

[1]   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.

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

[3]   If the rowset size has been changed since the previous call to fetch rows, this is the new rowset size.

SQL_FETCH_ABSOLUTE

The following rules apply.

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

[2]   If the rowset size has been changed since the previous call to fetch rows, this is the new rowset size.

An absolute fetch performed against a dynamic cursor cannot provide the required result because row positions in a dynamic cursor are undetermined. Such an operation is equivalent to a fetch first followed by a fetch relative; it is not an atomic operation, as is an absolute fetch on a static cursor.

SQL_FETCH_FIRST

The following rules apply.

SQL_FETCH_LAST

The following rules apply.

[1]   If the rowset size has been changed since the previous call to fetch rows, this is the new rowset size.

SQL_FETCH_BOOKMARK

The following rules apply.

For information about bookmarks, see the "Bookmarks" section in Chapter 11, "Retrieving Results (Advanced)."

Effect of Deleted, Added, and Error Rows on Cursor Movement

Static and keyset-driven cursors sometimes detect rows added to the result set and remove rows deleted from the result set. By calling SQLGetInfo with the SQL_STATIC_CURSOR_ATTRIBUTES2 and SQL_KEYSET_CURSOR_ATTRIBUTES2 options and looking at the SQL_CA2_SENSITIVITY_ADDITIONS, SQL_CA2_SENSITIVITY_DELETIONS, and SQL_CA2_SENSITIVITY_UPDATES bitmasks, an application determines whether the cursors implemented by a particular driver do this. For drivers that can detect deleted rows and remove them, the following paragraphs describe the effects of this behavior. For drivers that can detect deleted rows but cannot remove them, deletions have no effect on cursor movements, and the following paragraphs do not apply.

If the cursor detects rows added to the result set or removes rows deleted from the result set, it appears as if it detects these changes only when it fetches data. This includes the case when SQLFetchScroll is called with FetchOrientation set to SQL_FETCH_RELATIVE and FetchOffset set to 0 to refetch the same rowset, but does not include the case when SQLSetPos is called with fOption set to SQL_REFRESH. In the latter case, the data in the rowset buffers is refreshed, but not refetched, and deleted rows are not removed from the result set. Thus, when a row is deleted from or inserted into the current rowset, the cursor does not modify the rowset buffers. Instead, it detects the change when it fetches any rowset that previously included the deleted row or now includes the inserted row.

For example:

// Fetch the next rowset.
SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);

// Delete third row of the rowset. Does not modify the rowset buffers.
SQLSetPos(hstmt, 3, SQL_DELETE, SQL_LOCK_NO_CHANGE);

// The third row has a status of SQL_ROW_DELETED after this call.
SQLSetPos(hstmt, 3, SQL_REFRESH, SQL_LOCK_NO_CHANGE);

// Refetch the same rowset. The third row is removed, replaced by what
// was previously the fourth row.
SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 0);

When SQLFetchScroll returns a new rowset that has a position relative to the current rowset—that is, FetchOrientation is SQL_FETCH_NEXT, SQL_FETCH_PRIOR, or SQL_FETCH_RELATIVE—it does not include changes to the current rowset when calculating the starting position of the new rowset. However, it does include changes outside the current rowset if it is capable of detecting them. Furthermore, when SQLFetchScroll returns a new rowset that has a position independent of the current rowset—that is, FetchOrientation is SQL_FETCH_FIRST, SQL_FETCH_LAST, SQL_FETCH_ABSOLUTE, or SQL_FETCH_BOOKMARK—it includes all changes it is capable of detecting, even if they are in the current rowset.

When determining whether newly added rows are inside or outside the current rowset, a partial rowset is considered to end at the last valid row; that is, the last row for which the row status is not SQL_ROW_NOROW. For example, suppose the cursor is capable of detecting newly added rows, the current rowset is a partial rowset, the application adds new rows, and the cursor adds these rows to the end of the result set. If the application calls SQLFetchScroll with FetchOrientation set to SQL_FETCH_NEXT, SQLFetchScroll returns the rowset starting with the first newly added row.

For example, suppose the current rowset comprises rows 21 to 30, the rowset size is 10, the cursor removes rows deleted from the result set, and the cursor detects rows added to the result set. The following table shows the rows SQLFetchScroll returns in various situations.

[1]   This column uses the row numbers before any rows were inserted or deleted.

[2]   In this case, the cursor attempts to return rows starting with row 21. Because row 21 has been deleted, the first row it returns is row 22.

Error rows (that is, rows with a status of SQL_ROW_ERROR) do not affect cursor movement. For example, if the current rowset starts with row 11 and the status of row 11 is SQL_ROW_ERROR, calling SQLFetchScroll with FetchOrientation set to SQL_FETCH_RELATIVE and FetchOffset set to 5 returns the rowset starting with row 16, just as it would if the status for row 11 was SQL_SUCCESS.

Returning Data in Bound Columns

SQLFetchScroll returns data in bound columns in the same way as SQLFetch. For more information, see "Returning Data in Bound Columns" in SQLFetch.

If no columns are bound, SQLFetchScroll does not return data but does move the block cursor to the specified position. Whether data can be retrieved from unbound columns of a block cursor with SQLGetData depends on the driver. This capability is supported if a call to SQLGetInfo returns the SQL_GD_BLOCK bit for the SQL_GETDATA_EXTENSIONS information type.

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

SQLFetchScroll sets values in the row status array in the same manner as SQLFetch. For more information, see "Row Status Array" in SQLFetch.

Rows Fetched Buffer

SQLFetchScroll returns the number of rows fetched in the rows fetched buffer in the same manner as SQLFetch. For more information, see "Rows Fetched Buffer" in SQLFetch.

Error Handling

When an application calls SQLFetchScroll in an ODBC 3.x driver, the Driver Manager calls SQLFetchScroll in the driver. When an application calls SQLFetchScroll in an ODBC 2.x driver, the Driver Manager calls SQLExtendedFetch in the driver. Because SQLFetchScroll and SQLExtendedFetch handle errors in a slightly different manner, the application sees slightly different error behavior when it calls SQLFetchScroll in ODBC 2.x and ODBC 3.x drivers.

SQLFetchScroll returns errors and warnings in the same manner as SQLFetch; for more information, see "Error Handling" in SQLFetch. SQLExtendedFetch returns errors in the same manner as SQLFetch, with the following exceptions:

• When a warning occurs that applies to a particular row in the rowset, SQLExtendedFetch sets the corresponding entry in the row status array to SQL_ROW_SUCCESS, not SQL_ROW_SUCCESS_WITH_INFO.
  
  
• If errors occur in every row in the rowset, SQLExtendedFetch returns SQL_SUCCESS_WITH_INFO, not SQL_ERROR.
  
  
• In each group of status records that applies to an individual row, the first status record returned by SQLExtendedFetch must contain SQLSTATE 01S01 (Error in row); SQLFetchScroll does not return this SQLSTATE. If SQLExtendedFetch is unable to return additional SQLSTATEs, it still must return this SQLSTATE.

SQLFetchScroll and Optimistic Concurrency

If a cursor uses optimistic concurrency—that is, the SQL_ATTR_CONCURRENCY statement attribute has a value of SQL_CONCUR_VALUES or SQL_CONCUR_ROWVER—SQLFetchScroll updates the optimistic concurrency values used by the data source to detect whether a row has changed. This happens whenever SQLFetchScroll fetches a new rowset, including when it refetches the current rowset. (It is called with FetchOrientation set to SQL_FETCH_RELATIVE and FetchOffset set to 0.)

SQLFetchScroll and ODBC 2.x Drivers

When an application calls SQLFetchScroll in an ODBC 2.x driver, the Driver Manager maps this call to SQLExtendedFetch. It passes the following values for the arguments of SQLExtendedFetch.

For more information, see "Block Cursors, Scrollable Cursors, and Backward Compatibility" in Appendix G, "Driver Guidelines for Backward Compatibility."

Descriptors and SQLFetchScroll

SQLFetchScroll interacts with descriptors in the same manner as SQLFetch. For more information, see the "Descriptors and SQLFetchScroll" section in SQLFetch.

Code Example

See "Column-Wise Binding" and "Row-Wise Binding" in Chapter 11, "Retrieving Results (Advanced)", and "Positioned Update and Delete Statements" and "Updating Rows in the Rowset with SQLSetPos" in Chapter 12, "Updating Data."

Related Functions