Writing Exceptions

Care should be taken when writing exceptions; when you are dealing with hundreds of thousands of data sets, you need to source your problem quickly. For example, a message such as this:

could well occur after a database has been up and successfully running for years.

To detect such errors on a customer database where a lot of stored procedures with several hundred steps have been written, and which may run several hundred thousand times before the error occurs, is of course difficult.

This particular error message occurred because a data set was altered in the EMPLOYEE table; the first record, Robert Nelson, was amended to Robert Joseph Nelson-Katzenberger:

This in itself is not an error, as the fields FIRST_NAME and LAST_NAME have been specified as varchar(15) and varchar(20) respectively.

The problem arises when the stored procedure, ORG_CHART, is executed. If we take a look at the IBExpert Tools menu item, SP/Triggers/Views Analyzer, we can see where the problem lies:

The stored procedure's output parameter, MNGR_NAME has been defined as varchar(20); however the FULL_NAME from the EMPLOYEE table is specified at varchar(37). So, any FIRST_NAME_LAST_NAME combination containing a total of more than 20 characters, will fire an exception.

You can then go on to use the stored procedure debugger to trace the data set that has caused the problem. This process can however be very time-consuming when you have hundreds of thousands of data sets.

In Firebird it is possible to do the following: write an exception which will give you more information when this error occurs. Create a new exception, name it, add the exception message to your procedure, which should appear when the error occurs, e.g. when any do exception unknown_error;. And a really useful feature since FB 1.5: you can change the text on the fly (i.e. in the procedure itself):

It is even possible to combine the message with, for example, the department number:

This is much more useful for the error finding process, as you have already narrowed down the error to a specific department number. This is possible in every stored procedure that you use. The more obvious solution would be, in this case, to alter the return parameter, mngr_name from char(20) to, for example, char(37).

So, when writing stored procedures, incorporate exception messages that indicate the source of the problem. Use the on-the-fly possibility to define detailed exception messages in your procedures.