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:31:05 -0800

I forgot to say that the stored procedure is being called in the source code
is something like:

PKGName: Procedure SPName(
iFirstParam IN Number,
iSecondParam IN Number,
.
.
.
oRecordset OUT Cursor,
oOutParam OUT Number
)

"Hooman" wrote:

> 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

  • Re: Passing Dates to stored procedure - parameter headache.
    ... command type because I'm now running a command, not a stored procedure. ... procedure in SQL 2005. ... Dim prm1 As ADODB.Parameter ...
    (microsoft.public.access.modulesdaovba)
  • Stored procedure Output Parameter headache
    ... I have the following code chunk for a page which accesses an SQL Server 2000 stored procedure that takes an SSN and a Last Name and checks our database for a match. ... Dim cmdDWExport ... .Parameters.Append .CreateParameter("@LastName", adVarChar, adParamInput, 25, txtLName) ... declare @RetMsg varchar ...
    (microsoft.public.inetserver.asp.db)
  • Re: SQL Server -- Bulk Insert from Excel to SQL Server
    ... Dim oConn As Object ... Set oConn = CreateObject ... ' Set CommandText equal to the stored procedure name. ... Private Sub DeleteBlankRows() ...
    (microsoft.public.sqlserver.datawarehouse)
  • RE: Using stored procedure and ado to display recordset
    ... I have a stored procedure that is in the pubs database. ... select title_id, title, type, royalty ... Dim rs As New ADODB.Recordset ... Dim curPercent As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Retrieving the stored procedure parameter list in ado.net
    ... It might not work with the OLEDB provider. ... > to do this using OLEDB and it doesn't seem to support optional parameters, ... > Dim cn As OleDbConnection = _ ... The stored procedure part is not an issue, ...
    (microsoft.public.dotnet.framework.adonet)