Re: Exception Handling in Stored Proc's

Hey Octav, I tell you tomorrow on how it goes. Shew Octav Chiriac wrote in message <71sa5c$joq$1@news.xmission.com>... > >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