Fetches a block of rows (called the fetch buffer) from an explicit server cursor and makes the rows available using SqlCursorData$.
SqlCursorFetchEx% ( cursorhandle%, fetchtype%, rownum%, nfetchrows%, reserved& )
where
fetchtype% | Description |
---|---|
FETCHFIRST% | Fetches the first block of rows from a dynamic or keyset cursor. The first row of the new fetch buffer is the first row in the cursor results set. |
FETCHNEXT% | Fetches the next block of rows from a dynamic or keyset cursor. The first row of the new fetch buffer is the row after the last row of the current fetch buffer. If this is the first fetch using a new cursor, it behaves the same as FETCHFIRST%. |
FETCHPREV% | Fetches the previous block of rows from a fully dynamic or keyset cursor. The first row of the new fetch buffer is nrows% (specified in SqlCursorOpen%) before the first row of the current fetch buffer. |
FETCHRANDOM% | Fetches a block of rows from a keyset cursor. The first row of the new fetch buffer is the specified rownum% row in the cursor results set. |
FETCHRELATIVE% | Fetches a block of rows from a dynamic or keyset cursor. The first row of the new fetch buffer is rownum% rows before or after the first row of the current fetch buffer. |
FETCHLAST% | Fetches the last block of rows from a dynamic or keyset cursor. The last row of the new fetch buffer is the last row of the cursor results set. |
The block of rows retrieved by a fetch is called the fetch buffer. The number of rows in the fetch buffer is determined by the nfetchrows% parameter.
For a forward-only dynamic cursor (scrollopt% is CURFORWARD% in SqlCursorOpen%), you can only use the FETCHFIRST%, FETCHNEXT%, or FETCHRELATIVE% (with a positive rownum%) types.
When fetchtype% is FETCHRANDOM%:
For example, a rownum% of - 1 means the first row of the new fetch buffer is row N (N+1 - 1), or the last row, of the current results set. A rownum% of - N means the first row of the new fetch buffer is row 1 (N+1 - N), or the first row, of the current results set.
When fetchtype% is FETCHRELATIVE%:
For dynamic cursors, if the first row in the current fetch buffer is deleted before a relative fetch, the current cursor position becomes invalid. Let D be the number of contiguous rows, including the first row, deleted from the beginning of the current fetch buffer. Before executing a relative fetch, the current cursor position is set to before the first non-deleted row (row D+1) in the current fetch buffer.
In this case, when a relative fetch is performed with a positive rownum%, the first row of the new fetch buffer is row rownum%+D of the current fetch buffer.
In the case given above, after D contiguous rows have been deleted from the beginning of the current fetch buffer, when a relative fetch is performed with a rownum% of 0, the first row of the new fetch buffer is the first non-deleted row (row D+1) of the current fetch buffer.
For keyset cursors, a rownum% of 0 means that the current fetch buffer is refreshed with current data from SQL Server without moving the current cursor position. This is identical to calling SqlCursor% with optype% set to CRSREFRESH%.
When fetchtype% is FETCHFIRST%, an nfetchrows% value of 0 means that the new cursor position is set to before the beginning (first row) of the cursor results set.
When fetchtype% is FETCHLAST%, an nfetchrows% value of 0 means that the new cursor position is set to after the end (last row) of the cursor results set.
succeed (1) or fail (0).
SUCCEED (1) is returned if every row was fetched successfully. Note that for a keyset cursor, a fetch that results in a missing row will not cause SqlCursorFetchEx% to FAIL (0).
FAIL (0) is returned if at least one of the following is true:
After the fetch, the elements of the array of row status indicators (pstatus&() in SqlCursorOpen%) are filled with row status values, one for each row in the fetch buffer. Each row status value is a series of fetch status values ORed together. The following table shows the meaning of each row status value:
Fetch status | Description |
---|---|
FTCSUCCEED% | The row was successfully fetched. SqlCursorData$ will return valid data for the row. |
FTCMISSING% | The row has been deleted or a unique index column of the row has been changed. Do not use the values returned by SqlCursorData$ for the row. For keyset cursors, this fetch status can appear at any time. For dynamic cursors, this fetch status can appear only after the current fetch buffer is refreshed. |
A row status indicator of 0 means that the row is invalid, and SqlCursorData$ will not return valid data. This happens when the row is before the beginning (first row) or after the end (last row) of the cursor results set.
After the fetch, SqlCursorData$ returns:
If no fetches have been performed on a cursor, the current cursor position is before the beginning (first row) of the cursor results set.
After a fetch is complete, the new explicit server cursor position is one of the following:
When the current cursor position is before the beginning of the cursor, a FETCHNEXT% operation is identical to a FETCHFIRST% operation. When the current cursor position is after the end of the cursor, a FETCHPREV% operation is identical to a FETCHLAST% operation.
Note This function works with explicit server cursors in SQL Server 6.0. Do not use both SqlCursorFetchEx% and SqlCursorFetch% with the same server cursor handle. Once one of these functions is used on a specific cursor handle, any attempt to use the other function will return fail (0).
Each call to SqlCursorFetch% leaves the connection available for use with no pending results.
SqlCursor%, SqlCursorColInfo%, SqlCursorClose, SqlCursorInfo%, SqlCursorOpen%