Folks:

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: <jhardin (AT) epicor (DOT) 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: news (AT) netfront (DOT) net ---

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.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) 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.



On Thu, Jan 28, 2010 at 8:44 PM, John Hardin <jhardin (AT) epicor (DOT) com> wrote:

John Hardin wrote:
You can try to do some operation on the table and handle the exception...
Regards.

"Art Kagel" <art.kagel (AT) gmail (DOT) com> wrote


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 Specialist
EPICOR Retail
web: http://www.epicor.com
voice: (425) 245-1800
fax: (425) 670-1810
email: <jhardin (AT) epicor (DOT) 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: news (AT) netfront (DOT) net ---

Hi.
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.



On Fri, Jan 29, 2010 at 3:51 PM, John Hardin <jhardin (AT) epicor (DOT) com> wrote:



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

"Fernando Nunes" <domusonline (AT) gmail (DOT) com> wrote

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!

--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
voice: (425) 245-1800
fax: (425) 670-1810
email: <jhardin (AT) epicor (DOT) 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: news (AT) netfront (DOT) net ---

On Jan 29, 10:58*am, "John Hardin" <jhar... (AT) epicor (DOT) com> wrote:
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.leffler (AT) gmail (DOT) com>

On Jan 29, 9:51*am, "John Hardin" <jhar... (AT) epicor (DOT) com> wrote:
sadadasasdsa