Re: Reserved Error Nums for "Raise Exception" Statement?

>From: rvdbergh@allserv.rug.ac.be (Ria Vandenberghe) >Subject: Re: Reserved Error Nums for "Raise Exception" Statement? >Date: 23 Jun 1994 07:29:25 GMT >X-Informix-List-Id: <news.7322> >Ken Miles (kenm@vcd.hp.com) wrote: >: My 12/91, "Guide to SQL Reference" Manual, Page 8-67 on stored procedures >: says "The RAISE EXCEPTION statement is used to simulate an error. >: The generated error can be trapped by an ON EXCEPTION statement". >: This is great except that nowhere can I find a statement that tells >: me what error numbers Informix has reserved for my use. >We use the following stored procedure to generate an error message from >within a triggered action : >CREATE PROCEDURE Error_Message (Message VARCHAR (80)) > RAISE EXCEPTION -746, 0, Message;>END PROCEDURE; >I'm not sure whether this error number is reserved for use with triggers >only, or not, but you could give it a try. Error -746 has the text: %s This means it simply takes whatever you supply as the string (Message in the example above) and prints it. It is intended precisely to be used in exception handling from stored procedures. When I'm reporting an error from a stored procedure, I use a real error number in the secondary (ISAM) error position -- 0 in the example above. So, for example, if I am denying someone the right to update the table, I will use RAISE EXCEPTION -746, -273, "Explanation of why"; This is merely a refinement of what Ria suggested. If you do this, I suggest that you avoid parameterised error numbers in the secondary (ISAM) error. For instance, if you used: RAISE EXCEPTION -746, -206, "Explanation of why you can't use table"; You would get one of three undesirable behaviours. Either you'd see the secondary message in the form: -206: The specified table (Explanation of why you can't use table) is not inthe database. Or you'd see gibberish because no string was passed to sprintf() when the message was formatted, Or you'd see a core dump because no string was passed to sprintf(). And a fourth possibility is that an error message buffer would overflow because the error message with 'Explanation ... table' in it is longer than expected, with undesirable side effects (if you're lucky, it'll be a core dump; if you're unlucky, it'll just lead to to invisible corruption of key data somewhere). All the bona fide ISAM error messages are parameterless, so the code that reports errors may take advantage of this. You were warned! Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>