dbcursoropen

Opens a cursor. It is an explicit server cursor if you are connected to SQL Server 6.0 and dbcursorfetchex is used for the first fetch. It is a transparent server cursor if you are connected to SQL Server 6.0 and dbcursorfetch is used for the first fetch. It is a client cursor if you are connected to SQL Server 4.2, or if the DBCLIENTCURSORS option is set, and only dbcursorfetch can be used to fetch rows.

Syntax

PDBCURSOR dbcursoropen (
PDBPROCESS dbproc,
LPCSTR stmt,
INT scrollopt,
INT concuropt,
UINT nrows,
LPDBINT pstatus );

where

dbproc

Is the DBPROCESS structure that is the handle for a particular workstation/ SQL Server process. It contains all the information that DB-Library uses to manage communications and data between the workstation and SQL Server.

stmt

Is the SELECT statement that defines a cursor.

Client cursor:

This must be a single SELECT statement. All tables included in the FROM clause must have a unique index.

The SELECT statement cannot contain any of the following:

• INTO
• FOR BROWSE
• COMPUTE
• UNION
• COMPUTE BY
• aggregate function
• Table alias

If scrollopt is CUR_KEYSET, the SELECT statement can contain the following keywords:

• ORDER BY
• HAVING
• GROUP BY

If the SELECT statement includes a view, the FROM clause must include only a single view (no other tables or views). All base tables included in the FROM clause of the view definition must have a unique index, and the select list must include all unique index columns of the base tables.

Transparent server cursor, explicit server cursor:

This can be a single SELECT statement or the name of a stored procedure that contains only a single SELECT statement.

The SELECT statement (alone or in a stored procedure) cannot contain any of the following keywords:

• INTO
• FOR BROWSE
• COMPUTE

The SELECT statement can contain an ORDER BY clause. If the columns in the ORDER BY clause match the columns of the unique indexes used by the cursor, the cursor will use the scrollopt requested. If they do not match, SQL Server must generate a temporary table, and a CUR_KEYSET cursor will be used if a scrollopt of CUR_FORWARD or CUR_DYNAMIC is requested. This also occurs if the SELECT contains a subquery.

The cursor is automatically opened with a scrollopt of CUR_INSENSITIVE and a concuropt of CUR_READONLY if the SELECT statement contains any of the following:

• Table with no unique index
• UNION
• DISTINCT
• GROUP BY
• HAVING
• aggregate function
• Outer join

If a stored procedure is used, any input parameters must be constants. Declared variables cannot be used for input parameters. Any output parameters or return values from the stored procedure are ignored.

scrollopt

Is one of the following requested scroll options:

scrollopt	Description
CUR_DYNAMIC	Dynamic cursor. Client cursor, transparent server cursor: The dbcursorfetch function will allow only a fetchtype of FETCH_FIRST, FETCH_NEXT, or FETCH_PREV. Explicit server cursor: The dbcursorfetchex function will allow all fetchtype values except FETCH_RANDOM.
CUR_FORWARD	Forward-only dynamic cursor. Client cursor, transparent server cursor: The dbcursorfetch function will allow only a fetchtype of FETCH_FIRST or FETCH_NEXT. Explicit server cursor: The dbcursorfetchex function will allow only a fetchtype of FETCH_FIRST, FETCH_NEXT, or FETCH_RELATIVE with a positive rownum.
CUR_KEYSET	Keyset cursor. The dbcursorfetch and dbcursorfetchex functions will allow all fetchtype values.
CUR_INSENSITIVE	Client cursor: Not supported. Transparent server cursor, explicit server cursor: Insensitive keyset cursor. Use a concuropt of READ_ONLY. SQL Server will generate a temporary table, so changes made to the rows by others will not be visible through the cursor. The dbcursorfetch and dbcursorfetchex functions will allow all fetchtype values.
n > 1	Client cursor: For backward compatibility with "mixed" client cursors. Transparent server cursor, explicit server cursor: Mapped to a CUR_KEYSET cursor.

concuropt

Is one of the following concurrency control options:

concuropt	Description
CUR_READONLY	Read-only cursor. You cannot modify rows in the cursor results set.
CUR_LOCKCC	Intent to update locking. Client cursor: Places an exclusive lock on the data page that contains each row as it is fetched. The locks are maintained only if it is inside an open transaction block defined by BEGIN TRANSACTION; the locks are released when the transaction is closed by a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transparent server cursor, explicit server cursor: Places an update intent lock on the data page that contains each row as it is fetched. If not inside an open transaction, the locks are released when the next fetch is performed. If inside an open transaction, the locks are released when the transaction is closed.
CUR_OPTCC	Optimistic concurrency control using timestamp or values. Changes to a row through the cursor succeed only if the row remains unchanged since the last fetch. Changes are detected by comparing timestamps or by comparing all non-text, non-image values if timestamps are not available.
CUR_OPTCCVAL	Optimistic concurrency control using values. Changes to a row through the cursor succeed only if the row remains unchanged since the last fetch. Changes are detected by comparing all non-text, non-image values.

nrows

Client cursor, transparent server cursor:

Is the number of rows in the fetch buffer filled by calls to dbcursorfetch.

Explicit server cursor:

Is the maximum number of rows in the fetch buffer. The nfetchrows parameter of dbcursorfetchex must be less than or equal to this value.

pstatus

Is a pointer to the array of row status indicators. This array must contain nrows DBINT elements. A row status value is a bitmap of fetch status values ORed together.

Each row in the fetch buffer has a corresponding row status indicator. After a fetch, the status of every row in the fetch buffer is returned in the corresponding element of this array.

Client cursor, transparent server cursor:

For more information about fetch status values, see dbcursorfetch.

Explicit server cursor:

For more information about fetch status values, see dbcursorfetchex.

Returns

A handle to the cursor if the cursor open succeeds. If it fails, NULL is returned. Several errors, such as the following, can cause the cursor to fail:

• Not enough memory to complete the request. Reduce the number of rows in the keyset cursor results set by using a more limiting WHERE clause, use a dynamic cursor, or reduce the number of rows in the fetch buffer.
• Tables did not have the required unique indexes.
• A syntax error occurred or the SELECT statement failed.

Remarks

After dbcursoropen returns a valid cursor handle, you can call dbcursorinfoex and examine the Type field to determine the actual type of cursor that was opened.

With a dynamic cursor, membership of rows in the cursor results set is determined at fetch time, and it can change between each fetch. A row disappears from the cursor results set if it is deleted or if it is updated such that it no longer meets the WHERE clause criteria. A row appears in the cursor results set if it is inserted or updated such that it meets the WHERE clause criteria.

With a keyset cursor, membership and order of rows in the cursor results set is fixed at open time. A row is marked as missing from the cursor results set if it is deleted or if it is updated such that it no longer meets the WHERE clause criteria. A row appears in the cursor results set only if it inserted through a cursor based on a single table.

Multiple cursors (as many as the system's memory allows) can be opened using the same DBPROCESS connection. When cursor functions are called, there should be no commands waiting to be executed or results pending in the DBPROCESS connection.

See Also

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