Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Posts
    15

    Unanswered: drop table if exists?

    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

  2. #2
    Join Date
    Aug 2006
    Location
    Riga, Latvia
    Posts
    17
    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 ( )"
    Last edited by intarsplienis; 08-23-06 at 11:08.

  3. #3
    Join Date
    Aug 2006
    Posts
    15
    thx but I found a simpler method.
    I look in systables if there is a tabname which suits mytemptablename.
    I hope thats save enough.

  4. #4
    Join Date
    Aug 2006
    Location
    Riga, Latvia
    Posts
    17
    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!

  5. #5
    Join Date
    Aug 2006
    Posts
    15
    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.

  6. #6
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    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

  7. #7
    Join Date
    Aug 2006
    Posts
    15
    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

  8. #8
    Join Date
    Aug 2006
    Posts
    15
    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

  9. #9
    Join Date
    Aug 2006
    Location
    Riga, Latvia
    Posts
    17
    try this:

    Code:
    CREATE PROCEDURE dropTable(tblName CHAR(20))
    ON EXCEPTION in (-206)
    END EXCEPTION WITH RESUME
    DROP TABLE tblName ;
    END PROCEDURE;
    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".
    Last edited by intarsplienis; 08-24-06 at 04:33.

  10. #10
    Join Date
    Aug 2006
    Posts
    15
    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.

  11. #11
    Join Date
    Aug 2006
    Location
    Riga, Latvia
    Posts
    17

    Post

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •