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
SPL Sample code from similar series:
CREATE PROCEDURE add_salesperson(last CHAR(15),
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;
After procedure are created, you can execute by using SQL statement "execute procedure proc ( )"
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.
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.
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 ;
Other option is using IF statement like:
IF tblName= "whatever" THEN
drop table whatever;
ELIF tblName= "whatever2" THEN
DROP table whatever2;