Microsoft KB Archive/258997

= INFO: Oracle OLE DB Provider and ROWID Use =

Article ID: 258997

Article Last Modified on 12/3/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0
 * Microsoft OLE DB Provider for Oracle Server 1.0

-



This article was previously published under Q258997



SUMMARY
Beginning with Microsoft Data Access Components (MDAC) 2.1, the Microsoft OLE DB Provider for Oracle implements the use of ROWIDs for unique row identification. This may generate some unexpected side effects in some circumstances.



MORE INFORMATION
If you open an updateable ActiveX Data Objects (ADO) Recordset with a CursorLocation of adUseClient, the Microsoft OLE DB Provider for Oracle adds the ROWID column for the tables referenced in your query. The ROWID columns are implemented as a hidden column in the rowset and cannot be viewed or modified.

The ADO cursor engine allows you to modify data stored in the Recordset and translates those changes into action queries (UPDATE, DELETE, INSERT) to modify the appropriate rows in your database. The Microsoft OLE DB Provider for Oracle marks the ROWID columns so that the ADO cursor engine uses the ROWID values to locate which rows in your database to modify.

Without this feature, the ADO cursor engine needs to base the updates on a primary key or a unique index on the table. Basing the updates on ROWIDs rather than on a primary key or a unique index improves performance because the ADO cursor engine no longer needs to retrieve this metadata. Thus, the ADO cursor engine queries the database for ROWID information prior to retrieving the results of the original query.

While this is an extremely useful feature, it can cause some unexpected behavior for applications that are not designed with rowset resynchronization in mind. For example, you may run a query that retrieves a row into a client-side Recordset. Another user may delete that row after your query completes. Oracle occasionally re-uses ROWIDs. There is a possibility that another user may insert a new row into your table that would have the same ROWID as the row you originally retrieved into your client-side Recordset.

Also, it is common practice to create a rowset that uses client-side (disconnected) cursors, and to perform insertions of new records through that rowset. When you submit the new rows to the database by calling Update or UpdateBatch (depending on your choice of LockType for the Recordset), ADO automatically fetches the new server-generated ROWID value for each new row and stores that data in the Recordset. Keep in mind that the ROWID columns in your Recordsets are hidden and read-only.

In multi-tiered applications, many developers pass their Recordset across process boundaries (for example, from a client to a COM+ or Microsoft Transaction Server [MTS] object) and let the business object running on the middle tier server submit the changes to the database. To mark the changes in the Recordset in the client application as &quot;successfully submitted&quot;, by calling UpdateBatch on the disconnected Recordset. In this scenario, you are unable to modify any of the newly added rows to the Recordset until you have re-fetched these rows from the database because the new ROWID values have not been sent back from the Recordset on the server that actually submitted the new rows to the database. Instead, the following error message occurs:

Run-time error '-2147217864 (80040e38)':

Row cannot be located for updating. Some values may have been changed since it was last read.

The OLE DB provider for Oracle offers a means of controlling whether or not the ROWID is used, through the use of a provider-specific property called Determine Key Columns for Rowset. The Determine Key Columns for Rowset property tells the provider whether or not it must perform the extra work of identifying the unique index columns for the rowset (the property is set to TRUE) or whether it should rely on ROWIDs alone (the property is set to FALSE). The property is FALSE by default, so ROWIDs are ordinarily used.

Because this property is a provider-specific property, it must be set when the CursorLocation property of the rowset is set to adUseServer, while the provider-specific properties are still available. When the CursorLocation is set to adUseClient, the client cursor engine can only enumerate through the predefined set of properties it knows about, and attempts to reference provider-specific properties fail. However, the property remains set and will be honored by the provider.

Preserving the property also requires you to set the ActiveConnection property of the Recordset before you open the recordset, as opposed to providing the connection object or connection string as a parameter to the rs.Open(...) method. If a connection object or string is provided for rs.Open, it causes ADO to discard the property set that has already been established for the recordset, and provides instead a default set of properties that do not include any provider-specific properties.

Additionally, including the DISTINCT keyword in the SELECT statements also causes the Microsoft OLE DB provider for Oracle to drop the use of ROWIDs.

Note that neither the OLE DB provider for ODBC from Microsoft nor the OLE DB provider for Oracle from Merant include the selection of ROWIDs. The native OLE DB provider from Oracle does include the selection of ROWIDs but does not offer any means of excluding it if needed.

The following code sample demonstrates one way to accomplish preservation of the Determine Key Columns For Rowset property: Dim strConnect As String Dim rs As ADODB.Recordset Dim cn As ADODB.Connection Dim strSQL As String

Set rs = New ADODB.Recordset Set cn = New ADODB.Connection strConnect = &quot;Provider=MSDAORA;Data Source=MyOracleServer;&quot; & _ &quot;User ID=MyUID;Password=MyPwd;&quot; cn.Open strConnect

strSQL = &quot;SELECT * FROM MyTable&quot; With rs   .ActiveConnection = cn    .CursorLocation = adUseServer .Properties(&quot;Determine Key Columns For Rowset&quot;) = True .CursorLocation = adUseClient 'Note that the ActiveConnection parameter below is empty .Open strSQL,, adOpenStatic, adLockBatchOptimistic End With

