Specification: | 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.
SQLRETURN SQLFetchScroll (SQLHSTMT StatementHandle,
SQLSMALLINT FetchOrientation,
SQLLEN FetchOffset);
Data type | Argument | Use | Description |
---|---|---|---|
SQLHSTMT | StatementHandle | input | Statement handle. |
SQLUSMALLINT | FetchOrientation | input | Type of fetch:
|
SQLLEN | 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. |
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.
This value is not set by SQLFetch() or SQLFetchScroll().
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().
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 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 or timestamp data types, 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 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 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 returned by SQLGetDiagRec() in the *MessageText buffer describes the error and its cause. |
HY001 | Memory allocation failure. | DB2® CLI is unable to allocate memory required to support execution or completion of the function. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information on process-level memory limitations. |
HY008 | Operation was cancelled. | Asynchronous processing was enabled for StatementHandle. The function was called and before it completed execution, SQLCancel() was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle. |
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 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. |
None.
/* 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);