Stored Proc Return values / Output Params w ADO and SQL Server 7 
Author Message
 Stored Proc Return values / Output Params w ADO and SQL Server 7

Does anyone know if it is possible to retrieve RETURN values or OUTPUT
parameters from a stored procedure (that also returns a recordset) via
ADO WITHOUT having to close the recordset returned from a command's
execute method first?  Microsoft's workaround example for this problem
can be seen at:
http://www.***.com/

It just seems kind of funny to NOT be able to check an output parameter
or RETURN value first to then be able to deal with a recordset in a
certain manner.  Any help on this would be greatly appreciated.

--
________________________

Angelo Pacione

Sent via Deja.com http://www.***.com/
Before you buy.



Tue, 23 Apr 2002 03:00:00 GMT
 Stored Proc Return values / Output Params w ADO and SQL Server 7

Does anyone know if it is possible to retrieve RETURN values or OUTPUT
parameters from a stored procedure (that also returns a recordset) via
ADO WITHOUT having to close the recordset returned from a command's
execute method first?  Microsoft's workaround example for this problem
can be seen at:
http://msdn.microsoft.com/library/psdk/sql/adoprg01_20.htm

It just seems kind of funny to NOT be able to check an output parameter
or RETURN value first to then be able to deal with a recordset in a
certain manner.  Any help on this would be greatly appreciated.

--
________________________

Angelo Pacione

Sent via Deja.com http://www.deja.com/
Before you buy.



Tue, 23 Apr 2002 03:00:00 GMT
 Stored Proc Return values / Output Params w ADO and SQL Server 7
Try this:
            Dim cmdLead As New ADODB.Command
            Dim prmLead As New ADODB.Parameter
            'setup the command object
            Set cmdLead.ActiveConnection = mAdoCn
            cmdLead.CommandText = "sp_lead"        'This is your stored proc
            cmdLead.CommandType = adCmdStoredProc

            Set prmLead = New ADODB.Parameter
            prmLead.Type = adInteger
            prmLead.Direction = adParamOutput
            prmLead.Value = ID
            cmdLead.Parameters.Append prmLead
            cmdLead.Execute
            ID = cmdLead(1)
            Set cmdLead = Nothing

I hope this helps

Mike


Quote:
> Does anyone know if it is possible to retrieve RETURN values or OUTPUT
> parameters from a stored procedure (that also returns a recordset) via
> ADO WITHOUT having to close the recordset returned from a command's
> execute method first?  Microsoft's workaround example for this problem
> can be seen at:
> http://msdn.microsoft.com/library/psdk/sql/adoprg01_20.htm

> It just seems kind of funny to NOT be able to check an output parameter
> or RETURN value first to then be able to deal with a recordset in a
> certain manner.  Any help on this would be greatly appreciated.

> --
> ________________________

> Angelo Pacione

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Tue, 23 Apr 2002 03:00:00 GMT
 Stored Proc Return values / Output Params w ADO and SQL Server 7
See whether the provider has enabled the feature.

iParams = objConn.Properties("Output Parameter Availability")

the ADO Constants are
DBPROPVAL_OA_ATEXECUTE(2): Available immediately after Execute
DBPROPVAL_OA_ATROWRELEASE(4): Available after releasing the rowset
DBPROPVAL_OA_NOTSUPPORTED(1): Not Supported

Access is 1 as it does not support output parameters at all.
Sybase is 4, i would have been surprised if it was 2.
I thought SQL Server would be a 2.

check it out.

hth

Sent via Deja.com http://www.deja.com/
Before you buy.



Wed, 24 Apr 2002 03:00:00 GMT
 Stored Proc Return values / Output Params w ADO and SQL Server 7
Thanks for your response.  Here is what I discovered so far.

Although I am only a lowly VB progrmammer, your C++ code did lead me to
find the following articles from MDSN:

