Opens a cursor. It is an explicit server cursor if you are connected to SQL Server 6.0 and SqlCursorFetchEx% is used for the first fetch. It is a transparent server cursor if you are connected to SQL Server 6.0 and SqlCursorFetch% 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 SqlCursorFetch% can be used to fetch rows.
SqlCursorOpen% ( sqlconn%, stmt$, scrollopt%, concuropt%, nrows%, pstatus&() )
where
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:
If scrollopt% is CURKEYSET%, the SELECT statement can contain the following keywords:
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:
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 CURKEYSET% cursor will be used if a scrollopt% of CURFORWARD% or CURDYNAMIC% is requested. This also occurs if the SELECT contains a subquery.
The cursor is automatically opened with a scrollopt% of CURINSENSITIVE% and a concuropt% of CURREADONLY% if the SELECT statement contains any of the following:
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% | Description |
|---|---|
| CURDYNAMIC% | Dynamic cursor. Client cursor, transparent server cursor: The SqlCursorFetch% function will allow only a fetchtype% of FETCHFIRST%, FETCHNEXT%, or FETCHPREV%. Explicit server cursor: The SqlCursorFetchEx% function will allow all fetchtype% values except FETCHRANDOM%. |
| CURFORWARD% | Forward-only dynamic cursor. Client cursor, transparent server cursor: The SqlCursorFetch% function will allow only a fetchtype% of FETCHFIRST% or FETCHNEXT%. Explicit server cursor: The SqlCursorFetchEx% function will allow only a fetchtype% of FETCHFIRST%, FETCHNEXT%, or FETCHRELATIVE% with a positive rownum%. |
| CURKEYSET% | Keyset cursor. The SqlCursorFetch% and SqlCursorFetchEx% functions will allow all fetchtype% values. |
| CURINSENSITIVE% | 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 SqlCursorFetch% and SqlCursorFetchEx% 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 CURKEYSET% cursor. |
| concuropt% | Description |
|---|---|
| CURREADONLY% | Read-only cursor. You cannot modify rows in the cursor results set. |
| CURLOCKCC% | 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. |
| CUROPTCC% | 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. |
| CUROPTCCVAL% | 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. |
Is the number of rows in the fetch buffer filled by calls to SqlCursorFetch%.
Explicit server cursor:
Is the maximum number of rows in the fetch buffer. The nfetchrows% parameter of SqlCursorFetchEx% must be less than or equal to this value.
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 SqlCursorFetch%.
Explicit server cursor:
For more information about fetch status values, see SqlCursorFetchEx%.
A handle to the cursor if the cursor open succeeds. If it fails, 0 is returned. Several errors, such as the following, can cause the cursor to fail:
After SqlCursorOpen% returns a valid cursor handle, you can call SqlCursorInfoEx% 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 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 is 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 connection. When cursor functions are called, there should be no commands waiting to be executed or results pending in the connection.
SqlCursor%, SqlCursorClose, SqlCursorColInfo%, SqlCursorFetch%, SqlCursorInfo%, SqlCursorOpen%