dbcursorfetch
Fetches a block of rows (called the fetch buffer) from a client cursor or transparent server cursor, and stores the rows in the bound program variables established using dbcursorbind. If you are connected to Microsoft® SQL Server™ version 6.0 or later, you should use dbcursorfetchex.
Syntax
RETCODE dbcursorfetch (
PDBCURSOR hc,
INT fetchtype,
INT rownum );
Arguments
hc
Is the cursor handle created by dbcursoropen.
fetchtype
Specifies the type of fetch to execute, changing the position of the fetch buffer within the cursor result set. The following table describes the different fetchtype values.
| fetchtype | Description |
|---|---|
| FETCH_FIRST | 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 result set. |
| FETCH_NEXT | 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 FETCH_FIRST. |
| FETCH_PREV | 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 dbcursoropen) before the first row of the current fetch buffer. |
| FETCH_RANDOM | Fetches a block of rows from a keyset cursor. The first row of the new fetch buffer is the specified rownum row in the keyset cursor result set. |
| FETCH_RELATIVE | Fetches a block of rows from a keyset cursor. The first row of the new fetch buffer is rownum rows before or after the first row of the current fetch buffer. |
| FETCH_LAST | Fetches the last block of rows from a keyset cursor. The last row of the new fetch buffer is the last row of the cursor result 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 nrows parameter of dbcursoropen.
For a forward-only dynamic cursor (scrollopt is CUR_FORWARD in dbcursoropen), you can use only FETCH_FIRST or FETCH_NEXT.
rownum
Is the specified random or relative row number to use as the first row of the new fetch buffer. Use this parameter only with a fetchtype of FETCH_RANDOM or FETCH_RELATIVE. Specify 0 for any other fetchtype.
When fetchtype is FETCH_RANDOM, the first row of the new fetch buffer is the rownum row (counting forward from the beginning) of the keyset cursor result set. The rownum parameter must be positive.
When fetchtype is FETCH_RELATIVE:
- A positive rownum means that the first row of the new fetch buffer is rownum rows after the first row of the current fetch buffer.
- A negative rownum means that the first row of the new fetch buffer is rownum rows before the first row of the current fetch buffer.
- A rownum of 0 means that all rows in the fetch buffer are refreshed with current data from SQL Server without moving the current cursor position. This is identical to calling dbcursor with optype set to CRS_REFRESH.
Returns
SUCCEED or FAIL.
SUCCEED 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 dbcursorfetch to FAIL.
FAIL is returned if at least one of the following is true:
- A fetchtype of FETCH_RANDOM, FETCH_RELATIVE, or FETCH_LAST was used on a dynamic cursor.
- A fetchtype other than FETCH_FIRST or FETCH_NEXT was used on a forward-only dynamic cursor.
- The SQL Server connection is broken or times out.
- DB-Library is out of memory.
Remarks
Specify the size of the fetch buffer in the nrows parameter of dbcursoropen.
After the fetch, the elements of the array of row status indicators (pstatus in dbcursoropen) 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 joined in a logical OR. The following table shows the meaning of each row status value.
| Fetch status | Description |
|---|---|
| FTC_SUCCEED | Row was successfully fetched. The array of bound program variables and the array of data length values (specified in dbcursorbind) contain valid data for the row. |
| FTC_MISSING | Row has been deleted or a unique index column of the row has been changed. Do not use the values in the array of bound program variables and the array of data length values (specified in dbcursorbind) 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. |
| FTC_ENDOFKEYSET | Is the end of the keyset. This fetch status is set for backward compatibility with "mixed" client cursors used by existing applications. |
| FTC_ENDOFRESULTS | Is the end of the result set of a dynamic or keyset cursor. Rows in the fetch buffer after this row are invalid and will have a row status indicator of 0; do not use the values in the array of bound program variables and the array of data length values (specified in dbcursorbind) for those rows.
If joined in a logical OR with FTC_SUCCEED, this is the last row in the cursor result set; it contains valid data. If joined in a logical OR with FTC_MISSING, this is the last row in the cursor result set, but the row is missing. If not joined with FTC_SUCCEED or FTC_MISSING, this row is invalid. |
A row status indicator of 0 means that the row is invalid, and the values in the array of bound program variables and the array of data length values (specified in dbcursorbind) do not contain valid data. This usually happens when the row is before the beginning (first row) or after the end (last row) of the cursor result set.
After the fetch, the elements of the array of bound program variables and the array of data length values (specified earlier in dbcursorbind):
- Are filled with valid data for all rows with a fetch status of FTC_SUCCEED.
- Contain invalid data for all rows without a fetch status of FTC_SUCCEED.
If no fetches have been performed on a cursor, the current cursor position is before the beginning (first row) of the cursor result set.
After a fetch is complete, the new cursor position is one of the following:
- The first row of the new fetch buffer, as specified under fetchtype, if the first row of the new fetch buffer stayed within the cursor result set.
- Adjusted to the first row of the cursor result set if the first row of the new fetch buffer would have been before the first row of the cursor result set and the last row of the new fetch buffer would have stayed within the cursor result set due to a FETCH_PREV operation.
- Unchanged from the current position if all rows of the new fetch buffer would have been before the first row of the cursor result set, if the first row (and thus all rows) of the new fetch buffer would have been after the last row of the cursor result set, or, if for a keyset cursor, if the first row of the new fetch buffer would have been before the first row of the cursor result set and the last row of the new fetch buffer would have stayed within the cursor result set due to a FETCH_RELATIVE attempt with a negative rownum.
When the new cursor position is unchanged because the first row (and thus all rows) of the new fetch buffer would have been after the last row of the cursor result set, all rows in the fetch buffer are invalid and will not have a fetch status of FTC_SUCCEED. In the case of dynamic cursors, the first row of the fetch buffer will have a fetch status of FTC_ENDOFRESULTS, and later rows will have a row status of 0. In the case of keyset cursors, all rows in the fetch buffer will have a row status of 0.
When the new position of a dynamic cursor is unchanged because all rows of the new fetch buffer would have been before the first row of the cursor result set, all rows in the fetch buffer are invalid and will not have a fetch status of FTC_SUCCEED. The first row of the fetch buffer will have a fetch status of FTC_ENDOFRESULTS, and later rows will have a row status of 0.
When the new cursor position is unchanged and all rows in the fetch buffer are invalid, you can use dbcursor to refresh the rows in the fetch buffer with current data from SQL Server. This will result in valid rows that reflect the current cursor position.
Each call to dbcursorfetch leaves the DBPROCESS structure available for use with no pending results.
Caution This function works with client cursors and transparent server cursors. Do not use both dbcursorfetch and dbcursorfetchex with the same cursor handle. After one of these functions is used on a specific cursor handle, any attempt to use the other function returns FAIL.
If rows in the current fetch buffer of a dynamic cursor are deleted, a fetch using a client cursor might behave differently than a fetch using a transparent server cursor.
Client cursor
When the new position of a dynamic cursor is adjusted to be the first row of the cursor result set (which happens when the first row of the new fetch buffer would have been before the first row of the dynamic cursor result set and the last row of the new fetch buffer would have stayed within the dynamic cursor result set due to a FETCH_PREV operation), some rows at the end of the new fetch buffer might be invalid. Any invalid rows will have a row status indicator of 0.
If rows in the current fetch buffer of a dynamic cursor are deleted, a fetch next or fetch previous might result in a new fetch buffer that skips rows in the cursor result set or includes rows from the current fetch buffer again.
Transparent server cursor
A fetchtype of FETCH_NEXT or FETCH_PREV using a dynamic cursor is actually mapped to a relative fetch on SQL Server 6.0 or later. Because of this, if the first row in the current fetch buffer is deleted before a FETCH_NEXT (mapped to a forward relative fetch on SQL Server 6.0 or later) is performed, the current cursor position becomes invalid. For more information about the fetch behavior in this case, see dbcursorfetchex.