PRB: Passing a Recordset to a DLL to Do an UpdateBatch More Than Once Causes Error (270636)

CAUSE

When you have an out-of-process DLL in a 3-tier application, and the DLL is responsible for handling updates to the Oracle server, the following sequence of events may cause this error to occur:

The DLL returns an ADO Recordset to the client.
The client appends a new record to the Recordset and sends the Recordset back to the DLL.
The DLL calls UpdateBatch to commit the changes to the Recordset.
The client modifies the new record and sends the Recordset to the DLL again.
When the DLL calls UpdateBatch again, the error occurs.

The root problem is that a hidden ROWID column in the Recordset is still set to NULL after the first update on the client-side Recordset. This is because ROWID columns are automatically incremented by the server, and in this case, the client-side Recordset is disconnected; in other words, it has no server connection and therefore cannot be updated automatically. Because this column value is never updated on the client-side, all subsequent updates using this invalid ROWID fail.

RESOLUTION

To prevent this error, set the "Determine Key Columns For Rowset" dynamic property on the Recordset object. The property must be set before the CursorLocation is set to adUseClient. This property is provider-specific, and although it cannot be set once you've asked for a client-side cursor, the property value is retained and used even after the cursor location changes.

Also, in order to ensure that this property is used by the provider, set the ActiveConnection property of your Recordset before setting the "Determine Keys For Rowset" property, and do not pass a connection string into the Open method call for your Recordset.

In the code example in the "More Information" section, the error occurs even though this property is set, because a connection string is passed into the Open call. This forces ADO to create a new Connection object to execute the query, and the property setting is not saved. See the comments within the code for more information on how to prevent this from happening.

MORE INFORMATION

Steps to Reproduce Behavior

Create a table on an Oracle server by using the following SQL statement:

create table mhtest (test_id number primary key, txt_fld varchar2(10));

In Microsoft Visual Basic 6.0, create a new ActiveX DLL project named "testmdac".
Set a Project Reference to the ActiveX Data Objects 2.5 Library.

Paste the following code in the default class module of the project. You will need to change the connection strings below to work with your server:

Option Explicit

Public Function Load1() As ADODB.Recordset
    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
    
    'Fails only with OLE DB Provider for Oracle.
    strconnect = "provider=msdaora;password=password;user id=user;data source=oraclesrv;"
    'Works with the following:
    'strConnect = "provider=msdasql;DRIVER={Microsoft ODBC for Oracle};pwd=password;uid=user;server=oraclesrv;"

    With cn
       .ConnectionString = strconnect
       .Open
    End With

    'Get the SQL statement.
    strSQL = "SELECT * FROM mhtest"

    With rs
        .ActiveConnection = cn
        
        'Note: CursorLocation should be set to adUseServer by default.
        .CursorLocation = adUseServer 
        .Properties("Determine Key Columns For Rowset") = True
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        
        'The following causes the above dynamic property not to be used.
        .Open strSQL, strconnect, adOpenStatic, adLockBatchOptimistic

        'Instead, use the following:
        '.Open strSQL, , adOpenStatic, adLockBatchOptimistic
 
        'Disconnect the recordset.
        Set .ActiveConnection = Nothing
    End With

    Set Load1 = rs
    Set rs = Nothing
End Function


Public Sub Save(ByVal rs As ADODB.Recordset)
    Dim strconnect As String
    Dim cn As ADODB.Connection
    
    Set cn = New ADODB.Connection
    
    'Fails only with OLE DB Provider for Oracle.
    strconnect = "provider=msdaora;password=password;user id=user;data source=oraclesrv;"
    'Works with the following:
    'strConnect = "provider=msdasql;DRIVER={Microsoft ODBC for Oracle};pwd=password;uid=user;server=oraclesrv;"

    With cn
       .ConnectionString = strconnect
       .Open
    End With
 
    With rs
       .ActiveConnection = cn
       .UpdateBatch '<---------- Error will occur here second time around
       Set .ActiveConnection = Nothing
    End With
End Sub

From the Run menu, click Start with full compile so that you can test the DLL out of process. If prompted, choose Wait for Components to be created and then click OK.
Start another instance of Visual Basic 6.0.
Create a new Standard EXE project.
Set a Project Reference to the ActiveX DLL project that you just created, which is named "testmdac.vbp".
Set a Project Reference to the ActiveX Data Objects 2.5 Library.

Paste the following code in the Load method of the default form:

Option Explicit

Private Sub Form_Load()
    Dim rs As ADODB.Recordset
    Dim DS As testmdac.Class1
  
    Set DS = CreateObject("TESTMDAC.Class1")
    Set rs = DS.Load1
    
    rs.AddNew
    rs(0) = 42
    rs(1) = "TEST1"
    
    DS.Save rs
    rs.UpdateBatch
    
    rs.MoveLast
    rs(1) = "TEST1"

    DS.Save rs
    rs.UpdateBatch
    Set rs = Nothing
End Sub

Step into the code by pressing F8.

NOTE: Before you run the code, be sure to delete any existing rows in the table; otherwise, you may get an error due to a duplicate primary key.