Re: Q:How to use RAISE EXCEPTION in SPL?

> We are using Online 5.00 UC4 on a Sun Sparc 10/41 clone and I have > problems in getting RAISE EXCEPTION clause of SPL to work (I'm > comfortablewith stored procedure manipulation, FYI). When the stored > procedure encountersa line like: > > RAISE EXCEPTION -99999,0,"You broke the rules." > > (example in the manual) it stops with the error: > > 1214: Value exceeds limit of SMALLINT precision> > when I give an error code within SMALLINT range like -9999, now it says: > > 9999: Cannot open file 'ace.iem'.> > and trying other values gives either similar "Cannot open..."s or other > errorsdepending on which range the code I have given lies in. So what's > going wrong? Previously other problems with creating stored procedures > had led us torecognize that our Informix products were installed the > wrong way round and wehad our local tech. support install it again. Your first problem is due to SPL not allowing numbers greater than 32767 (ie. SMALLINT size). The second is because the numbers you are trying to use are in-use as real live error codes by Informix, so SPL retrieves the message for that code from its message files on the exeception return. The text you supply with your RAISE EXCEPTION statement is used to substitute the '%s' variable which appears in some messages. If you use error no, I think it's -769 or something close by, the error text is simply '%s', so it takes whatever message you supplied. The version 6.00 manuals tell you all about using this. The number is specifically set aside for custom SPL errors. The disadvantage is that your front-end can't use the number alone to distinguish between one application-raised error and another; and a call to err_get() would be pretty useless. One technique I've tried is to use the error numbers 5000 to 5999. These were reserved for the ancient File-It! product that Informix sold way back in the early 80s. Use mkmessage to define your own error text for these. Now, you can have each number define a different condition and err_get() should do something sensible (although I've not tried this end of it). The risk is that if Informix ever decide to re-use 5000 - 5999 for something else in the future, you're.... well, on your own. My guess is that since these relate to a former product, albeit a very old one, they'll leave re-using these numbers till last. akent@cix.compulink.co.uk (Andy Kent) -------------------------------------