dbcursor

Inserts, updates, deletes, locks, or refreshes a particular row in the fetch buffer of a client cursor, a transparent server cursor, or an explicit server cursor.

Syntax

RETCODE dbcursor (
PDBCURSOR hc,
INT optype,
INT row,
LPCSTR table,
LPCSTR values );

where

hc

Is the cursor handle previously returned by dbcursoropen.

optype

Specifies the type of cursor operation to perform on a row or rows in the fetch buffer, as follows:

optype	Description
CRS_DELETE	Deletes row(s).
CRS_INSERT	Inserts a single row using data specified in values.
CRS_LOCKCC	Locks row(s). Client cursor: An exclusive lock is placed on the data page that contains the specified row. The lock is maintained only if it is inside an open transaction block defined by BEGIN TRANSACTION; the lock is released when the transaction is closed by a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transparent server cursor, explicit server cursor: An update intent lock is placed on the data page that contains the specified row. If not inside an open transaction, this lock is released when the next fetch is performed. If inside an open transaction, this lock is released when the transaction is closed.
CRS_REFRESH	Refreshes row(s) by retrieving current row data from SQL Server.
CRS_UPDATE	Updates row(s) using data specified in values.

If the cursor was opened using a concuropt of CUR_READONLY (specified in dbcursoropen), only CRS_REFRESH is valid.

row

Is the row number in the fetch buffer to which the optype operation applies. The first row in the buffer is number 1. The specified row must contain valid row data.

Client cursor, transparent server cursor:

When optype is CRS_REFRESH, a row value of 0 indicates that all rows in the fetch buffer will be refreshed.

Explicit server cursor:

When optype is one of the following values:

• CRS_DELETE
• CRS_LOCKCC
• CRS_REFRESH
• CRS_UPDATE

a row value of 0 indicates that the optype operation will be performed on all rows in the fetch buffer.

table

Is the table to which the optype operation applies. If optype is CRS_REFRESH use NULL. It must be one of the tables specified in the FROM clause of the SELECT statement (specified in dbcursoropen) that defines the cursor. If the FROM clause includes only one table, this parameter is not required, and you can specify that table or NULL.

Client cursor:

If the FROM clause includes more than one table, this parameter is required unless:

• optype is CRS_INSERT and values points to a complete INSERT statement
• optype is CRS_UPDATE and values points to a complete UPDATE statement

Transparent server cursor, explicit server cursor:

If the FROM clause includes more than one table and the dbcursor operation is being performed with an ambiguous column name, this parameter is required. If table is required but not specified, the default is the first table listed in the FROM clause.

values

Is a pointer to a string that contains a Transact-SQL statement or clause, or NULL. This parameter specifies the data to be inserted or updated. The following table lists the valid values parameters for each optype:

optype	values
CRS_DELETE	NULL
CRS_INSERT	Can be one of the following: NULL, indicating that the single row to be inserted will come from the data stored in the bound program variables (pvaraddr and poutlen in dbcursorbind) for the specified row in the fetch buffer. This requires overwriting the values in the bound program variables for an existing row. A pointer to a string that contains a complete Transact-SQL INSERT statement that specifies the single row to be inserted, with no WHERE clause. The table specified in the INSERT statement overrides the table parameter. The row parameter is ignored. A pointer to a string that contains just the VALUES clause (from an INSERT statement) that specifies the single row to be inserted. The VALUES keyword is optional, but the list of values to be inserted must be surrounded by parentheses. The row parameter is ignored.
CRS_LOCKCC	NULL
CRS_REFRESH	NULL
CRS_UPDATE	Can be one of the following: NULL, indicating that the changes made to a single row will come from the data stored in the bound program variables (pvaraddr and poutlen in dbcursorbind) for the specified row in the fetch buffer. To do this, the SELECT statement that defines the cursor (specified in dbcursoropen) cannot include an expression (for example, "length 10") or a function (such as CONVERT) in the SELECT list. A pointer to a string that contains a complete UPDATE statement that specifies the changes made to a single row, with no WHERE clause. The table specified in the UPDATE statement overrides the table parameter. The row parameter is ignored. A pointer to a string that contains just the SET clause (from an UPDATE statement) that specifies the changes made to a single row. The SET keyword is optional. The row parameter is ignored.

Returns

SUCCEED or FAIL.

This function can fail for the following reasons:

• The cursor is opened as read only, no updates allowed.
• A server or connection failure or timeout occurs.
• You have not been granted permission to update or change the database.
• DB-Library is out of memory.
• A trigger in the database caused the INSERT, LOCK, or UPDATE operation to fail.
• You are using optimistic concurrency control, and the row has changed.

Remarks

Using dbcursor does not affect the current cursor position.

When optype is CRS_UPDATE and the values parameter is not NULL and points to a string that contains the UPDATE statement or SET clause, the bound program variables (pvaraddr and poutlen in dbcursorbind) are automatically refreshed to their newly updated values.

The select list used to define the cursor (specified in dbcursoropen) can contain timestamp or identity columns. When optype is CRS_INSERT or CRS_UPDATE and the values parameter is NULL (indicating that new data is obtained from bound program variables), any read-only columns (including timestamp or identity columns) in the fetch buffer are skipped. Because no attempt is made to change these read-only columns, the insert or update can succeed without receiving errors.

When using CRS_UPDATE, if a change is made to a column that is part of the unique index used to open the cursor, the changed row will:

• Be missing from a keyset cursor. The next time the changed row is fetched, the row status indicator (pstatus in dbcursoropen) for that row will be FTC_MISSING.
• Appear in a new position in a dynamic cursor. The new position depends on the new value of the unique index column, and later fetches might retrieve the changed row.

After using CRS_DELETE, deleted rows will be missing from a keyset cursor (later fetches will have a row status of FTC_MISSING), and will disappear from later fetches using dynamic cursors.

Client cursor:

When using CRS_INSERT with a keyset cursor, the inserted row does not appear in the cursor results set, and thus does not appear in later fetches.

Transparent server cursor, explicit server cursor:

When optype is one of the following:

• CRS_INSERT with a keyset cursor involving only one table
• CRS_UPDATE to change a column that is part of the unique index used to open the cursor

the inserted or updated row will appear as a new row at the end of the keyset (even if the inserted row does not match the WHERE clause criteria), or it will appear in the position of a missing row if the unique index columns of the inserted or updated row match the unique index columns of the missing row.

See Also

dbcursorbind, dbcursorclose, dbcursorcolinfo, dbcursorfetch, dbcursorinfo, dbcursoropen; Bulk-Copy Functions