|
 |
asp_databases thread: Returning a recordset _and_ ouput parameters from a stored procedure
Message #1 by "Joel Raha" <jraha@g...> on Tue, 17 Apr 2001 22:29:36
|
|
I want to return a recordset as well as output parameters from a stored
procedure...
I'm not sure how to do this or if this is possible.
Here's what I have.
Set con = Server.CreateObject ("ADODB.Connection")
con.Open "DSN=" & DBLIB_DATABASE_NAME, DATABASE_PASSWORD, ""
Set cmd = Server.CreateObject ("ADODB.Command")
cmd.CommandType = adCmdStoredProc '4
'cmd.CommandType = adCmdText '4
Set cmd.ActiveConnection = con
'stored procedure returns a recordset and 2 output parameters
cmd.CommandText = "usp_PTO_InsertRequestLog"
cmd.Parameters.Refresh()
'Then simply apply the initial values...
cmd.Parameters.Item(1).Value = 2293
cmd.Parameters.Item(2).Value = 44
cmd.Parameters.Item(3).Value = "OUTPUT1"
cmd.Parameters.Item(4).Value = 0
'To call this without the recordset returned
cmd.Execute ,cmd.Parameters
'But to call this and return the output parameters...
'I'm quite stuck here. any ideas?
dim rs
set rs = Server.CreateObject("ADODB.Recordset")
rsValues.Open cmd cmd.Parameters,?,?,?,?
Message #2 by "Charles Feduke" <webmaster@r...> on Tue, 17 Apr 2001 18:24:37 -0400
|
|
From an earlier post (sorry for the additional post everyone):
> This is a wee bit more complicated than one would first think.
>
> If your stored procedure has IN/OUT parameters (that is you are
> passing
> ByRef - call 'em pointers) then you will need to create a Parameters
object
> and populate it with names, datatypes (there's a listing of constants
like
> adVarChar, etc), sizes, and initial values. You then tie the Parameters
> object to a command object and finally use a command object to execute
the
> stored procedure.
>
> However, if your stored procedure returns a recordset, you can set
a
> recordset to a connection.Execute's returned recordset.
>
> First, make sure there is a reference to ADO 2.1 in your project.
> sConString is a valid connection string to the Oracle server. Let's
assume
> I have a stored proc ("sp_mystery") in an Oracle server that accepts a
> VARCHAR(25) IN only ('blah_type') and a SMALLINT IN/OUT ('ret_code'):
>
> Dim cmdCommand As New ADODB.Command
> cmdCommand.ActiveConnection = sConString
> With cmdCommand
> .CreateParameter "blah_type", adVarChar, adParamInput, 25, "test"
> .CreateParameter "ret_code", adSmallInt, adParamInputOutput, , 0
> .CommandText = "sp_mystery"
> .CommandType = adCmdStoredProc
> .Execute
> MsgBox .Parameters("ret_code")
> End With
>
> And now we have a stored procedure named "sp_ret_records" that
> returns a
> recordset:
>
> Dim conConnection As New ADODB.Connection
> Dim rsRecordset As ADODB.Recordset
> With conConnection
> .Open sConString
> Set rsRecordset = .Execute "SELECT * FROM invoice ORDER BY cust_id", _
> , adOpenForwardOnly + adCmdText
> End With
>
> Hope that helps.
>
> - Chuck
----- Original Message -----
From: "Joel Raha" <jraha@g...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, April 17, 2001 10:29 PM
Subject: [asp_databases] Returning a recordset _and_ ouput parameters from a
stored procedure
> I want to return a recordset as well as output parameters from a stored
> procedure...
>
> I'm not sure how to do this or if this is possible.
>
> Here's what I have.
>
>
> Set con = Server.CreateObject ("ADODB.Connection")
> con.Open "DSN=" & DBLIB_DATABASE_NAME, DATABASE_PASSWORD, ""
> Set cmd = Server.CreateObject ("ADODB.Command")
> cmd.CommandType = adCmdStoredProc '4
> 'cmd.CommandType = adCmdText '4
> Set cmd.ActiveConnection = con
>
>
> 'stored procedure returns a recordset and 2 output parameters
> cmd.CommandText = "usp_PTO_InsertRequestLog"
> cmd.Parameters.Refresh()
> 'Then simply apply the initial values...
> cmd.Parameters.Item(1).Value = 2293
> cmd.Parameters.Item(2).Value = 44
> cmd.Parameters.Item(3).Value = "OUTPUT1"
> cmd.Parameters.Item(4).Value = 0
>
> 'To call this without the recordset returned
> cmd.Execute ,cmd.Parameters
>
> 'But to call this and return the output parameters...
> 'I'm quite stuck here. any ideas?
> dim rs
> set rs = Server.CreateObject("ADODB.Recordset")
> rsValues.Open cmd cmd.Parameters,?,?,?,?
>
> ---
> SoftArtisans helps developers build robust, scalable Web applications!
> Excel Web reports, charts: http://www.softartisans.com/excelwriter.html
> File uploads: http://www.softartisans.com/saf.html
> Transactional file management: http://www.softartisans.com/saf1.html
> Scalability: http://www.softartisans.com/saxsession.html
> ASPstudio value pack: http://www.softartisans.com/aspstudiosuite.html
$subst('Email.Unsub')
Message #3 by "Ken Schaefer" <ken@a...> on Wed, 18 Apr 2001 13:36:56 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: I want to return a recordset as well as output parameters from a stored
: procedure...
:
: I'm not sure how to do this or if this is possible.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Caveat: If you are using SQL Server, and serverside cursors, you need to
close your recordset before you can get your output parameters.
Code for doing all this was supplied by Charles.
Cheers
Ken
Message #4 by "Tomm Matthis" <matthis@b...> on Wed, 18 Apr 2001 09:53:05 -0400
|
|
You're just about there Joel!!!
One caveat... in your production code, remove the
cmd.Parameters.Refresh()... it will slow down the exection of the code
and is only usefull if you need to print out the properties of unknown
parameter values... if you have access to the stored proc itself (thru
Enterprise Manager or Access 2k project files then you don't need the
.refresh() at all.)
Also, note that you do not need to pass parameters on the cmd.Execute
line as you've already specified them in your code by loading up the
individual params.
Also, I've had problems with procs not return recordsets AND output
parameter is SET NOCOUNT OFF is not in the proc.
To get the values after the execution of the proc, simply reverse the
assigment statments:
'The recordset
Set rsValues =3D cmd.Execute
' The return vars
x =3D cmd.Parameters.Item(2).Value
y =3D cmd.Parameters.Item(3).Value
z =3Dcmd.Parameters.Item(4).Value
=09
etc.
Tomm
> -----Original Message-----
> From: Joel Raha [mailto:jraha@g...]
> Sent: Tuesday, April 17, 2001 10:30 PM
> To: ASP Databases
> Subject: [asp_databases] Returning a recordset _and_ ouput parameters
> from a stored procedure
>
>
> I want to return a recordset as well as output parameters from a
stored
> procedure...
>
> I'm not sure how to do this or if this is possible.
[SNIP}
Message #5 by "Joel Raha" <jraha@g...> on Wed, 18 Apr 2001 16:08:03
|
|
I understand the first portion (returning output parameters when calling a
stored procedure.)
The second part is still not clear (returning a recordset and output
parameters when calling a stored procedure). The stored procedure that
returns a recordset, "sp_ret_records" was never called. I don't see where
output parameters were declared or assigned. And the connectionType is
adCmdText not adCmdStoredProc.
If anybody could clarify I would appreciate it.
Thanks in advance.
> > And now we have a stored procedure named "sp_ret_records" that
> > returns a
> > recordset:
> >
> > Dim conConnection As New ADODB.Connection
> > Dim rsRecordset As ADODB.Recordset
> > With conConnection
> > .Open sConString
> > Set rsRecordset = .Execute "SELECT * FROM invoice ORDER BY
cust_id", _
> > , adOpenForwardOnly + adCmdText
> > End With
> >
> > Hope that helps.
> From an earlier post (sorry for the additional post everyone):
>
> > This is a wee bit more complicated than one would first think.
> >
> > If your stored procedure has IN/OUT parameters (that is you are
> > passing
> > ByRef - call 'em pointers) then you will need to create a Parameters
> object
> > and populate it with names, datatypes (there's a listing of constants
> like
> > adVarChar, etc), sizes, and initial values. You then tie the
Parameters
> > object to a command object and finally use a command object to execute
> the
> > stored procedure.
> >
> > However, if your stored procedure returns a recordset, you can set
> a
> > recordset to a connection.Execute's returned recordset.
> >
> > First, make sure there is a reference to ADO 2.1 in your project.
> > sConString is a valid connection string to the Oracle server. Let's
> assume
> > I have a stored proc ("sp_mystery") in an Oracle server that accepts a
> > VARCHAR(25) IN only ('blah_type') and a SMALLINT IN/OUT ('ret_code'):
> >
> > Dim cmdCommand As New ADODB.Command
> > cmdCommand.ActiveConnection = sConString
> > With cmdCommand
> > .CreateParameter "blah_type", adVarChar, adParamInput, 25, "test"
> > .CreateParameter "ret_code", adSmallInt, adParamInputOutput, , 0
> > .CommandText = "sp_mystery"
> > .CommandType = adCmdStoredProc
> > .Execute
> > MsgBox .Parameters("ret_code")
> > End With
> >
> > And now we have a stored procedure named "sp_ret_records" that
> > returns a
> > recordset:
> >
> > Dim conConnection As New ADODB.Connection
> > Dim rsRecordset As ADODB.Recordset
> > With conConnection
> > .Open sConString
> > Set rsRecordset = .Execute "SELECT * FROM invoice ORDER BY
cust_id", _
> > , adOpenForwardOnly + adCmdText
> > End With
> >
> > Hope that helps.
> >
> > - Chuck
>
>
> ----- Original Message -----
> From: "Joel Raha" <jraha@g...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Tuesday, April 17, 2001 10:29 PM
> Subject: [asp_databases] Returning a recordset _and_ ouput parameters
from a
> stored procedure
>
>
> > I want to return a recordset as well as output parameters from a stored
> > procedure...
> >
> > I'm not sure how to do this or if this is possible.
> >
> > Here's what I have.
> >
> >
> > Set con = Server.CreateObject ("ADODB.Connection")
> > con.Open "DSN=" & DBLIB_DATABASE_NAME, DATABASE_PASSWORD, ""
> > Set cmd = Server.CreateObject ("ADODB.Command")
> > cmd.CommandType = adCmdStoredProc '4
> > 'cmd.CommandType = adCmdText '4
> > Set cmd.ActiveConnection = con
> >
> >
> > 'stored procedure returns a recordset and 2 output parameters
> > cmd.CommandText = "usp_PTO_InsertRequestLog"
> > cmd.Parameters.Refresh()
> > 'Then simply apply the initial values...
> > cmd.Parameters.Item(1).Value = 2293
> > cmd.Parameters.Item(2).Value = 44
> > cmd.Parameters.Item(3).Value = "OUTPUT1"
> > cmd.Parameters.Item(4).Value = 0
> >
> > 'To call this without the recordset returned
> > cmd.Execute ,cmd.Parameters
> >
> > 'But to call this and return the output parameters...
> > 'I'm quite stuck here. any ideas?
> > dim rs
> > set rs = Server.CreateObject("ADODB.Recordset")
> > rsValues.Open cmd cmd.Parameters,?,?,?,?
>
Message #6 by "Charles Feduke" <webmaster@r...> on Wed, 18 Apr 2001 11:25:15 -0400
|
|
To return a recordset and parameters, just combine the two examples. First
create and append the parameters and then set a recordset = Command.Execute.
I've read a few times in this list that the returned recordset must first be
closed before you can actually get the returned parameters. This makes no
sense to me, but I've never tried it either.
? Chuck
> -----Original Message-----
> From: Joel Raha [mailto:jraha@g...]
> Sent: Wednesday, April 18, 2001 4:08 PM
> To: ASP Databases
> Subject: [asp_databases] Re: Returning a recordset _and_ ouput
> parameters from a stored procedure
>
>
> I understand the first portion (returning output parameters when
> calling a
> stored procedure.)
> The second part is still not clear (returning a recordset and output
> parameters when calling a stored procedure). The stored procedure that
> returns a recordset, "sp_ret_records" was never called. I don't see where
> output parameters were declared or assigned. And the connectionType is
> adCmdText not adCmdStoredProc.
>
> If anybody could clarify I would appreciate it.
>
> Thanks in advance.
>
> > > And now we have a stored procedure named "sp_ret_records" that
> > > returns a
> > > recordset:
> > >
> > > Dim conConnection As New ADODB.Connection
> > > Dim rsRecordset As ADODB.Recordset
> > > With conConnection
> > > .Open sConString
> > > Set rsRecordset = .Execute "SELECT * FROM invoice ORDER BY
> cust_id", _
> > > , adOpenForwardOnly + adCmdText
> > > End With
> > >
> > > Hope that helps.
>
>
>
> > From an earlier post (sorry for the additional post everyone):
> >
> > > This is a wee bit more complicated than one would first think.
> > >
> > > If your stored procedure has IN/OUT parameters (that is you are
> > > passing
> > > ByRef - call 'em pointers) then you will need to create a Parameters
> > object
> > > and populate it with names, datatypes (there's a listing of constants
> > like
> > > adVarChar, etc), sizes, and initial values. You then tie the
> Parameters
> > > object to a command object and finally use a command object to execute
> > the
> > > stored procedure.
> > >
> > > However, if your stored procedure returns a recordset, you can set
> > a
> > > recordset to a connection.Execute's returned recordset.
> > >
> > > First, make sure there is a reference to ADO 2.1 in your project.
> > > sConString is a valid connection string to the Oracle server. Let's
> > assume
> > > I have a stored proc ("sp_mystery") in an Oracle server that accepts a
> > > VARCHAR(25) IN only ('blah_type') and a SMALLINT IN/OUT ('ret_code'):
> > >
> > > Dim cmdCommand As New ADODB.Command
> > > cmdCommand.ActiveConnection = sConString
> > > With cmdCommand
> > > .CreateParameter "blah_type", adVarChar, adParamInput, 25, "test"
> > > .CreateParameter "ret_code", adSmallInt, adParamInputOutput, , 0
> > > .CommandText = "sp_mystery"
> > > .CommandType = adCmdStoredProc
> > > .Execute
> > > MsgBox .Parameters("ret_code")
> > > End With
> > >
> > > And now we have a stored procedure named "sp_ret_records" that
> > > returns a
> > > recordset:
> > >
> > > Dim conConnection As New ADODB.Connection
> > > Dim rsRecordset As ADODB.Recordset
> > > With conConnection
> > > .Open sConString
> > > Set rsRecordset = .Execute "SELECT * FROM invoice ORDER BY
> cust_id", _
> > > , adOpenForwardOnly + adCmdText
> > > End With
> > >
> > > Hope that helps.
> > >
> > > - Chuck
> >
> >
> > ----- Original Message -----
> > From: "Joel Raha" <jraha@g...>
> > To: "ASP Databases" <asp_databases@p...>
> > Sent: Tuesday, April 17, 2001 10:29 PM
> > Subject: [asp_databases] Returning a recordset _and_ ouput parameters
> from a
> > stored procedure
> >
> >
> > > I want to return a recordset as well as output parameters
> from a stored
> > > procedure...
> > >
> > > I'm not sure how to do this or if this is possible.
> > >
> > > Here's what I have.
> > >
> > >
> > > Set con = Server.CreateObject ("ADODB.Connection")
> > > con.Open "DSN=" & DBLIB_DATABASE_NAME, DATABASE_PASSWORD, ""
> > > Set cmd = Server.CreateObject ("ADODB.Command")
> > > cmd.CommandType = adCmdStoredProc '4
> > > 'cmd.CommandType = adCmdText '4
> > > Set cmd.ActiveConnection = con
> > >
> > >
> > > 'stored procedure returns a recordset and 2 output parameters
> > > cmd.CommandText = "usp_PTO_InsertRequestLog"
> > > cmd.Parameters.Refresh()
> > > 'Then simply apply the initial values...
> > > cmd.Parameters.Item(1).Value = 2293
> > > cmd.Parameters.Item(2).Value = 44
> > > cmd.Parameters.Item(3).Value = "OUTPUT1"
> > > cmd.Parameters.Item(4).Value = 0
> > >
> > > 'To call this without the recordset returned
> > > cmd.Execute ,cmd.Parameters
> > >
> > > 'But to call this and return the output parameters...
> > > 'I'm quite stuck here. any ideas?
> > > dim rs
> > > set rs = Server.CreateObject("ADODB.Recordset")
> > > rsValues.Open cmd cmd.Parameters,?,?,?,?
> >
>
|
|
|
View Cart
