MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for SQL Server
Microsoft® SQL Server™ returns result sets to consumers using two methods. The advantages of each method can be described as follows:
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 until 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 by using a default result set. If any of these properties are set to a value other than the default, SQLOLEDB implements the rowset by 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_IRowsetScroll require bookmarks in the rowset. Attempting to create a rowset with bookmarks and DBPROP_OTHERINSERT set to VARIANT_TRUE results in an error. |
| 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_IMMOBILEROWS is available only in rowsets that can show SQL Server rows inserted by commands on other sessions or by other users. Attempting to open a rowset with the property set to VARIANT_FALSE on any rowset for which DBPROP_OTHERINSERT cannot be VARIANT_TRUE results in an error. |
| 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.
The two tables below 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:
To use a certain type of cursor model, locate the column corresponding to 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, the cursor model selected is determined as follows:
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.
See Also
How to Obtain a FAST_FORWARD Cursor
"Block Cursors" in SQL Server Books Online