Re: IIS Oracle Stored Procedures

Ben is correct that this should solve your problem. The main difference between his example and the original post is the explicit versus implicit creation of parameters.

See http://www.microsoft.com/Mind/1198/ado/ado.htm for more information on the differences between explicit and implicit parameter creation, as well as other ways to optimize ASP and ADO. This article explains that you should not implicitly create parameters by using the Refresh call if it can be avoided. If you already know the parameters, use the command object's CreateParameter method as in Ben's example.

I would wager that this is the cause of Dave's woes, as Oracle may not wish to return parameter information through the Refresh call due to permissions.

Kirk Allen Evans
kaevans_at_yahoo.com

In article <7mkoeo$g3k$1_at_nnrp1.deja.com>,   ben.salemi_at_gcgc.com wrote:
> Here is code that works for us ...
>
> Set DbConnection = Server.CreateObject("ADODB.Connection")
> DbConnection.connectionTimeout= yourConnectionTimeout
> DbConnection.open yourConnectionString
>
> set cmd = Server.CreateObject("ADODB.Command")
> set cmd.ActiveConnection = DbConnection
>
> cmd.CommandText = "Login"
> cmd.CommandType = adCmdStoredProc
> cmd.Parameters.Append cmd.CreateParameter("userId", adVarChar,
> adParamInput, 30)
> cmd.Parameters.Append cmd.CreateParameter("password",
adVarChar,
> adParamInput, 30)
> cmd.Parameters.Append cmd.CreateParameter("userSeqNbr",
> adInteger,
> adParamOutput)
> cmd.Parameters.Append cmd.CreateParameter("userLevel",
> adVarChar,
> adParamOutput, 30)
> cmd.Parameters.Append cmd.CreateParameter("returnCode",
> adVarChar,
> adParamOutput, 50)
> cmd.Parameters("userId") = userId
> cmd.Parameters("password") = password
> cmd.Execute
> userSeqNbr=cmd("userSeqNbr")
> returnCode=cmd("returnCode")
> userLevel=cmd("userLevel")
>
> A few gotchas:
> - MS ODBC driver for Oracle can't handle more than 10 parameters
> although oracle's can
> - MS ODBC driver for Oracle can't handle oracle synonyms that refer
> to stored procedures although Oracles's can
> - I have seen postings that suggest that Oracle's odbc driver is
not
> good for use with ADO 2.0 and that ADO 2.1 doesn't want to play
> nice with Oracle at all. Haven't been able to confirm that.
> - make sure you explicitly set all of your objects to nothing when
> you are done with them
>
> Hope this helps ...
>
> Ben
>
> In article <932037477.9407.0.nnrp-01.9e98ba5c_at_news.demon.co.uk>,
> "Dave Monks" <DMonks_at_CompassC.Demon.Co.UK> wrote:
> > Does anyone know how to execute a stored procedure in an Oracle
> database
> > from an ASP script so that I can pass parameters to and from the
> procedure.
> >
> > The code I was using for MSSQL Server was:
> >
> > set cmd = Server.CreateObject ("ADODB.Command")
> > cmd.ActiveConnection = cn
> > cmd.CommandText = "{call SalesOrgNew (?,?,?,?,?,?,?,?)}"
> > cmd.CommandType = 1
> > cmd.parameters.refresh
> > cmd.parameters("@SalesOrgID") = SalesOrgID
> > cmd.parameters("@SalesOrgCode") = SalesOrgCode
> > cmd.parameters("@SalesOrgName") = SalesOrgName
> > cmd.parameters("@Status") = Status
> > cmd.parameters("@CreatedBy") = Session("UserName")
> > cmd.parameters("@CreatedOn") = date() cmd.parameters
("@ActiveStart")
> =
> > cdate(ActiveStart) cmd.parameters("@ActiveEnd") = cdate(ActiveEnd)
> > cmd.execute recordsaffected
> > SalesOrgID = cmd.parameters("@SalesOrgID")
> >
> > Trying to use the same code with Oracle gives me the following error
> > message:
> >
> > Microsoft OLE DB Provider for ODBC Drivers error '80040e51'
> > The provider cannot derive parameter info and SetParameterInfo has
not
> been
> > called
> >
> > Anyone any ideas ?
> >
> > Thanks in advance for any help received.
> >
> > Dave Monks
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

--
Kirk Allen Evans
kaevans_at_yahoo.com

(Do not send mail to the Deja News account used

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 19 1999 - 13:08:55 CDT