dbcursoropen
Opens a cursor. It is an explicit server cursor if you are connected to Microsoft® SQL Server™ version 6.0 or later and dbcursorfetchex is used for the first fetch. It is a transparent server cursor if you are connected to SQL Server 6.0 or later 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 );
Arguments
dbproc
Is the DBPROCESS structure that is the handle for a particular workstation or SQL Server 2000 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: Explicit server cursor: |
| CUR_FORWARD | Forward-only, dynamic cursor.
Client cursor, transparent server cursor: Explicit server cursor: |
| 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: 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: |
concuropt
Is one of the following concurrency control options.
| concuropt | Description |
|---|---|
| CUR_READONLY | Read-only cursor. You cannot modify rows in the cursor result set. |
| CUR_LOCKCC | Intent to update locking.
Client cursor: Transparent server cursor, explicit server cursor: |
| CUR_OPTCC | Optimistic concurrency control using timestamp or values. Changes to a row that are initiated through the cursor succeed only if the row remains unchanged since the last fetch. Changes are detected by comparing timestamps or by comparing all nontext, nonimage 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 nontext, nonimage 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 joined in a logical OR.
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 result 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 result set is determined at fetch time, and it can change between each fetch. A row disappears from the cursor result 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 result 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 result set is fixed at open time. A row is marked as missing from the cursor result 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 result 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.
When you call the dbcursoropen function with a scrollopt parameter of CUR_DYNAMIC, the tables you specify in the SELECT statement of the stmt parameter no longer require unique indexes.
In SQL Server 6.0, the cursor automatically opens with a scrollopt parameter of CUR_INSENSITIVE and a concuropt parameter of CUR_READONLY if the SELECT statement contains a table without a unique index.
In SQL Server 6.5 or later, the cursor automatically opens with a scrollopt parameter of CUR_INSENSITIVE and a concuropt parameter of CUR_READONLY if the SELECT statement contains any of the following:
- ORDER BY and a table with no unique index
- UNION
- DISTINCT
- GROUP BY
- HAVING
- Aggregate function
SQL Server copies the results into temporary work tables. Because all fetch operations are performed on the temporary tables, you cannot make changes to the cursor by using dbcursor.