Re: Exception Handling in Stored Proc's

I'll assume that you've already noticed the obvious, being that the table you're dropping isn't the same as the table you're trying to create (at least, not according to your syntax). Assuming you've fixed that, here's what I can see: From what I can see, you're trying to create a temp table inside a stored procedure. Problem is, sometimes the temp table already exists. So what you've tried to do is trap the "table already exists" error and drop the table when that happens, as in: { ON EXCEPTION IN (-310) DROP TABLE dah;END EXCEPTION WITH RESUME; ... SELECT stuff INTO TEMP dah;<NEXT_STMT> } The problem here is that when you do the SELECT INTO TEMP and you get the -310 error, you run the exception, which drops the dah table. The procedure then resumes, but it doesn't re-try the SELECT INTO TEMP; rather, it will resume at <NEXT_STMT>. This is problematic. I usually attack that problem the other way. Right before doing the SELECT INTO TEMP 'dah', I drop table 'dah', ignoring the potential -206 error (table does not exist). Example follows: ON EXCEPTION IN (-206) -- Don't do anything, just ignore the error. END EXCEPTION WITH RESUME; DROP TABLE dah; SELECT stuff INTO TEMP dah; This should work for you... - Tom Girsch Database Systems Manager Arch Communications Group, Inc. Shew Leong wrote in message <71qnrb$cmq$1@news.interlog.com>... >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 > >