MySQL Forums
Forum List  »  Triggers

Re: How to Raise Error ?
Posted by: Roland Bouman
Date: February 24, 2006 08:28AM

The discussion as to what is the best method is not very interesting. All the work-around methods discussed so far (unique constraint violation see: http://mysql.gilfster.com/page.php?parent_id=2&page_id=2.0.7; UDF see: http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html; non-existing stored proc: this thread) essentially boil down to deliberately causing an error or constraint violation, and inevitably, the block or caller that wants to handle it is stuck figuring out what the REAL problem might be (as Andrew pointed out already).

If you still want to weigh the advantages and disadvantages for the workarounds, I feel one should consider the following criteria:

1) how much chance is there make a mistake in handling the error

Because all the workarounds revolve around abusing another constraint or condition, there is a reasonable risk of handling inappropriately to the raised condition. Unfortunately, because the error code or error message is not visible inside a mysql proc (anyone care to add a comment on this feature request? It might help: http://bugs.mysql.com/bug.php?id=11660) neither of the workarounds by itself are very good in this respect. Just relying on the abilities of the client to read out the error code or error message is no good: this would leave the responsibility to handle the errors with the application layer - often not a good place.
A solution -sort of- can be enabled for all these workarounds however: by packing the actual error raising device inside a stored procedure (somewhat like RAISE_APPLICATION_ERROR in oracle), you can set one or multiple user variables (such as: @error_code, @error_message or @sqlstate, @offending_proc, @offending_block, knock yourself out...) just prior to actually raising the error. These can then be checked by whatever handler. Of course, these variables should be reserved for this purpose only, and be cleaned up after handling the error. In the UDF article, I suggested such a mechanism (at the bottom) but it should work equally well for the other workarounds as well.

2) how much dependencies are involved to set the workaround up

Here, Zigmund's method seems to have an edge as it requires absolutely no work in advance to make it work. The UDF involves of course some work, but after deploying it, it works for every database. For the unique violation, each database needs to set up it's own table - or stored procedure - or at least, the privileges need to be granted to execute the sp (BTW, does anyone know if you can grant something to everyone - something like the grant blabla to PUBLIC in oracle? ).
Of course, when we want to be able to actually handle the error we raised, we will need stored procedure encapsulation (as suggested under 1) anyway, so we're stuck with the privilege thing regardless of the workaround.
On the other hand, Zigmund's method needs a disciplined set of developers to work. It should remain clear what stored procedures are in fact mockups to get the workaround working.

Zigmund, maybe you'll care to explain WHY your method is better? You certainly seem to have a strong feeling about it...

Options: ReplyQuote


Subject
Views
Written By
Posted
25043
November 17, 2005 04:00AM
16730
November 17, 2005 06:58AM
23682
November 19, 2005 08:53AM
14504
February 23, 2006 02:28AM
10792
February 23, 2006 04:42AM
Re: How to Raise Error ?
15454
February 24, 2006 08:28AM
8485
February 24, 2006 10:15AM
6274
February 24, 2006 12:42PM
7892
February 27, 2006 08:56AM
6691
February 27, 2006 11:27AM
18878
February 28, 2006 05:22AM
6540
December 20, 2006 03:34AM
4104
November 18, 2008 08:28AM
4750
November 18, 2008 12:45PM
6163
December 19, 2006 11:00AM
4963
December 19, 2006 02:35PM
6216
December 19, 2006 07:53PM
4409
December 20, 2006 03:37AM
4614
December 19, 2006 11:01AM
5493
November 24, 2006 05:32AM
4470
November 24, 2006 07:02AM
3616
December 23, 2008 06:08AM
11181
February 25, 2006 11:48PM
4945
December 19, 2007 04:24AM
3919
December 24, 2008 01:02PM
4765
December 19, 2007 03:59AM
4623
December 19, 2007 05:09AM
3520
November 18, 2008 08:25AM
3904
November 18, 2008 12:49PM
6005
December 19, 2007 04:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.