Database Programming, Stored Proc Return values / Output Params w ADO and SQL Server 7

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

Author	Message
Angelo Pacion #1 / 8	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

Angelo Pacion #2 / 8	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

Mike #3 / 8	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

nne.. #4 / 8	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

Angelo Pacion #5 / 8	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

Angelo Pacion #6 / 8	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

Aleksey Savateye #7 / 8	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

Mark McDowel #8 / 8	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

Page 1 of 1

[ 8 post ]