Re: on exception in SPL

Thanks for the advice, Jonathan. What I was trying to do was keep the proc from blowing up if the temp table already existed for some reason. What I would really like is something like this: Create temp table x (... ) with drop and shutup. This would drop the temp if it existed before creating it. But I cannot find any such thing in the manuals. A lookup for the temp table in sysmaster does not work reliably for me. So I tried to set up a trap, but stepped into my own trap. 8-{ The "lookup" code I refer to was: -- SELECT count(*) into nrows -- from sysmaster:systabnames s, sysmaster:systabinfo i -- where i.ti_partnum = s.partnum -- and sysmaster:BITVAL(i.ti_flags,'0x0020') = 1 -- and s.tabname = 'commis_work' ; -- trace nrows; -- if nrows=1 then -- drop table commis_work; -- end if -- ----- Original Message ----- From: "Jonathan Leffler" <jleffler@earthlink.net> To: <informix-list@iiug.org> Sent: Thursday, July 14, 2005 12:43 AM Subject: Re: on exception in SPL > Bill Hamilton wrote: > > The code below does not work. > > I was trying to create the temp if it exists, otherwise flush it. > > Dropping it and falling into the create is ok, too. > > Is it possible to do something like this? > > > > BEGIN > > ON EXCEPTION IN (-958) set err_num > > > > if err_num = -958 then > > delete from pos_to_gen where 1=1;> > else > > create temp table pos_to_gen( > > reqn_no integer, > > disp_no integer, > > matcode char(20), > > cusno char(15), > > dropshp char(1), > > qtytogen integer, > > ordate datetime year to day, > > reqd_date datetime year to day, > > venno char(15), > > vennam char(35), > > line_no integer, > > doc_no integer, > > matid integer, > > specpo integer, > > rg_opnpo integer, > > aqid integer > > ) with no log;> > end if > > > > end exception with resume; > > END > > (a) In what sense doesn't it work? What is the error message? > (b) What exactly are you trying to do? > > The CREATE TEMP TABLE statement is part of the exception block - the > code that is handling the error. You need to execute the statement > outside the exception block to trigger the error: > > BEGIN > ON EXCEPTION IN (-958) > DELETE FROM pos_to_gen;> END EXCEPTION WITH RESUME; > CREATE TEMP TABLE pos_to_gen ( ... ) WITH NO LOG;> END > > That's unvalidated syntax -- check for the placement of semi-colons in > particular -- but I'm fairly sure that's the trouble too. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/ sending to informix-list

Bill Hamilton wrote: My alternative for what you want is to begin the SPL proc with: DROP TABLE mytemp_name; CREATE TEMP TABLE mytemp_name; And I include an exception handler for the -206 error that results if the table were never created which ignores the error and continues. That's cleaner and cheaper than deleting all the data in the temp table if it already exists. Art S. Kagel > Thanks for the advice, Jonathan. > What I was trying to do was keep the proc from blowing up if the temp table > already existed for some reason. > What I would really like is something like this: > Create temp table x > (> ... > ) with drop and shutup. > > This would drop the temp if it existed before creating it. > > But I cannot find any such thing in the manuals. > A lookup for the temp table in sysmaster does not work reliably for me. > So I tried to set up a trap, but stepped into my own trap. 8-{ > > The "lookup" code I refer to was: > -- SELECT count(*) into nrows > -- from sysmaster:systabnames s, sysmaster:systabinfo i > -- where i.ti_partnum = s.partnum > -- and sysmaster:BITVAL(i.ti_flags,'0x0020') = 1 > -- and s.tabname = 'commis_work' ; > -- trace nrows; > -- if nrows=1 then > -- drop table commis_work; > -- end if > -- > > > ----- Original Message ----- > From: "Jonathan Leffler" <jleffler@earthlink.net> > To: <informix-list@iiug.org> > Sent: Thursday, July 14, 2005 12:43 AM > Subject: Re: on exception in SPL > > > >>Bill Hamilton wrote: >> >>>The code below does not work. >>>I was trying to create the temp if it exists, otherwise flush it. >>>Dropping it and falling into the create is ok, too. >>>Is it possible to do something like this? >>> >>>BEGIN >>>ON EXCEPTION IN (-958) set err_num >>> >>>if err_num = -958 then >>> delete from pos_to_gen where 1=1;>>>else >>> create temp table pos_to_gen( >>> reqn_no integer, >>> disp_no integer, >>> matcode char(20), >>> cusno char(15), >>> dropshp char(1), >>> qtytogen integer, >>> ordate datetime year to day, >>> reqd_date datetime year to day, >>> venno char(15), >>> vennam char(35), >>> line_no integer, >>> doc_no integer, >>> matid integer, >>> specpo integer, >>> rg_opnpo integer, >>> aqid integer >>> ) with no log;>>>end if >>> >>>end exception with resume; >>>END >> >>(a) In what sense doesn't it work? What is the error message? >>(b) What exactly are you trying to do? >> >>The CREATE TEMP TABLE statement is part of the exception block - the >>code that is handling the error. You need to execute the statement >>outside the exception block to trigger the error: >> >>BEGIN >>ON EXCEPTION IN (-958) >> DELETE FROM pos_to_gen;>>END EXCEPTION WITH RESUME; >>CREATE TEMP TABLE pos_to_gen ( ... ) WITH NO LOG;>>END >> >>That's unvalidated syntax -- check for the placement of semi-colons in >>particular -- but I'm fairly sure that's the trouble too. >> >>-- >>Jonathan Leffler #include <disclaimer.h> >>Email: jleffler@earthlink.net, jleffler@us.ibm.com >>Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/ > > sending to informix-list

"Art S. Kagel" <kagel@bloomberg.net> schrieb im Newsbeitrag news:42D7DD12.4040202@bloomberg.net... > > My alternative for what you want is to begin the SPL proc with: > DROP TABLE mytemp_name; > CREATE TEMP TABLE mytemp_name;> Just a little warning here (and please tell me if this problem maybe is fixed now as I tried last time with IDS 7.3x on SCO...). I did what you proposed once and ran into really a hell of problems. What maybe can happen (as I found out after a long shitty day of testing): 1. The stored proc is executes fine first. 2. The System sees the DROP TABLE statement and recognizes that the Proc changes the database schema. It doesnt take into account that the table you drop is only a temp table (that is the 'bug'). 3. The System therefore regenerates the sysprocplan For the time the sysprocplan is regenerated (and in a transaction until someone commits or rolls back) the sysprocplan entry is locked. Therefore if you do this in a multiuser context the procedure regularely fails because because it cant update the sysprocplan. If you do a create temp table only on the other side the system is able to see that no changes are made on the non-temp schema and sees no reason to regenerate the sysprocplan info. As I said I dont know if this is still true for newer versions. But since that experience I avoid the above syntax. Regards, Dirk -- -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 28446-0 -- Hammer Str. 13 fax: +49 (0)251 28446-55 -- D-48153 Muenster http://www.GunCon.de/ -- "Toto, I don't think we're in Kansas anymore..."