Monday, October 12, 2009

Global Exception Handler

Stephan.ahlf posted an excellent suggestion in this thread in the Google SQL Anywhere Web Development group:

"It would be a nice feature if developers could implement a Global Exception Handler. In case of errors in procedures invoked via a database event there is only a error message in the database console. With a Global Exception Handler I could develop a general error notification e.g via email. There is an event for after RAISEERROR. Does there exists somthing like that for native database exceptions?"
Eric Farrar thinks it's a good idea too, which is wonderful news; Eric is highly respected throughout the SQL Anywhere community and his word should carry some significant weight:
"Unfortunately, there is nothing inside of SQL Anywhere to allow you to specify a top-level exception handler that will be called on any native database exception. I do understand why this would be useful (especially in a web context), and I have passed this idea on to the appropriate engineers."
Exception handling plays a huge role in the web-based Foxhound database monitor, and I would love to be able to code an all-encompassing global exception handler to record errors for later diagnosis.

One problem facing a global exception handler, however, is providing an answer to the question "Where, in all the thousands of lines of SQL code, did this exception occur?"

That's a hard question to answer even for a local exception handler. One solution (used by Foxhound) is to maintain a "where am I?" variable as the code executes just so an exception handler can include it with the other information it saves and/or displays.

For a global exception handler, one could use a global CREATE VARIABLE to store the "where am I?" value, but I would rather not... I would rather have SQL Anywhere provide the source code line number and object name (procedure, event, service, etc).

No comments: