SQLFetchScroll function (CLI) - Fetch rowset and return data for all bound columns

Purpose

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,
                              SQLLEN            FetchOffset);

Function arguments

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

Not all cursors support all of these options. A static forward-only cursor, for example, will only support SQL_FETCH_NEXT. Scrollable cursors, such as keyset cursors, will support all of these options. 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.

Returning 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 does move the block cursor to the specified position. As with SQLFetch(), you can use SQLGetData() to retrieve the information in this case.

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

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

Descriptors and SQLFetchScroll()

SQLFetchScroll() interacts with descriptors in the same manner as SQLFetch().

Return codes

• SQL_SUCCESS
• SQL_SUCCESS_WITH_INFO
• SQL_NO_DATA
• SQL_STILL_EXECUTING
• SQL_ERROR
• SQL_INVALID_HANDLE

Diagnostics

Restrictions

None.

Example

  /* fetch the rowset: row15, row16, row17, row18, row19 */
  printf("\n  Fetch the rowset: row15, row16, row17, row18, row19.\n");

  /* fetch the rowset and return data for all bound columns */
  cliRC = SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 15);
  STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);

  /* call SQLFetchScroll with SQL_FETCH_RELATIVE offset 3 */
  printf("  SQLFetchScroll with SQL_FETCH_RELATIVE offset 3.\n");
  printf("    COL1          COL2         \n");
  printf("    ------------  -------------\n");

  /* fetch the rowset and return data for all bound columns */
  cliRC = SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 3);