Server 2K and CFMX 6.1. I have a stored procedure that handles database
inserts. It looks something like this (trimmed):
INSERT INTO quiz_results (user_id, question_id, answer_text)
VALUES (@user_id, @question_id, @answer_text)
IF @@ERROR <> 0
BEGIN
PRINT 'ERROR: Duplicate entry.'
RETURN -2
END
ELSE
BEGIN
PRINT 'Success.'
RETURN 0
END
Then, I'm calling it via the following CFML in a UDF (the fact that this is
in a UDF shouldn't matter, just thought I'd mention it):
<cfstoredproc procedure="quiz_results_insert" datasource="Quiz"
returncode="yes">
<cfprocparam variable="user_id" value="#arguments.user_id#"
cfsqltype="cf_sql_integer">
<cfprocparam variable="question_id" value="#arguments.question_id#"
cfsqltype="cf_sql_integer">
<cfprocparam variable="answer_text" value="#arguments.answer_text#"
cfsqltype="cf_sql_varchar">
</cfstoredproc>
.. and I'd like the function to return success or failure. Easy enough,
right?
<cfreturn cfstoredproc.StatusCode>
However, if the insert fails, CFMX chokes on the error message. I wanted to
trap the error in my stored procedure, and return '-2' to CFMX, so that
cfstoredproc.StatusCode is -2. I know I can fix this by just wrapping the
storedproc call in a try block, and setting cfstoredproc.StatusCode
manually, but I want all of the error handling to be done in the stored
procedure. The UDF should just return the StatusCode that was returned by
the stored proc on the database.
It doesn't look like this is possible, from everything I've seen there are
some errors in SQL Server that you just can't trap. If I run the stored
proc in Query Analyzer and the insert fails, I see the insert failure error
message, but I also see the 'ERROR' text so I know that the statement is
executing alright. It's just that as soon as CFMX gets the error message,
it terminates the statement, and cfstoredproc.StatusCode isn't set.
Any ideas, other than wrapping it in a try/catch block and manually setting
the error code?
-d
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
