By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,896 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,896 IT Pros & Developers. It's quick & easy.

Error Handling in Java Stored Procedures (V7.2)

P: n/a
I've written several Java stored procedures now (DB2 V7.2) and I'd like to
write down a few "best practices" for reference so that I will have them
handy for future development.

Would the experts here agree with the following? Would they add any other

1. If the shop standard calls for logging of application errors, a stored
procedure should log any error that it encounters immediately upon detecting
it and then return to the calling program without executing any further
statements in the stored procedure. The information logged should include: a
timestamp showing when the error was detected; the full name of the stored
procedure, including the schema name; a stack trace (if a stack trace can be
generated); some identification of which specific statement in the stored
procedure detected the error; SQLState; SQLCode; and number or number and
text of DB2 message associated with the error, for example "SQL0101N" or
"SQL0101N - The statement is too long or too complex" . Sufficient
information should also be returned to the calling program so that it knows
the stored procedure hit an error as well as any information that the shop
standards say should be displayed for the user when an error is encountered.
The calling program should then display the information to the user in the
appropriate way, such as a printout or GUI panel.

2. If the shop standard does not call for logging of application errors, the
stored procedure should return to the calling application immediately
without executing any further statements in the stored procedure. All
information needed to debug the error should be returned to the calling
program, including the SQLState, the SQLCode, the DB2 message associated
with the error (or at least its number), a stack trace (assuming a stack
trace can be generated), and some indication of which statement in the
stored procedure encountered the problem. The latter could be the line
number of the statement in the source code of the stored procedure, the
statement itself, or an application message identifying the statement, for
example "Detected error trying to get connection from calling program" or
"Error encountered while attempting to prepare the query".

Also, would everyone agree with me that the code generated by the wizard in
the Stored Procedure Builder is weak for the following reasons:
- all of the generated code is generally in a single try/catch block, making
it difficult to detect which statement in the try block actually encountered
the error
- the wizard has no provision for returning stack traces or messages coded
by the developer

I'm inclined to recommend that developers take the code generated by the
wizard and split each statement out so that it is in its own try/catch block
and that each catch block returns to the caller after encountering an error
rather than waiting until the end of the procedure. Would everyone agree
that this is a good idea?

Also, I'm curious to know how widely application logging is used. Do most
shops insist on developers writing to application logs when errors are
encountered or do most shop standards simply call for error messages and
debug information to be displayed on consoles, printouts, or on GUI screens?
I tend to agree with the philosophy that the end user should not see any
information that he will not likely understand or need and that everything
needed to debug the error should be written to a log, not to printouts or
GUI screens. But I'm open-minded and would be interested in other points of

rhino1 AT sympatico DOT ca
"If you're getting something for nothing, you're not using your own credit
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.