SP Temp Tables
Hi All, I am creating temp tables and dropping them after the processing is done in a stored procedure , however if users abort and try to run again we get the error " t_temp etc.. already in the database" . Is there any workaround to check if this temp table exists then drop it and create the temp table again . Navdeep _________________________________________________________________ See when your friends are online with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com sending to informix-list
If the user aborts in the SPL code you can trap on the exception and tidy up. Otherwise always drop the table first and then use the exception to resume a failed drop. navdeep virk wrote: > > Hi All, > > I am creating temp tables and dropping them after the processing is done in > a stored procedure , however if users abort and try to run again we get the > error " t_temp etc.. already in the database" . Is there any workaround to > check if this temp table exists then drop it and create the temp table again > . > > Navdeep > > _________________________________________________________________ > See when your friends are online with MSN Messenger 6.0. Download it now > FREE! http://msnmessenger-download.com > > sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #
"navdeep virk" <nvirk@msn.com> wrote in message news:bnbo7c$k58$1@terabinaries.xmission.com...
>
> Hi All,
>
> I am creating temp tables and dropping them after the processing is done in
> a stored procedure , however if users abort and try to run again we get the
> error " t_temp etc.. already in the database" . Is there any workaround to
> check if this temp table exists then drop it and create the temp table again
> .
There are two ways of dealing with this problem.
1. Create an exception block to ignore errors and drop the temp table at the
start of the procedure. No error will be returned if the table does not exists.
BEGIN
ON EXCEPTION
--
END EXCEPTION WITH RESUME
DROP TABLE TMP_TABLE ;
END
2. Run this query at the start of the SP.
SELECT count(*)
into w_count
from sysmaster:systabnames s,sysmaster:systabinfo i
where i.ti_partnum = s.partnum
and sysmaster:BITVAL(i.ti_flags,'0x0020') = 1
and s.tabname = 'your_tmp_table_name' ;
The query will return 1 if the temp table exists.
I would go for option (1).
Ravi
--
email id is bogus
Here's a procedure I have that does the same kind of thing you're talking
about. You could essentialy add the begin end block logic to the top of
your procedure.
Hope this helps.
Gregg Walker
Create Procedure ClearShiftEntries(
pShiftCardID integer
)
--set debug file to "/tmp/ClearShiftEntries.out";
--trace on;
begin
on exception in (-206)
begin
create temp table tShiftEntry (
shift_card_id integer not null,
line_num smallint default 0 not null,
entry_time datetime year to second not null,
clock_entry_id integer
)
with no log;
end
end exception with resume
delete from
tShiftEntry
where
shift_card_id = pShiftCardID;
end
End Procedure; -- ClearShiftEntries
"navdeep virk" <nvirk@msn.com> wrote in message
news:bnbo7c$k58$1@terabinaries.xmission.com...
>
> Hi All,
>
> I am creating temp tables and dropping them after the processing is done
in
> a stored procedure , however if users abort and try to run again we get
the
> error " t_temp etc.. already in the database" . Is there any workaround to
> check if this temp table exists then drop it and create the temp table
again
> .
>
>
> Navdeep
>
> _________________________________________________________________
> See when your friends are online with MSN Messenger 6.0. Download it now
> FREE! http://msnmessenger-download.com
>
> sending to informix-list