dbcursorfetchex

DB Library for C

DB Library for C

dbcursorfetchex

Fetches a block of rows (called the fetch buffer) from an explicit server cursor and stores the rows in the bound program variables established using dbcursorbind.

Syntax

RETCODE dbcursorfetchex (
PDBCURSOR
hc,
INT
fetchtype,
DBINT
rownum,
DBINT
nfetchrows,
DBINT
reserved )

Arguments

hc

Is a PDBCURSOR pointer to a server cursor returned 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 cursor result set.
FETCH_RELATIVE 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.
FETCH_LAST 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 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 nfetchrows parameter.

For a forward-only, dynamic cursor (scrollopt is CUR_FORWARD in dbcursoropen), you can only use the FETCH_FIRST, FETCH_NEXT, or FETCH_RELATIVE (with a positive rownum) types.

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:

  • A positive rownum means that the first row of the new fetch buffer is the rownum row (counting forward from the beginning) of the cursor result set.

  • A negative rownum means that the first row of the new fetch buffer is rownum rows backward from the end of the cursor result set. Given n rows in the cursor result set, the first row of the new fetch buffer is row n+1+rownum of the cursor result set.

    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 result 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 result set.

  • A rownum of 0 means that the first row of the new fetch buffer is before the beginning (first row) of the cursor result set.

    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.

    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 nondeleted 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.

  • 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.

  • For dynamic cursors, a rownum of 0 means that all the rows in the current fetch buffer are fetched again without moving the current cursor position. This is different from a refresh because the rows in the new fetch buffer can differ from the rows in the current fetch buffer. New rows can appear, and old rows can disappear.

    In the case given earlier, 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 nondeleted 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 Microsoft® SQL Server™ without moving the current cursor position. This is identical to calling dbcursor with optype set to CRS_REFRESH.

nfetchrows

Is the number of rows in the new fetch buffer. This value must be less than or equal to the nrows parameter specified for this cursor in dbcursoropen. The poutlen and pvaraddr arrays specified in calls to dbcursorbind must have at least nfetchrows elements. If these arrays are not large enough, you must break the existing bindings and then rebind with large enough arrays (at least nfetchrows elements) before calling dbcursorfetchex.

When fetchtype is FETCH_FIRST, an nfetchrows value of 0 means that the new cursor position is set to before the beginning (first row) of the cursor result set.

When fetchtype is FETCH_LAST, an nfetchrows value of 0 means that the new cursor position is set to after the end (last row) of the cursor result set.

reserved

Reserved for future use. Use 0.

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 dbcursorfetchex to FAIL.

FAIL is returned if at least one of the following is true:

  • A fetchtype of FETCH_RANDOM was used on a dynamic cursor.

  • A fetchtype other than FETCH_FIRST, FETCH_NEXT, or FETCH_RELATIVE (with a positive rownum) was used on a forward-only, dynamic cursor.

  • The SQL Server connection is broken or times out.

  • DB-Library is out of memory.
Remarks

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.


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 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 (earlier specified 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 explicit server 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.

  • Before the beginning of the cursor result set if all rows of the new fetch buffer are before the first row of the cursor result set, or if any backward fetch (FETCH_PREV or FETCH_RELATIVE with a negative rownum) is performed when the first row of the current fetch buffer is the first row of the cursor result set.

  • After the end of the cursor result set if the first row (and thus all rows) of the new fetch buffer is after the last row of the cursor result set.

When the current cursor position is before the beginning of the cursor, a FETCH_NEXT operation is identical to a FETCH_FIRST operation. When the current cursor position is after the end of the cursor, a FETCH_PREV operation is identical to a FETCH_LAST operation.

Note  This function works with explicit server cursors in SQL Server version 6.0 or later. Do not use both dbcursorfetchex and dbcursorfetch with the same server cursor handle. After one of these functions is used on a specific cursor handle, any attempt to use the other function returns FAIL.

Each call to dbcursorfetch leaves the DBPROCESS structure available for use with no pending results.

See Also

Bulk-Copy Functions

dbcursorcolinfo

dbcursor

dbcursorinfo

dbcursorbind

dbcursoropen

dbcursorclose