- How to process return codes and output parameters (OLE DB)
http://msdn.microsoft.com/library/psdk/sql/olehowt1_2.htm
(Could someone who is a little more proficient in C++ than me try this
cut/paste example?  I'll try it too.)

- How to process return codes and output parameters (ODBC)
http://msdn.microsoft.com/library/psdk/sql/odbcht10_2.htm

The articles above basically state that you cannot have access to the
output parameters (or return values I assume) until the rowset returned
with them has been released first.  It seems this is the case because
the rowset comes first in the packet, and then the parameters after
once the rowset is released.  Since ADO and OLEDB are inherently
linked, I believe that is why Microsoft suggested the workaround as
descibed in the article I referred to in my original post
(http://msdn.microsoft.com/library/psdk/sql/adoprg01_20.htm).

So in conclusion, it looks like there is no current way to access the
output parameters before closing a returned recordset.  This seems
really weird to me though, and I am really suprised that no one else
has talked about this at all.  Does it not seem like a logical process
to have to check a return value to know the status of the rowset (or
lack of one) being returned?  Just my humble opinion.

Any further input on this topic would be greatly appreciated.  Thanks.

________________________

Angelo Pacione


Quote:

> See whether the provider has enabled the feature.

> iParams = objConn.Properties("Output Parameter Availability")

> the ADO Constants are
> DBPROPVAL_OA_ATEXECUTE(2): Available immediately after Execute
> DBPROPVAL_OA_ATROWRELEASE(4): Available after releasing the rowset
> DBPROPVAL_OA_NOTSUPPORTED(1): Not Supported

> Access is 1 as it does not support output parameters at all.
> Sybase is 4, i would have been surprised if it was 2.
> I thought SQL Server would be a 2.

> check it out.

> hth

> Sent via Deja.com http://www.deja.com/
> Before you buy.

--

Sent via Deja.com http://www.deja.com/
Before you buy.



Fri, 26 Apr 2002 03:00:00 GMT
 Stored Proc Return values / Output Params w ADO and SQL Server 7
Thanks for your response.  Here is what I discovered so far.

Although I am only a lowly VB progrmammer, your C++ code did lead me to
find the following articles from MDSN:

- How to process return codes and output parameters (OLE DB)
http://msdn.microsoft.com/library/psdk/sql/olehowt1_2.htm
(Could someone who is a little more proficient in C++ than me try this
cut/paste example?  I'll try it too.)

- How to process return codes and output parameters (ODBC)
http://msdn.microsoft.com/library/psdk/sql/odbcht10_2.htm

The articles above basically state that you cannot have access to the
output parameters (or return values I assume) until the rowset returned
with them has been released first.  It seems this is the case because
the rowset comes first in the packet, and then the parameters after
once the rowset is released.  Since ADO and OLEDB are inherently
linked, I believe that is why Microsoft suggested the workaround as
descibed in the article I referred to in my original post
(http://msdn.microsoft.com/library/psdk/sql/adoprg01_20.htm).

So in conclusion, it looks like there is no current way to access the
output parameters before closing a returned recordset.  This seems
really weird to me though, and I am really suprised that no one else
has talked about this at all.  Does it not seem like a logical process
to have to check a return value to know the status of the rowset (or
lack of one) being returned?  Just my humble opinion.

Any further input on this topic would be greatly appreciated.  Thanks.

________________________

Angelo Pacione


Quote:

> See whether the provider has enabled the feature.

> iParams = objConn.Properties("Output Parameter Availability")

> the ADO Constants are
> DBPROPVAL_OA_ATEXECUTE(2): Available immediately after Execute
> DBPROPVAL_OA_ATROWRELEASE(4): Available after releasing the rowset
> DBPROPVAL_OA_NOTSUPPORTED(1): Not Supported

> Access is 1 as it does not support output parameters at all.
> Sybase is 4, i would have been surprised if it was 2.
> I thought SQL Server would be a 2.

> check it out.

> hth

> Sent via Deja.com http://www.deja.com/
> Before you buy.

--

Sent via Deja.com http://www.deja.com/
Before you buy.



Fri, 26 Apr 2002 03:00:00 GMT
 Stored Proc Return values / Output Params w ADO and SQL Server 7
Yes, this one for SQL Server 7.0 is 4. Even using ODBC you cannot get return
value before releasing recordset. ADO is wrapper around ODBC, so it cannot
do this either.

--
Aleksey Savateyev

Quote:

> See whether the provider has enabled the feature.

> iParams = objConn.Properties("Output Parameter Availability")

> the ADO Constants are
> DBPROPVAL_OA_ATEXECUTE(2): Available immediately after Execute
> DBPROPVAL_OA_ATROWRELEASE(4): Available after releasing the rowset
> DBPROPVAL_OA_NOTSUPPORTED(1): Not Supported

> Access is 1 as it does not support output parameters at all.
> Sybase is 4, i would have been surprised if it was 2.
> I thought SQL Server would be a 2.

> check it out.

> hth

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Fri, 26 Apr 2002 03:00:00 GMT
 Stored Proc Return values / Output Params w ADO and SQL Server 7


Quote:
> Yes, this one for SQL Server 7.0 is 4. Even using ODBC you cannot get return
> value before releasing recordset. ADO is wrapper around ODBC, so it cannot
> do this either.

Are you certain?  It was my understanding that ADO "CAN" wrap, but in
general it uses native OLE DB drivers, only using ODBC for those formats
that don't have an OLE DB driver.

Mark



Sun, 28 Apr 2002 03:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Stored Proc Return values / Output Params w ADO and SQL Server 7

2. Stored Proc Return values / Output Params w ADO and SQL Server 7

3. Stored Proc Return values / Output Params w ADO and SQL Server 7

4. Stored Proc Return values / Output Params w ADO and SQL Server 7

5. Stored Proc Return values / Output Params w ADO and SQL Server 7

6. Returning output params from a store proc that also return a recordset

7. sql server cursors as stored proc output params?

8. What's different between OUTPUT params and Return value in Store Procedures

9. SQL Server 7 stored procedure output params return nulls

10. Executing stored proc with Output params without ADO Command object

11. vc ado extensions vs stored proc w output params

12. Stored proc not returning values with output parameters


 
Powered by phpBB® Forum Software