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
24968
November 17, 2005 04:00AM
16698
November 17, 2005 06:58AM
23648
November 19, 2005 08:53AM
14480
February 23, 2006 02:28AM
10765
February 23, 2006 04:42AM
Re: How to Raise Error ?
15424
February 24, 2006 08:28AM
8466
February 24, 2006 10:15AM
6244
February 24, 2006 12:42PM
7872
February 27, 2006 08:56AM
6670
February 27, 2006 11:27AM
18856
February 28, 2006 05:22AM
6516
December 20, 2006 03:34AM
4074
November 18, 2008 08:28AM
4724
November 18, 2008 12:45PM
6132
December 19, 2006 11:00AM
4939
December 19, 2006 02:35PM
6190
December 19, 2006 07:53PM
4387
December 20, 2006 03:37AM
4589
December 19, 2006 11:01AM
5471
November 24, 2006 05:32AM
4448
November 24, 2006 07:02AM
3591
December 23, 2008 06:08AM
11114
February 25, 2006 11:48PM
4929
December 19, 2007 04:24AM
3903
December 24, 2008 01:02PM
4743
December 19, 2007 03:59AM
4607
December 19, 2007 05:09AM
3504
November 18, 2008 08:25AM
3885
November 18, 2008 12:49PM
5984
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.