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 "successfully submitted", 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 = "Provider=MSDAORA;Data Source=MyOracleServer;" & _
"User ID=MyUID;Password=MyPwd;"
cn.Open strConnect
strSQL = "SELECT * FROM MyTable"
With rs
.ActiveConnection = cn
.CursorLocation = adUseServer
.Properties("Determine Key Columns For Rowset") = True
.CursorLocation = adUseClient
'Note that the ActiveConnection parameter below is empty
.Open strSQL, , adOpenStatic, adLockBatchOptimistic
End With