Board index » delphi » Error Handling in Stored Procedures

2007-03-17 07:50:56 AM
delphi187
Does anyone know how to trap exceptions in a stored procedure? I want
to process a bunch of records and not stop because one record fails.
Most of the documentation talks about processing by SqlCode. I'm
trying to handle my own exceptions.
In particular, how can I get the message that would otherwise be
displayed. I want to put it in a variable so I can store it someplace.
Also, is it necessary to code for each exception that I want to trap?
That's how I read the documentation, such that it is. While I may know
the exceptions most directly related to the process I am working on, for
a complex system, a lot of unexpected exceptions could pop up. And
having to fix up this code down the road when somebody creates another
exception would be impossible.
--
Patrick Moloney
 

See the WHEN...DO statment in the Language Reference then post again if
you still have questions.
--
Bill Todd (TeamB)
 

Bill Todd writes:
1. Apparently I have to list every exception I would like to trap. I'd
rather not have to make that list (and maintain it). By default IB
stops and displays any exception to the user. I just want to record any
exception and continue with the next record.
2. I see no documentation on how to capture the exception message. The
only thing I can devise is to list every exception in my When
statement, (meaning I provide the exception name), then retrieve the
message from the system tables using that name.
If, as I suspect, this is the best there is, then we have an area for
IB enhancement here.
--
Patrick Moloney
 

Patrick Moloney writes:
WHEN ANY DO
INSERT INTO LOGTBL (ERRORCODE) VALUES (GDSCODE);
You are correct that there is no way using SQL to get the message text
that corresponds to an error code. The error messages are not stored in
a system table.
--
Bill Todd (TeamB)
 

Bill Todd writes:
I have created and which are activated during user processing. I am not
trying to handle "Harder" errors resulting in SQLCODE errors or IB
errors in this case. At lease the IB Exception message texts are in
the system tables. I wish the message text was automatically returned
and available for processing. And, I wish I didn't have to explicitly
list each Exception I want to handle. This certainly leaves room for
error.
I'll post to the IBX group to see if anyone knows how to capture what
is displayed to the user during interactive processing. That must get
something from the Embedded capabilities - although I am trying to
handle it in a stored procedure.
--
Patrick Moloney