Hello,

now that I dare to play with temporary tables, I ran into the next problem
To be sure that my "select into temp" statement works fine, I would like to drop the temp table in advance. How do I find out if there is any table to drop?
If I try to drop an not existing table, I produce an error and I cant catch it, because the connection is done by a self made class (not by me) that pops up errors in a messagebox
There must be something like
drop table if exists mytemptable
but that only produces an syntax error.
What is the correct syntax for that? My online documentation on publib.ibm ist not responding

mfg
Sven

You can use SPL.
Create procedure and inside procedure use ON EXCEPTION ...

Syntax is under section SPL Statements.
http://www.database.lv/4367.pdf

SPL Sample code from similar series:
CREATE PROCEDURE add_salesperson(last CHAR(15),
first CHAR(15))
RETURNING INT;
DEFINE x INT;
ON EXCEPTION IN (-206) -- If no table was found, create one
CREATE TABLE emp_list
(lname CHAR(15),fname CHAR(15), tele CHAR(12));
INSERT INTO emp_list VALUES -- and insert values
(last, first, '800-555-1234');
END EXCEPTION WITH RESUME
INSERT INTO emp_list VALUES (last, first, '800-555-1234')
LET x = SELECT count(*) FROM emp_list;
RETURN x;
END PROCEDURE

After procedure are created, you can execute by using SQL statement "execute procedure proc ( )"

thx but I found a simpler method.
I look in systables if there is a tabname which suits mytemptablename.
I hope thats save enough.

In environment where many users will use this approach, they individually will create own temporary tables with the same name. If only one user use this routine, than that is enough!

Good luck!

I just found out, that it is not working as I hoped.
When I look into systables, searching for mytemptablename, the result is empty but the "select into temp" throws an error, telling me that the table does already exist.
The problem is, that there is a server running, getting the sql statements via http, executing them and sending the resultsets back via http.
I have no idea how that server works, if it uses session pooling or what ever is ruining my plan
Next try is using ON EXECPTION inside an procedure.

here is what I use in my stored procedures

on exception in (-206)
-- do nothing
let o_sql_err = 0;
let o_isam_err = 0;
let o_err_text = "";
end exception with resume;


Hope this helps

Thanks for all the help.
I have never seen an Informix procedure before but I hoped this would be one. All what my Editor tells me, is "Error: A syntax error has occurred." That is not very helpful

Please help me. Where have I done wrong?

CREATE PROCEDURE dropTable(tblName CHAR(20)) RETURNING INT;
ON EXCEPTION in (-206)
DROP TABLE tblName RESTRICTED;
END EXCEPTION WITH RESUME
RETURN 1;
END PROCEDURE

sorry, the procedure above was my first try. the one with a little more understanding of SPL is this one. still producing nothing more than an syntax error


CREATE PROCEDURE dropTable(tblName CHAR(20));
ON EXCEPTION in (-206)
let o_sql_err = 0;
let o_isam_err = 0;
let o_err_text = "";
END EXCEPTION WITH RESUME
DROP TABLE tblName RESTRICTED;
END PROCEDURE

try this:

But take into account, that string in procedure "tblName" must be replaced by real table name.

For example:
1. Create procedure with code above
2. select * tablename into temptable;
3. Actually drops table :

execute procedure droptable("whatever"); && procedure will delete table "tblname" not "whatever".

ok, it seems that my RESTRICTED caused the syntax error but why is tblName not the parameter where I put the name of the table to be dropped in?
What do I have to do to make the procedure able to delete other tables than "tblName"?
The example procedure seems to get the parameters last and first and fills their contens into the table emp_list, not the strings "first" and "last".
Why is my tblName not a variable? I thought that I have done everything like the example shows.

Hi,
DROP TABLE sql statement using with variables is not possible as I know,
but in one SPL procedure you can use multiply DROP table statements and then it drop those temp tables, that exists - if of course procedure aim is to clean all existing temp tables for particular user session:

CREATE PROCEDURE dropTable(tblName CHAR(20))
ON EXCEPTION in (-206)
END EXCEPTION WITH RESUME

DROP TABLE tblName1 ;
DROP TABLE tblName2 ;
DROP TABLE tblName3 ;
DROP TABLE tblName4 ;
DROP TABLE tblName5 ;

END PROCEDURE;


Other option is using IF statement like:

IF tblName= "whatever" THEN
drop table whatever;
ELIF tblName= "whatever2" THEN
DROP table whatever2;
END IF