Opening an Existing Data Set

To access any of the data sets from an established data source connection, an OLE DB session object first needs to be created by calling IDBCreateSession::CreateSession(). The session object spawns rowset objects which are used for reading, creating, and updating data sets.

The OLE DB rowset component encapsulates a specific data set. This component can be thought of as a cursor over a data set that enables you to read and update the underlying data set. Data sets are opened via the OpenRowset() method on the session object's IOpenRowset interface.

Specifying the Data Set

Arguments to IOpenRowset::OpenRowset() include a DBID structure which specifies the TableID and rowset properties.

The TableID is the DBID structure that is defined by OLE DB. The IOM provider only looks at two members in this structure: eKind and uName. When setting up an instance of this structure, include the name of the physical data set to open. (More on this below.) The eKind member indicates where the provider should look within the DBID to find the name of the table to open within the rowset. DBKIND_NAME indicates the provider should only look at the pwszName member of the uName union. Other naming combinations in the DBID structure are not supported. The following code fragment demonstrates how to set up a DBID structure to refer to a data set named FITNESS.

DBID TableID;
TableID.eKind = DBKIND_NAME;
TableID.uName.pwszName = L"FITNESS";

In addition to the TableID, the client may set properties in the DBPROPSET_SAS_ROWSET property set to further identify the data set. These properties are:

• DBPROP_SAS_PATH - a physical path name to associate with the libref.
• DBPROP_SAS_ENGINE - a SAS engine name to associate with the libref. If this is not specified the default engine will be used.
• DBPROP_SAS_LIBOPTS - a string of SAS library/engine options to use when creating a libref.

There are several ways to specify which data set to open. They involve various combinations of setting TableID, and the DBPROP_SAS_PATH rowset property:

1. If the data set belongs to an assigned libref, simply give a libname.memname pair for the TableID. Do not set any properties.
2. If the data set does not belong to an assigned libref, you may create a new libref by using the IOM method AssignLibref(), which is on the IDataService interface. Then give a libname.memname pair for the TableID. Do not specify any properties.
3. If the data set does not belong to an assigned libref and the client does not need a Libref name, the IOM provider will assign one on behalf of the client. Give only memname for TableID. Also set the The DBPROP_SAS_PATH property to specify the path name of the SAS data library being referenced. The provider then generates a libref name on behalf of the caller. Setting DBPROP_SAS_ENGINE and DBPROP_SAS_LIBOPTS are optional. They will be used in the creation of the new libref. If all three of these properties were set by the client, a call to IOpenRowset::OpenRowset() would have an effect similar to this SAS statement:

LIBNAME libref engine "physpath" options; 

Open Mode

An OLE DB rowset is opened in either read-only or update mode. The access mode for each rowset is negotiated by the client and the IOM provider when the rowset is created. Once the access mode has been negotiated, it is maintained for the life of the rowset. The access mode on the rowset cannot be changed.

A rowset's open mode is determined by the interface that it supports. A rowset surfacing IRowsetChange is in update mode. Read-only rowsets do not surface IRowsetChange. The IOM provider by default does not surface IRowsetChange on its rowsets, so it opens rowsets in read-only mode. To open a data set for update access, pass a DBPROP_IRowsetChange property value of VARIANT_TRUE via IOpenRowset::OpenRowset() .

Update Modes

Updateable rowsets support one of two update modes: delayed or immediate . Immediate update mode transmits changes immediately to the data source. Delayed update mode buffers a set of changes (to the same row or multiple rows) and transmits them to the data source on request.

The access mode of a rowset is determined by the interfaces that rowset surfaces. As mentioned in the previous section, all updateable rowsets surface IRowsetChange. If a rowset also surfaces IRowsetUpdate, it is in delayed update mode. A rowset surfacing IRowsetChange and not IRowsetUpdate is in immediate update mode. (A rowset cannot surface IRowsetUpdate without also surfacing IRowsetChange.)

By default, IOM provider rowsets expose neither IRowsetChange nor IRowsetUpdate. To open a data set for immediate update access, pass a DBPROP_IRowsetChange property value of VARIANT_TRUE via IOpenRowset::OpenRowset(). To open a data set for delayed update access, pass a value of VARIANT_TRUE for both DBPROP_IRowsetChange and DBPROP_IRowsetUpdate via OpenRowset::OpenRowset().

Lock Modes

When you are using the data provider to update a rowset, make sure that no other task is also updating the same data set. Otherwise, one task may overwrite another task. To safeguard against interference from other tasks, a provider may implement a locking strategy for updateable rowsets.

The IOM provider implements two different strategies: pessimistic and optimistic locking. The client sets the lock mode when opening the rowset, and this determines which locking strategy is used throughout the life of the rowset. The next section tells how the client sets the lock mode.

Pessimistic locking assumes that collisions between transactions are common. The client is pessimistic about the row being unchanged between the time the row is read and the time it is updated or deleted. The IOM provider locks the entire data set, giving it exclusive access to each row. When the provider locks the data set, no other task may have access to that data set until the client releases the rowset. The IOM provider implements pessimistic locking by

1. opening the data set with exclusive access
2. reading any rows
3. making changes to its local copy of the rows
4. transmitting the changes to the data source.

Optimistic locking assumes that collisions between rowsets over the same physical data set will rarely occur. That is to say, the client is optimistic that a given row will not change in the data source between the time it is read and the time it is updated or deleted. The provider locks one row at a time, as needed. When the provider has a row locked, no other task may have access to that row. Specifically, the IOM provider implements optimistic locking by:

1. opening the data set
2. reading a row from the data set
3. making changes to its local copy of the row and buffering the changes
4. locking the row on the data set when requested to transmit the changes
5. comparing its local copy of the row to the row's current value on the data set, ensuring the row has not changed because the provider last read it.
6. transmitting the change to the data source.

between steps 2 and 4, if another task either locks the row or changes the row, the update will fail.

The IOM provider by default implements optimistic locking on the rowsets that it opens.

Which lock mode should a client select? Optimistic concurrency enables multiple users to access a data set concurrently. This may be the only acceptable solution in multiuser environments. However, be aware that optimistic concurrency may result in reduced performance. The IOM provider has to do the additional processing of rereading each row before transmitting its changes to see if it has been changed because it was last read. If allowing multiple users access to the same data concurrently is not a requirement, then pessimistic locking usually results in the best performance.

Setting the Lock Mode

Clients determine the lock mode when the rowset is opened. On the IRowsetOpen::OpenRowset() method clients set the property DBPROP_SAS_OPTIMISTICLOCKING, which is in the DBPROPSET_SAS_ROWSET property set. The default setting for this property is TRUE. Therefore, this property only needs to be set when requesting pessimistic locking.

Random and Sequential Access

By definition, a rowset is a random access rowset when the IRowsetLocate interface is exposed. The IOM provider exposes IRowsetLocate on data set rowsets by default. This behavior can be controlled using the DBPROP_IRowsetLocate property. Set this property value to VARIANT_FALSE when opening a rowset to indicate that you have no need for random access.

In ADO, only the static cursor type is supported by the IOMProvider. If you specify another cursor type, the IOMProvider will return a static cursor.