Re: IIS Oracle Stored Procedures

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. Received on Thu Jul 15 1999 - 08:41:47 CDT