Re: Exception Handling in Stored Proc's

Sorry, I have copied the statements without excluding the error code. So the fragment should be as: BEGIN ON EXCEPTION END EXCEPTION WITH RESUME; DROP TABLE dah;END; SELECT x, y, z INTO TEMP dah WITH NO LOG; I think there is no much trouble in ignroing ALL errors while dropping a temporary table. You can see that table is dropped anytime the procedure is called, just before the "select into temp". To get the error code you could use the following template: DEFINE errcode, isamerr INT; DEFINE errmsg CHAR(78); ... ON EXCEPTION SET errcode, isamerr, errmsg -- Now you have these variables set to the values of error code -- isam error code and to the error message -- You could do : LET errmsg = "currentProcedure:"||errmsg; RAISE EXCEPTION errcode, isamerr, errmsg; -- At least I'm doing so. As a result I get the SP call stack -- in errmsg (sqlca structure in application) END EXCEPTION ; Hope it helps, Octav Shew Leong wrote: >Hi Octav, > >I tried what you suggested and it worked the 1st time but on the 2nd try. >Informix errored out with table is not in the database. I guess what it did >was try to drop the table even it was error -310. > >I went through the manual again and tried: > >BEGIN > ON EXCEPTION IN (-310) SET err_num > IF err_num = -310 THEN > DROP TABLE dah;> END IF > > SELECT ....> >END > >What happened was it didn't recogize err_num so I DEFINEd it as INTEGER. But >I got the same message - table is not in the database. > >Question now is, is there a way to assign the error number returned from >Informix ? Is there a system variable that the error number is stored in? > >Shew > >Octav Chiriac wrote in message <71p0m3$blp$1@news.xmission.com>... >> >>Let's hope I understand your problem. >>Try this: >> >> >>BEGIN >> ON EXCEPTION IN (-310) >> END EXCEPTION WITH RESUME; >> DROP TABLE dah;>>END; >> >>SELECT x, y, z >> INTO TEMP dah WITH NO LOG;>> >> >>Hope it helps, >>Octav >> >>Shew Leong wrote: >>>Hi, >>> >>>Can someone explain how ON EXCEPTION works in stored proc's. If you have >>>sample code, it would be much appreciated. >>> >>>I'm trying to trap table already exist - error number -310. If the table >>>exist >>>then I want to drop it. >>> >>>Here's the code I've tried (syntax is off the top of my head): >>> >>>BEGIN >>> ON EXCEPTION IN (-310) >>> DROP TABLE tempMan >>> END EXCEPTION WITH RESUME>>> >>> BEGIN >>> SELECT x, y, z >>> INTO TEMP dah >>> WITH NO LOG >>> END >>>END >>>END>>> >>>Thanks >>> >>> >> -- Octav Chiriac Phone: (373) 2 21 20 96 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 24 00 83 Moldova, Republic of mailto:com@netinfo-moldova.com