Re: Stored procedures and exceptions

}From: pkjones0@aol.com (Pkjones0) }Date: 20 Sep 1996 10:30:46 -0400 }X-Informix-List-Id: <news.28279> } }I'm relatively experienced in ESQL/C but have recently started dabbling in }stored procedures. } }The problem I have is as follows: } }............ }IF (<condition>) THEN } RAISE EXCEPTION <SQL status code> }END IF }............ } }The above code works fine when the SP is called from ESQL. However, I need }to return a meaningful text message from the SP. So what I've done is: } }............ }ON EXCEPTION IN (<SQL status code>) } return errMessage; }END EXCEPTION }............ }IF (<condition>) THEN } LET errMessage = "<A descriptive error message !!> } RAISE EXCEPTION <SQL status code> }END IF }............ } }Works fine except that the error status is cleared by the ON EXCEPTION }clause and so making my SQLCODE check in C redundant. } }Finally to the point. What I want to know is: }How do I set SQLCODE (or its equivalent within SPL) so that all I have to }check in C is the SQLCODE. I don't want to have to set the errMessage to }NULL and check it later as this is a rather clumsy method. The full syntax for RAISE EXCEPTION is: RAISE EXCEPTION SQLError [ISAMError [ Info string]] The error number reserved for user errors is -746 -- you get to supply the message string. So, you should probably do something like: DEFINE SQLCode INTEGER; DEFINE IsamCode INTEGR; DEFINE ErrText CHAR(80); ON EXCEPTION IN (...) SET SQLCode, IsamCode, ErrText RETURN -746, SQLCode, "A Descriptive Error Message"; END EXCEPTION; -- This sets the variable SQLCode to the SQL error number, the variable -- IsamCode to the ISAM Error, and the variable ErrText to the supporting -- information, if there is any. This might be a table name, a column -- name, a constraint name, or whetever is appropriate for the error in -- SQLCode. IsamCode is often 0, but may contain auxilliary error -- information. Note that the variables are defined by the user! IF (<condition>) THEN RAISE EXCEPTION -746, SQLCode, "<A descriptive error message !!>"; END IF You can, of course, simulate any error you like; a singularly unhelpful one would be: RAISE EXCEPTION -201, 0, "Ha Ha, Fooled You All!"; which generates 'A syntax error has occurred'. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>