VB6 - How to Capture result set from Stored Procedure
-
Hi
I have a stored procedure (usp_Test) that accepts one parameter (@UserID) and returns a one column, one row result set. The proc executes successfully in QA, with the expected result displaying.
I have been unable to figure out how to call the proc from VB6. I have been able to either get a result set out, or pass parameters in - not both at the same time.
My VB Code:
Dim ACommand As New ADODB.Command
Dim Aprm As New ADODB.Parameter
Dim objRecordset As ADODB.Recordset
Set ACommand = New ADODB.Command
ACommand.ActiveConnection = WScnn
ACommand.CommandType = adCmdStoredProc
ACommand.CommandText = "usp_Test"
Set Aprm = ACommand.CreateParameter("@UserInitials", adVarChar, adParamInput, 10, UserInitials)
ACommand.Parameters.Append Aprm
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseServer 'i've tried adUseClient here too
objRecordset.CursorType = adOpenForwardOnly
objRecordset.Open ACommand ' Open the Recordset
Debug.Print objRecordset.RecordCount '<--- gets an error here; The error says "This operation is not allowed when the object is closed." i also cannot reference .fields(0) etc. Do I need to define an output param inside the proc? If so, what should it look like?
I know that the proc runs, as it performs an insert, and I can see the new row as expected.
I'm tearing my hair out, and I'm running out fast...
Thanks
Antony
-
Here's some sample code... I can make a new simpler one if you don't understand this one :
sql code :
CREATE PROCEDURE [dbo].[SearchDependencies] @Search as varchar(256), @PkDB as int, @SaveSearch as bit = 0, @FkSearch_Results as int = null, @FkSearch as int = null output
AS
SET NOCOUNT ON
Declare @Results table (PkDependanceADP_SQL int primary key, ADPName varchar(100) not null, ObjName varchar(100) not null,
DescTypeObj varchar(50) not null, ParentName varchar(128) not null, ParentXTypeName varchar(50) not null, ParentXType varchar(3) not null)
set @FkSearch = (Select PkRechercheCode from dbo.RecherchesCode where Recherche = @Search and FkDB = @PkDB)
--checks if the data has been updated since the search was performed and returns 1 if the search is still valid
set @FkSearch = nullif(dbo.fnSearchIsStillValid (@FkSearch), 0)
set @FkSearch_Results = nullif(dbo.fnSearchIsStillValid (@FkSearch_Results), 0)
--this sets @FkSearch to null if both searchs are the same, which simply redisplays the recorded results since no further filtering will be performed anyways.
set @FkSearch = nullif(@FkSearch, @FkSearch_Results)
if @FkSearch is null and @FkSearch_Results is null
begin
--the search is not recorded and this is not a sub search
if @SaveSearch = 1
begin
Insert into @Results (PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType)
Select PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType
from dbo.fnSearchDependencies_NewSearch (@Search, @PkDB)
end
else
begin
Select PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType
from dbo.fnSearchDependencies_NewSearch (@Search, @PkDB)
end
end
else if @FkSearch is not null and @FkSearch_Results is null
begin
--using the recorded results to send the results without refiltering the previous results
if @SaveSearch = 1
begin
Insert into @Results (PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType)
Select PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType
from dbo.fnSearchDependencies_ResendResults(@FkSearch_Results)
end
else
begin
Select PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType
from dbo.fnSearchDependencies_ResendResults(@FkSearch_Results)
end
end
else if @FkSearch is null and @FkSearch_Results is not null
begin
--the search is not recorded, filtering the results of another search with a new search
if @SaveSearch = 1
begin
Insert into @Results (PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType)
Select PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType
from dbo.fnSearchDependencies_FilterOldSearch(@Search, @PkDB, @FkSearch_Results)
end
else
begin
Select PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType
from dbo.fnSearchDependencies_FilterOldSearch(@Search, @PkDB, @FkSearch_Results)
end
end
else if @FkSearch is not null and @FkSearch_Results is not null
begin
--filtering the results of a search with the results of another search
if @SaveSearch = 1
begin
Insert into @Results (PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType)
Select PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType
from dbo.fnSearchDependencies_MergeSearchs (@FkSearch, @FkSearch_Results)
end
else
begin
Select PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType
from dbo.fnSearchDependencies_MergeSearchs (@FkSearch, @FkSearch_Results)
end
end
if @SaveSearch = 1
begin
--save the search
if @FkSearch is null
begin
--create a new search and retrieve the id
exec @FkSearch = dbo.AjouterRechercheCode @PkDB, @Search
end
--save the results in the RRCDependances Table
Insert into dbo.RRCDependances (FkRechercheCode, FkDependanceADP_SQL) (Select @FkSearch as FkRechercheCode, PkDependanceADP_SQL from @Results)
--show the results
--this must be reordered because the primary key changes the order from the view because the PK is PkDependanceADP_SQL instead of the 3 varchar fields.
Select PkDependanceADP_SQL, ADPName, ObjName, DescTypeObj, ParentName, ParentXTypeName, ParentXType from @Results
Order by ADPName, DescTypeObj, ObjName
end
SET NOCOUNT OFF
GO
vb code :
Private Function exec_SearchDependencies(ByVal Search As String, ByVal PkDB As Integer, ByVal SaveSearch As Boolean, ByVal FkSearch_Results As Integer, ByRef FkSearch As Integer, Optional ByRef ReturnValue As Integer) As ADODB.Recordset
On Error GoTo Gestion
Dim MyCmd As ADODB.Command
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "dbo.SearchDependencies"
MyCmd.CommandType = adCmdStoredProc
Dim MyParam As ADODB.Parameter
Set MyParam = New ADODB.Parameter
MyParam.Direction = adParamReturnValue
MyParam.Name = "@Return"
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@Search"
MyParam.Value = Search
MyParam.Size = 256
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@PkDB"
MyParam.Value = PkDB
MyParam.Size = 4
MyParam.Direction = adParamInput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@SaveSearch"
MyParam.Value = SaveSearch
MyParam.Size = 1
MyParam.Direction = adParamInput
MyParam.Type = adBoolean
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@FkSearch_Results"
MyParam.Value = FkSearch_Results
MyParam.Size = 4
MyParam.Direction = adParamInput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@FkSearch"
MyParam.Value = FkSearch
MyParam.Size = 4
MyParam.Direction = adParamInputOutput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Dim MyRs As ADODB.Recordset
Set MyRs = New ADODB.Recordset
MyRs.CursorLocation = adUseClient
MyCn.Open
MyCmd.ActiveConnection = MyCn
MyRs.Open MyCmd, , adOpenKeyset, adLockOptimistic
If MyRs.State = 1 Then
Set exec_SearchDependencies = MyRs.Clone
exec_SearchDependencies.ActiveConnection = Nothing
Else
Set exec_SearchDependencies = Nothing
End If
MyCn.Close
ReturnValue = CInt(MyCmd.Parameters("@Return").Value)
FkSearch = MyCmd.Parameters("@FkSearch").Value
DisposeRS MyRs
Set MyParam = Nothing
Set MyCmd = Nothing
Exit Function
Gestion:
ErrHandler ModuleName, Me.Name, "exec_SearchDependencies", Err
MsgBox Err.Description & " : " & Err.Number
End Function
-
Thanks heaps.
That's exactly what I've been looking for.
-
HTH.
-
Maybe I'm missing something here, but shouldn't it be a lot easier than this? If the SP truly has one parameter and returns a single row with a single column, couldn't you just do:
Dim rs as New ADODB.Recordset
Dim sql as String
sql = "EXEC usp_Test @user-id=" & valueForParam
rs.Open sql, WScnn, adOpenForwardOnly, adLockReadOnly
valOfParam = rs("SingleFieldName")
rs.Close
Of course you could add some code for checking rs.EOF in case the open doesn't yield a recordset (if the record matching the provided UserID isn't found) but that should add just a couple extra lines at the most. Seemed to me that the other solutions are overcomplicated, but again I could be missing something...
-
The point you're missing is that I have a code generator
.Anyways your version uses dynamic sql which is subject to injection attacks. The command objects cannot fall for such attacks and is also faster to run.
-
Whoa, bad idea. You're opening yourself up for a SQL injection attact with that code.
-
OK, how about this...
Dim cmd as New ADODB.Command
Dim rs as ADODB.Recordset
With cmd
.ActiveConnection = WScnn
.CommandType = adCmdStoredProc
.CommandText = "usp_Test"
.Parameters("@UserID") = valueForParam
Set rs = .Execute
End With
valOfParam = rs("SingleFieldName")
FWIW, I'm not sure my "dynamic" SQL is subject to injection attacks since I'm calling a SP. As long as the SP doesn't in turn use dynamic SQL, everything should be OK. The dynamic SQL of calling SELECTs, UPDATEs, etc from inside your code is what's most vulnerable as far as I know...
-
Think about it, you're concatenating text >>
me.txtbox = '5; DROP TABLE X--'
MyVar = "Exec dbo.MySP " & me.txtbox
And boom, you're screwed (maybe that one doesn't work but I've seen it done).
-
WRT your original example, what if I set the value of valueForParam to the following?
NULL; DROP PROCEDURE usp_Test;
?
Naturally, that assumes I have priviliges to do a DROP PROCEDURE, but it illustrates the problem, I think. Problem is not the stored proc, it's the fact that you're building a dynamic SQL statement to execute it.
-
OK, I'm goign to stop double-teaming now.
-
No that's fine... the points get across better that way
. -
OK, I definitely follow. I guess I just got wrapped around the fact that my apps rarely use user-entered data for queries like this. When I do INSERTs, UPDATEs, DELETEs, etc that take in a lot of user-entered data, I always use Command objects. My "quick and dirty" recordset reads like I listed typically get their parameters from internal controls such as a button clicks, other RS's, etc. I just scanned a bunch of my apps and tried the injection attack like you listed and nothing happened. But I definitely see your point. Oh well, the way ADO.NET and VB.NET work pretty much takes care of this. My "quick and dirty" method is OBE... Thanks for the reminder to always be careful with designing your code...
-
HTH. Don't forget that it's also faster to call RPC than sql batch (for a single operation).
-
I use ADO in VBA quite a lot to execute and return OUTPUT values. From your original post it appears you need to execute the adodb.command. You DO NOT need a recordset, merely assign the parameter value to a VB variable.
Dim strParamReturnValue As String
ACommand.Parameters.Append Aprm
ACommand.Execute
strParamReturnValue = APrm.Value
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply