How to tell if a given temporary table exists in the current session?
John Hardin
I have a need to detect and drop temporary tables from SQL if they exist in
my session. It's the standard
IF EXISTS temp_table_x THEN
DROP TABLE temp_table_x;
END IF;
SELECT ... INTO TEMP temp_table_x;
logic.
I am aware that temporary tables are automatically dropped when the session
closes. This does not help as I cannot guarantee the SP that creates a given
temporary table will only be called once per session. Connection pooling and
other connection-reusing client application behaviors must be dealt with
gracefully.
Simply ignoring the error that dropping a nonexistent table generates causes
problems if something else blows up, as that will cause the error generated
by a "SELECT ... FROM table_that_should_exist_but_doesnt;" query to be
ignored. This is not acceptable behavior, as it can lead to silent failure.
I've been googling, but all of the solutions I've found so far that involve
trolling around in sysmaster to enumerate temporary tables are not reliably
session-specific.
Surely "onstat -g ses" must be querying the information from the system
tables _somehow_! How do I duplicate that logic?
I hate to say this but because of the partial solutions provided to others
who have asked this question in the past I want to be perfectly clear:
Parsing the output of "onstat -g ses" is not an option, as this is for use
within a stored procedure. Please don't suggest anything that is not
session-specific. Any query that solves this problem is going to involve
dbinfo('sessionid') at some point, and must not return results if a
temporary table with the name in question has been created in a different
session but not in the current session. It also cannot rely on username to
differentiate the temporary tables, multiple different sessions may be using
the same username to connect to the database and run the same stored
procedure.
Thanks in advance to anybody who can describe how this could be done.
--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
voice: (425) 245-1800
fax: (425) 670-1810
email: <jha...@epicor.com>
20818 44th Ave. W., Suite 270
Lynnwood, WA 98036 USA
Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA
------------------------------------------------------------------------
The first time I saw a bagpipe, I thought the player was torturing an
octopus. I was amazed they could scream so loudly.
------------------------------------------------------------------------
--- news://freenews.netfront.net/ - complaints: ne...@netfront.net ---
Art Kagel
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Fernando Nunes
You can try to do some operation on the table and handle the exception...
Regards.
John Hardin
news:mailman.509.12647345...@iiug.org...
> On Thu, Jan 28, 2010 at 8:44 PM, John Hardin <jha...@epicor.com> wrote:
>
>> I have a need to detect and drop temporary tables from SQL if they exist
>> in
>> my session.
>
> The only way to do this in an SPL routine is to DROP the temp table at the
> beginning of the routine, but trap the -206 error that occurs when the
> table
> is not found and ignore it. Then continue to create the temp table as if
> it
> had been dropped successfully. Not elegant, but it's the only way that
> works.
It doesn't work very well. How do you restore trapping of -206 exceptions so
that valid errors of that sort in the stored procedure are properly trapped?
Ignoring exceptions generated for what arguably should not even _be_ an
error in the first place will also ignore valid exceptions that do indicate
serious problems in the process that need to be fixed.
Fernando Nunes
Not elegant, but you can set a variable to flag it before the expected error happens and reset it after.
In the exception code test the flag variable... If it flags the code area, ignore it, else handle it.
Something like:
on exception (-206)
IF flag != 1 THEN
-- do legitimate exception code
END IF
END EXCEPTION
LET flag=0
....
....
LET flag=1
DROP TABLE temp_table
LET flag=0
...
...
Regards.
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
John Hardin
"Fernando Nunes" <domus...@gmail.com> wrote in message
news:mailman.512.12647851...@iiug.org...
>
> On Fri, Jan 29, 2010 at 3:51 PM, John Hardin <jha...@epicor.com> wrote:
>
>> "Art Kagel" <art....@gmail.com> wrote in message
>> news:mailman.509.12647345...@iiug.org...
>>
>> > On Thu, Jan 28, 2010 at 8:44 PM, John Hardin <jha...@epicor.com>
>> > wrote:
>> >
>> >> I have a need to detect and drop temporary tables from SQL if they
>> >> exist in my session.
>> >
>> > The only way to do this in an SPL routine is to DROP the temp table at
>> > the beginning of the routine, but trap the -206 error that occurs when
>> > the table is not found and ignore it. Then continue to create the temp
>> > table as if it had been dropped successfully. Not elegant, but it's
>> > the
>> > only way that works.
>>
>> It doesn't work very well. How do you restore trapping of -206 exceptions
>> so that valid errors of that sort in the stored procedure are properly
>> trapped?
>>
>> Ignoring exceptions generated for what arguably should not even _be_ an
>> error in the first place will also ignore valid exceptions that do
>> indicate serious problems in the process that need to be fixed.
>
> DROP TABLE temp_table
> LET flag=0
That works, but... eww. :)
As an elegant solution is apparently impossible, I've just restructured all
my SPs so that the DROP TABLEs are isolated in a helper SP that does nothing
but ignore the -206 exception and drop the list of temporary tables that the
primary SP will be creating. I'd hoped to avoid doing that, but I think it's
the least disgusting hack to achieve this.
Can anybody tell my _why_ that even throws an exception in the first place?
It's not logically defensible to generate an error because you've been asked
to delete something that does not exist - the end state of that failure mode
is identical to the end state of a success!
Jonathan Leffler
> "Fernando Nunes" <domusonl...@gmail.com> wrote in message
> news:mailman.512.12647851...@iiug.org...
> > On Fri, Jan 29, 2010 at 3:51 PM, John Hardin <jhar...@epicor.com> wrote:
> >> "Art Kagel" <art.ka...@gmail.com> wrote in message
> >>news:mailman.509.12647345...@iiug.org...
> >> > On Thu, Jan 28, 2010 at 8:44 PM, John Hardin <jhar...@epicor.com>
When you request dropping a table - any table - it is a problem if the
table does not exist.
Hence, an error is reported - and the error is -206.
And alternative view is: it did that back in 1986 when the ISQL
product was released; backwards compatibility means it still does.
If you study the syntax of SPL exception blocks carefully enough, you
can arrange for the block to apply to a single statement:
BEGIN
ON EXCEPTION IN (-206) END EXCEPTION WITH RESUME;
DROP TABLE temptablename;
END;
You can see the effect with this procedure:
DROP PROCEDURE ignore_missing_temp;
CREATE PROCEDURE ignore_missing_temp() RETURNING INT;
BEGIN
ON EXCEPTION IN (-206) END EXCEPTION WITH RESUME;
DROP TABLE does_not_exist;
END;
DROP TABLE missing_in_action;
RETURN(0);
END PROCEDURE;
SELECT * FROM dual INTO TEMP missing_in_action;
EXECUTE PROCEDURE ignore_missing_temp();
EXECUTE PROCEDURE ignore_missing_temp();
The first time through, the table 'missing_in_action' exists, so the
procedure returns 0 successfully.
The second time through, the table 'missing_in_action' is MIA, so the
procedure fails with an error.
Both time, the table 'does_not_exist' does not exist, but the
procedure works past that.
So, by using the notation shown, you can indeed get the procedure to
handle the one table you do not mind being absent, while still having
it object to other tables that are missing.
If you set a debug file and turn trace on with the statements above,
you get:
trace on
drop table does_not_exist;
exception : looking for handler
SQL error = -206 ISAM error = -111 error string = = "does_not_exist"
exception : handler FOUND
drop table missing_in_action;
procedure ignore_missing_temp returns 0
iteration of cursory procedure ignore_missing_temp
trace on
drop table does_not_exist;
exception : looking for handler
SQL error = -206 ISAM error = -111 error string = = "does_not_exist"
exception : handler FOUND
drop table missing_in_action;
exception : looking for handler
SQL error = -206 ISAM error = -111 error string = =
"missing_in_action"
exception : no appropriate handler
Have fun...
Jonathan Leffler <jonathan...@gmail.com>
reddyar...@gmail.com
> "Art Kagel" <art.ka...@gmail.com> wrote in message
>
> news:mailman.509.12647345...@iiug.org...
>
> > On Thu, Jan 28, 2010 at 8:44 PM, John Hardin <jhar...@epicor.com> wrote:
>
> >> I have a need to detect and drop temporary tables from SQL if they exist
> >> in
> >> my session.
>
> > The only way to do this in an SPL routine is to DROP the temp table at the
> > beginning of the routine, but trap the -206 error that occurs when the
> > table
> > is not found and ignore it. Then continue to create the temp table as if
> > it
> > had been dropped successfully. Not elegant, but it's the only way that
> > works.
>
> It doesn't work very well. How do you restore trapping of -206 exceptions so
> that valid errors of that sort in the stored procedure are properly trapped?
>
> Ignoring exceptions generated for what arguably should not even _be_ an
> error in the first place will also ignore valid exceptions that do indicate
> serious problems in the process that need to be fixed.
>
> --
> John Hardin KA7OHZ
> Senior Applications Developer, BI SpecialistEPICORRetail
> web:http://www.epicor.com
> voice: (425) 245-1800
> fax: (425) 670-1810
> 20818 44th Ave. W., Suite 270
> Lynnwood, WA 98036 USA
> Worldwide Headquarters 18200 Von Karman, Suite 1000, Irvine CA 92612 USA
> ------------------------------------------------------------------------
> The first time I saw a bagpipe, I thought the player was torturing an
> octopus. I was amazed they could scream so loudly.
> ------------------------------------------------------------------------
>
sadadasasdsa