Excel + VBA + ADO -> Oracle: Automation Error & Excel crashes

[Burser]

Howdy,

I tried to fit all the gubbins I'm using in the title. I am trying to execute a stored procedure on an Oracle 10g database. To do this I use the following code:

Public Function ExecuteStoredProcedure(sStoredProcedure As String, ByRef vParameters() As Variant) As ADODB.Parameters
On Error GoTo Catch
Dim bRaceFlag As Boolean: bRaceFlag = False

    Dim dCon As ADODB.Connection
    Set dCon = Me.GetConnection(bStandAlone:=True)
    
    If dCon Is Nothing Then Exit Function
    
    If dCon.State <> adStateOpen Then dCon.Open
    
    Dim dCommand As New ADODB.Command
    
    Set dCommand.ActiveConnection = dCon
    dCommand.CommandType = adCmdStoredProc
    dCommand.CommandTimeout = 60000
    dCommand.CommandText = sStoredProcedure
    dCommand.NamedParameters = True
    
    dCommand.Parameters.Refresh ' Does nothing!
    
    Dim vParam As Variant
    For Each vParam In vParameters
        Call dCommand.Parameters.Append(vParam)
    Next vParam

    Call dCommand.Execute   ' Crash happens here
    
    Set ExecuteStoredProcedure = dCommand.Parameters    '<-- Don't ask
    
Finally:
    If Not bRaceFlag Then
        bRaceFlag = True
    End If
    Exit Function
    
Catch:
    Debug.Print "DatabaseManager::ExecuteStoredProcedure: " & Err.Description
    Resume Finally
End Function

I'm hoping that the relevent calls and bits are self explanatory. bStandAlone in the call to GetConnection causes the method to construct and set-up, but not open, a new connection.

Sadly, when I get to .Execute, Excel explodes and either puts on the standard "Send this error report" window, or throws an automation error - 'The Object invoked has Disconnected from its Clients.'

Does anyone know what's going on? I'm going to pay attention to the tread, so ask any other info you need. I'm happy to post any of the other bits of code used here if you need then.

 

[Burser]

Update on the problem:

I just enabled tracing on the local ODBC drivers. No ODBC trace is genarated for that /entire/ method.

Not for creating the connection - not for opening it - running Refresh on the parameters - nada. My ExecuteScalar, ExecuteNonQuery and ExecuteQuery methods all trace perfectly.

This is very odd - the call doesn't seem to be making it out of ADO into the ODBC layer.

 

[Burser]

Dear TekTips - I would like to be able to edit my posts. If I already can, I am too stupid to work out how - this may explain why I'm banging my head against my PC crying out to any god that will hear me to strike down the person who wrote ADO and all that sail in it. Yours, Josh.

Update:

I just got a real error code:

System Error &H80010108 (-2147417848)

 

[Welshbird]

Burser - check the switches for the time-out.

The default timeout in the ADO isn't long enough IMHO.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Burser]

Both the command and connection timeouts are set to 60000 whilst I'm debugging - I'll put some more sensible timeouts in later. I've fallen for that one in the past

The query is falling over nearly instantly (slight delay - presumably gathering crash information).

-- Josh (also in Wales)