Re: Unnecessary calls to DBMS_DESCRIBE Oracle Stored procedure

From: Hooman (Hooman_at_discussions.microsoft.com)
Date: 01/27/05 

Date: Thu, 27 Jan 2005 09:23:02 -0800

Stephen,

No, We manually setup the parameters. It is the way that I am calling the
stored procedures:

**********************************************
    Dim objCmd As ADODB.Command
    Dim objRs As ADODB.Recordset
    Dim sCommandText As String
    Dim objConnection as ADODB.Connection

    Set objCmd = New ADODB.Command
    With objCmd

        'Open an active connection to ORACLE database
        Set objConnection = New ADODB.Connection
        With objConnection
           .ConnectionString = "PROVIDER=MSDAORA;" & _
                                         "User ID=useid;Password=password;"
& _
                                         "Data Source=ServerName;" & _
                                         "Driver={Microsoft ODBC for
Oracle};" & _
                                         "DSN="
           .CursorLocation = adUseClient
           .Open
        End With
        Set .ActiveConnection = objConnection
        
        'Set the command type and text for the stored procedure
        .CommandType = adCmdStoredProc
        .CommandText = "PKGName.SPName"

        
        'Create the parameters
        .Parameters.Append .CreateParameter("iFirstParam", _
                                                                 adInteger, _
                                                                
adParamInput, , _
                                                                a_lFirstParam)
        .Parameters.Append .CreateParameter("iSecondParam", _
                                                                 adInteger,
adParamInput, , _
                                                                 
a_lSecondParam)
        .
        .
        .
        .Parameters.Append .CreateParameter("oOutParam", _
                                                                 adNumeric, _
                                                                 
adParamOutput)
        
        'Create the result Recordset
        Set objRS = New ADODB.Recordset
        Set objRS = .Execute
    
        'Save the returned output
        a_lOutParam = IIf(IsNull(.Parameters("oOutParam").Value), 0, _
                                   .Parameters("oOutParam").Value)
        
        Set .ActiveConnection = Nothing
    End With

    Set objConnection = Nothing
    Set objRS = Nothing
    Set objCmd = Nothing

**********************************************

Thanks
Hooman

"Stephen Howe" wrote:

> > Do you have any idea to prevent those extra calls?
>
> Are you calling Parameter.Refresh to setup the parameters? If so, that is
> the extra call. Best manually setup parameters
>
> Stephen Howe
>
>
>

Relevant Pages

  • Page not found with mobile application..
    ... 'Protected Sub frmMain_Activate(ByVal sender As Object, ... Dim objCmd As SqlCommand = New SqlCommand ... Dim bln As Boolean ...
    (microsoft.public.dotnet.framework.aspnet.mobile)
  • Re: Zugriff auf SQLServer DB?
    ... > Name der DB, PW, Username und SELECT statement habe ich. ... In VBA schreibe ich meine DB-Abfragen so: ... Dim objCMD As ADODB.Command ...
    (microsoft.public.de.excel)
  • Re: Zugriff auf SQLServer DB?
    ... Sub DB_Zugriff ... Dim objCMD As ADODB.Command ... Man sollte die Verbindung auch wieder schliessen. ...
    (microsoft.public.de.excel)
  • Re: How do I find modified date of table
    ... Dim objConn,db,DataConn ... Set objConn = Server.CreateObject ... Dim objRS ... If I change the strSQL line to: ...
    (microsoft.public.inetserver.asp.db)
  • ASP - Type mismatch problem
    ... dim dateczka ... Dim objRS, Param1, Param2 ... Provider error '80020005' ... I have even write the same examples which was written on the Polish ...
    (microsoft.public.scripting.vbscript)