To obtain FAST_FORWARD cursorMicrosoft® SQL Server™ returns result sets to consumers using two methods:
After a statement has been executed, no other statements can be executed on the connection until all of the results have been retrieved by the consumer, or the statement has been canceled.
The support for cursor functionality can decrease performance relative to a default result set. This can be offset if the consumer can use cursor functionality to retrieve a smaller set of rows.
Consumers can request different cursor behaviors in a rowset by setting certain rowset properties. If the consumer does not set any of these rowset properties, or sets them all to their default values, SQLOLEDB implements the rowset using a default result set. If any one of these properties are set to a value other than the default, SQLOLEDB implements the rowset using a server cursor.
The following rowset properties direct SQLOLEDB to use SQL Server cursors. Some properties can be safely combined with others. For example, a rowset that exhibits the DBPROP_IRowsetScroll and DBPROP_IRowsetChange properties will be a bookmark rowset exhibiting immediate update behavior. Other properties are mutually exclusive. For example, a rowset exhibiting DBPROP_OTHERINSERT cannot contain bookmarks.
| Property ID | Value | Rowset behavior |
|---|---|---|
| DBPROP_SERVERCURSOR | VARIANT_TRUE | Cannot update SQL Server data through the rowset. The rowset is sequential, supporting forward scrolling and fetching only. Relative row positioning is supported. Command text can contain an ORDER BY clause. |
| DBPROP_CANSCROLLBACKWARDS or DBPROP_CANFETCHBACKWARDS | VARIANT_TRUE | Cannot update SQL Server data through the rowset. The rowset supports scrolling and fetching in either direction. Relative row positioning is supported. Command text can contain an ORDER BY clause. |
| DBPROP_BOOKMARKS or DBPROP_LITERALBOOKMARKS | VARIANT_TRUE | Cannot update SQL Server data through the rowset. The rowset is sequential, supporting forward scrolling and fetching only. Relative row positioning is supported. Command text can contain an ORDER BY clause. |
| DBPROP_OWNUPDATEDELETE or DBPROP_OWNINSERT or DBPROP_OTHERUPDATEDELETE | VARIANT_TRUE | Cannot update SQL Server data through the rowset. The rowset supports scrolling and fetching in either direction. Relative row positioning is supported. Command text can contain an ORDER BY clause. |
| DBPROP_OTHERINSERT | VARIANT_TRUE | Cannot update SQL Server data through the rowset. The rowset supports scrolling and fetching in either direction. Relative row positioning is supported. Command text can include an ORDER BY clause if an index exists on the referenced columns.
DBPROP_OTHERINSERT cannot be VARIANT_TRUE if the rowset contains bookmarks. Attempting to create a rowset with this visibility property and bookmarks results in an error. |
| DBPROP_IRowsetLocate or DBPROP_IRowsetScroll | VARIANT_TRUE | Cannot update SQL Server data through the rowset. The rowset supports scrolling and fetching in either direction. Bookmarks and absolute positioning through the IRowsetLocate interface are supported in the rowset. Command text can contain an ORDER BY clause.
DBPROP_IRowsetLocate and DBPROP_ |
| DBPROP_IRowsetChange or DBPROP_IRowsetUpdate | VARIANT_TRUE | Can update SQL Server data through the rowset. The rowset is sequential, supporting forward scrolling and fetching only. Relative row positioning is supported. Command text cannot contain an ORDER BY clause. |
| DBPROP_IRowsetLocate or DBPROP_IRowsetScroll and DBPROP_IRowsetChange or DBPROP_IRowsetUpdate |
VARIANT_TRUE | Can update SQL Server data through the rowset. The rowset supports scrolling and fetching in either direction. Bookmarks and absolute positioning through IRowsetLocate are supported in the rowset. Command text can contain an ORDER BY clause. |
| DBPROP_IMMOBILEROWS | VARIANT_FALSE | Cannot update SQL Server data through the rowset. The rowset supports forward scrolling only. Relative row positioning is supported. Command text can include an ORDER BY clause if an index exists on the referenced columns.
DBPROP_ |
| DBPROP_REMOVEDELETED | VARIANT_TRUE | Cannot update SQL Server data through the rowset. The rowset supports forward scrolling only. Relative row positioning is supported. Command text can contain an ORDER BY clause unless constrained by another property. |
A SQLOLEDB rowset supported by a server cursor can be easily created on a SQL Server base table or view by using the IOpenRowset::OpenRowset method. Specify the table or view by name, passing the required rowset property sets in the rgPropertySets parameter.
Command text that creates a rowset is restricted when the consumer requires that the rowset be supported by a server cursor. Specifically, the command text is restricted to either a single SELECT statement that returns a single rowset result, or a stored procedure that implements a single SELECT statement returning a single rowset result.
These two tables show the mappings of various OLE DB properties and the cursor models. They also show which rowset properties should be set to use certain type of cursor model.
Each cell in the table contain a value of the rowset property for the specific cursor model. The data type of the rowset properties listed above are all VT_BOOL and the default values are VARIANT_FALSE. The following symbols are used in the table.
F = default value (VARIANT_FALSE)
T = VARIANT_TRUE
- = VARIANT_TRUE or VARIANT_FALSE
To use a certain type of cursor model, locate the column corresponding the cursor model, and find all the rowset properties with value ‘T’ in the column. Set these rowset properties to VARIANT_TRUE to use the specific cursor model. The rowset properties with ‘-’ as a value can be set to either VARIANT_TRUE or VARIANT_FALSE.
Rowset properties/Cursor models |
Default result set (RO) |
Fast Forward- only (RO) |
Static (RO) |
Keyset driven (RO) |
|---|---|---|---|---|
| DBPROP_SERVERCURSOR | F | T | T | T |
| DBPROP_DEFERRED | F | F | - | - |
| DBPROP_IRowsetChange | F | F | F | F |
| DBPROP_IRowsetLocate | F | F | - | - |
| DBPROP_IRowsetScroll | F | F | - | - |
| DBPROP_IRowsetUpdate | F | F | F | F |
| DBPROP_BOOKMARKS | F | F | - | - |
| DBPROP_CANFETCHBACKWARDS | F | F | - | - |
| DBPROP_CANSRCOLLBACKWARDS | F | F | - | - |
| DBPROP_CANHOLDROWS | F | F | - | - |
| DBPROP_LITERALBOOKMARKS | F | F | - | - |
| DBPROP_OTHERINSERT | F | T | F | F |
| DBPROP_OTHERUPDATEDELETE | F | T | F | T |
| DBPROP_OWNINSERT | F | T | F | T |
| DBPROP_OWNUPDATEDELETE | F | T | F | T |
| DBPROP_QUICKSTART | F | F | - | - |
| DBPROP_REMOVEDELETED | F | F | F | - |
| DBPROP_IRowsetResynch | F | F | F | - |
| DBPROP_CHANGEINSERTEDROWS | F | F | F | F |
| DBPROP_SERVERDATAONINSERT | F | F | F | - |
| DBPROP_UNIQUEROWS | - | F | F | F |
| DBPROP_IMMOBILEROWS | - | - | - | T |
Rowset properties/Cursor models |
Dynamic (RO) | Keyset (R/W) | Dynamic (R/W) |
|---|---|---|---|
| DBPROP_SERVERCURSOR | T | T | T |
| DBPROP_DEFERRED | - | - | - |
| DBPROP_IRowsetChange | F | - | - |
| DBPROP_IRowsetLocate | F | - | F |
| DBPROP_IRowsetScroll | F | - | F |
| DBPROP_IRowsetUpdate | F | - | - |
| DBPROP_BOOKMARKS | F | - | F |
| DBPROP_CANFETCHBACKWARDS | - | - | - |
| DBPROP_CANSRCOLLBACKWARDS | - | - | - |
| DBPROP_CANHOLDROWS | F | - | F |
| DBPROP_LITERALBOOKMARKS | F | - | F |
| DBPROP_OTHERINSERT | T | F | T |
| DBPROP_OTHERUPDATEDELETE | T | T | T |
| DBPROP_OWNINSERT | T | T | T |
| DBPROP_OWNUPDATEDELETE | T | T | T |
| DBPROP_QUICKSTART | - | - | - |
| DBPROP_REMOVEDELETED | T | - | T |
| DBPROP_IRowsetResynch | - | - | - |
| DBPROP_CHANGEINSERTEDROWS | F | - | F |
| DBPROP_SERVERDATAONINSERT | F | - | F |
| DBPROP_UNIQUEROWS | F | F | F |
| DBPROP_IMMOBILEROWS | F | T | F |
For a given set of rowset properties, which cursor model is selected is determined as follows.
From the given collection of rowset properties, obtain a subset of properties that is listed in the above tables. Divide these properties into two subgroups depending on the flag value (required (T, F) or optional (-)) of each of the rowset properties listed in the above tables. For each cursor model from left to right (starting from the first table), compare the values of the properties in the two subgroups with the values of the corresponding properties at that column. The cursor model that has no mismatch with the required properties and the least number of mismatches with the optional properties is selected. If there is more than one cursor model, the leftmost is chosen.
When a SQL Server cursor supports a SQLOLEDB rowset, the number of elements in the row handle array parameter of the IRowset::GetNextRows or the IRowsetLocate::GetRowsAt methods defines the cursor block size. The rows indicated by the handles in the array are the members of the cursor block.
For rowsets supporting bookmarks, the row handles retrieved by using the IRowsetLocate::GetRowsByBookmark method define the members of the cursor block.
Regardless of the method used to populate the rowset and form the SQL Server cursor block, the cursor block is active until the next row-fetching method is executed on the rowset.
To obtain FAST_FORWARD cursor
