Trying to return a return code and a recordset from a stored proc
-
I have a stored proc that is using OLE calls to use VB Reg Expr to run some pattern matching on a table of sentences.
The proc has 3 steps
1 create an instance of the VB Reg Expr Component
2 if successful run a select statement to return a recordset of all recs matching that expression by calling a udf passing into it the reference to the component
3 destroy the VB Reg Expr component
If either step 1 or 3 fails then I would like to be able to return to my calling ASP code a return code notifying it of the failure with a custom err message.
I would also like to be able to look at this return code first before deciding whether to display the rst if there are records.
Is there anyway I could retrieve a return value/output parameter first before a recordset?
My bodgy code so far which Im not happy with is this...
CREATE PROCEDURE [usp_asp_cms_find_pattern]
@SitePK int,
@TextType char(1),
@FindText varchar(50),
@SearchType char(1)
AS
declare@RegExp varchar(100),
@hr integer,
@objRegExp integer,
@results bit
begin
--create VB object
exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp output
if @hr <> 0
begin
set @results = 0
GOTO HANDLE_ERROR
end
SELECT 1 as success, '' as message
if @SearchType = 'e'--email
begin
set @regexp = '[a-z0-9_\.\-\'']+@[a-z0-9\.\-]+\.[a-z]{2,4}' --email reg exp
end
else --'p' phone
begin
set @regexp = '[0-9]+[0-9-\s]{4,}[0-9]+'--phone number reg exp
end
if @TextType = 'T'
begin
SELECT d.SentanceID as ID, d.Sentance as cDefault, c.Sentance as cCustom
FROMtbl_DEFAULT_SENTENCES as d
LEFT JOIN tbl_CUSTOM_SENTENCES as c
ONd.PageFK = c.PageFK AND
d.SentanceID = c.SentanceID AND
c.SiteFK = @SitePk
WHERE dbo.udf_REG_EXP(d.Sentance,@regexp,1)=1 OR
dbo.udf_REG_EXP(c.Sentance,@regexp,1)=1
ORDER BY d.SentanceID;
end
else
begin
SELECT d.MessageID as ID, d.Message as cDefault, c.Message as cCustom
FROM tbl_DEFAULT_MESSAGES as d
LEFT JOIN tbl_CUSTOM_MESSAGES as c
ON d.PageFK = c.PageFK AND
d.MessageID = c.MessageID AND
c.SiteFK = @SitePk
WHERE dbo.udf_REG_EXP(d.Message,@regexp,1)=1 OR
dbo.udf_REG_EXP(c.Message,@regexp,1)=1
ORDER BY d.MessageID;
end
--destroy VB Object
set @objRegExp = @objRegExp
exec @hr = sp_OADestroy @objRegExp
if @hr <> 0
begin
set @results = 0
GOTO HANDLE_ERROR
end
end
return
HANDLE_ERROR:
SELECT 0 as success, dbo.udf_ERROR_MESSAGE(@SitePK, 120,1) as message
GO
At the moment my code is fine unless it fails on the OADestroy part after I have run the select statement. If that happens I return 3 recordsets.
1st one lets me know that the sp_OACreate call worked as success=1
2nd one contains a results recordset which may or not be empty
3rd one contains an error message letting me know that one of the OA calls has failed.
However I want to know if any of the OA calls fails before I display the recordset and the only way I know in VBscript ADO of doing that is using GetRows to hold my results whilst I check other recordsets. Or call the proc twice once with just objCom.Execute so I could access the Return Code parameter and then again set objRS = objCom.Execute to get the recordsets.
Does anyone have any better ways of doing it?
-
Return the resultset last. Why does the sp succeed even though one of its OA calls fail?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
The sp succeeds if only the OA destroy call fails as the select statement returns records but it cant destroy the OA object.
So if you mangle the sp to break the destroy call manually by doing this
--destroy VB Object
set @objRegExp = @objRegExp + 11 --break code
--set @objRegExp = @objRegExp
exec @hr = sp_OADestroy @objRegExp
if @hr 0
begin
set @results = 0
GOTO HANDLE_ERROR
end
this is what you get if you run in QA
declare @SitePK int,
@TextType char(1),
@FindText varchar(50),
@SearchType char(1)
set @SitePk = 18
set @TextType = 'T'
set @FindText = ''
set @SearchType = 'e'
exec usp_asp_cms_find_pattern @SitePK, @TextType, @FindText, @SearchType
successmessage
1
(1 row(s) affected)
IDcDefaultcCustom
8If you have not yet registered with us, please go to the registration page.If you have not yet registered with us, please email info@xxxxx.org or call 0207 XXX XXXX
(1 row(s) affected)
successmessage
0120: System Error! There was an error with the sp_OACreate object. Contact your system administrator.
(1 row(s) affected)
So I get a recordset of results that match the reg exp looking for email addresses in the second recordset but my error message is in the 3rd recordset which I don't want.
I would like to be able to get a success/fail result value that I can look at first and then decide whether to look at the recordset. With the code I have used I cannot do this as it falls over if the OA Destroy call breaks down.
-
What are you using for your front end? I don't see why you cant read all of the tables(recordsets), THEN display what you want.
-
Im using ASP. Thats what I said in my message the only way I know of doing it is to put my recordsets into an array then I can move through them get the success value then get the data out of the array.
I would like to know if theres a different way of doing it though as I dont want to have to change the code that displays the recordset to look at the array rather than the current code as its used for several different procs.
-
Hmm. Perhaps an output parameter that tells you if there was an error at the end?
-
But then I still would have to use a NextRecordset method to get to it as the actual results recordset would always come before the output parameter.
If I use ADO command object and assign the recs to the execute "set objRS = objCom.execute" I would have to move to next recordset to check output parameter then I would loose my results.
If I just did objCom.execute I could check the output parameter eg Result = objCom.parameters("result").value first but then i would have to call the command execute again to get the results.
Pain in the ass as I thought if you used Return values there was a way of accessing them before any result set returned but it seems not.
-
Hmmm. Didn't know it worked that way with old ASP and ADO. I am spoiled with .NET and my collections. Don't really know what to say.
One possiblility: Put all of your results into temp tables on SQL, and check them on the server. If your failure is there, decide what to send to the client. Otherwise, simply send it all.
Don't know if that makes sense or not....
-
in SQL8+, you could put the results in a varaible and wait until everything else is done before returning it. You could supress the rst at the source, rather than sending it to ASP to suppress. Also, don't bother returning a recordset to record success.
That way, the first(or only?) recordset you get will either be the results of the error or the results of the query. Not sure if that is what you're after...
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
- But I don't see why you can't use a return code or output param.
- Doesn't something like this work?
- set prm1 = objCom.parameters.add(createparameter etc. etc., adparamoutput, blah)
- set objRS = objCom.execute
- Result = prm1.value
- If Result then
- ''''''''display rst
- else
- ''''''''don't
- end if 'but why not do it in the sp?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
That was first thing I tried this morning.
My test page ASP code is this:
dim objCom
dim intSitePK: intSitePK= 18
dim strFindText : strFindText= "''"
dim strSearchType : strSearchType = "e"
dim strTextType: strTextType= "T"
Connect()
set objCom = Server.CreateObject("ADODB.Command")
objCom.ActiveConnection = objData.getConnection()
objCom.CommandType = adCmdStoredProc
Response.Write("EXEC @rc = usp_asp_cms_find_pattern " & intSitePK & ", " & strTextType & "," & strFindText & ", " & strSearchType & "
")objCom.CommandText = "dbo.usp_asp_cms_find_pattern"
objCom.parameters.append objCom.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
objCom.parameters.append objCom.CreateParameter("@SitePK",adInteger,adParamInput,0,intSitePK)
objCom.parameters.append objCom.CreateParameter("@TextType",adChar,adParamInput,1,strTextType)
objCom.parameters.append objCom.CreateParameter("@FindText",adVarchar,adParamInput,50,strFindText)
objCom.parameters.append objCom.CreateParameter("@SearchType",adChar,adParamInput,1,strSearchType)
set objRS = objCom.Execute
'objCom.Execute
intRetVal = objCom.Parameters("RETURN_VALUE")
if cint(intRetVal) = 1 then
do while not objRS.EOF
Response.Write(objRS("ID") & " - " & objRS("cDefault") & "
")objRS.movenext
loop
objRS.close
else
Response.Write("IT ERRORED - Return Value was 0 No Recs")
end if
DisConnect()
which returns on the ASP page when run
EXEC @rc = usp_asp_cms_find_pattern 18, T,'', e
IT ERRORED - Return Value was 0 No Recs
and if you take that debug statement and run it directly on SQL QA like
declare @rc int
EXEC @rc = usp_asp_cms_find_pattern 18, 'T','', 'e'
if @rc = 1
print 'Return Val 1 Success'
else
print 'Return Val 0 Fail'
you get
successmessage
1
(1 row(s) affected)
IDcDefaultcCustom
8If you have not yet registered with us, please go to the registration page.If you have not yet registered with us, please email info@xxxxxxx.org or call 0207 xxx xxxx
(1 row(s) affected)
Return Val 1 Success
so u see you cant do what you said and I really wish you could but it just won't work like that. If you set a recordset to the objCom.execute you have to move through the recordsets to get the return value.
I changed my ASP to this
set objRS = objCom.Execute
set objRS = objRS.nextRecordset() 'to skip first select 1 as success
'loop through results
do while not objRS.EOF
Response.Write(objRS("ID") & " - " & objRS("cDefault") & "
")objRS.movenext
loop
objRS.close
'have to close it first b4 getting return value
intRetVal = objCom.Parameters("RETURN_VALUE")
Response.Write("Return Value is " & intRetVal & "
")set objRS = nothing
which produces
EXEC usp_asp_cms_find_pattern 18, T,'', e
8 - If you have not yet registered with us, please go to the registration page.
Return Value is 1
so you see its a bugger! You cant access return codes b4 recordsets
-
Have you changed the stored proc to actually return a value? (If so, is it set up to be responsive to all errors?) - ah reading your last post again, it appears you have - sorry! But see my previous post.
1. Why not suppress the recordset at the server if an error occurs? Do you have complex logic?
2. Why not return an error rst before (or instead of, see 1) the final select * from @retval
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply