+ Reply to Thread
Results 1 to 8 of 8

How to drop a temp table using "if exists" logic ?

  1. How to drop a temp table using "if exists" logic ?

    Hi
    Is there a way to drop a temp table in Informix using some kind of "if
    exists" logic ?
    In oter words I need to test for the existence of the temp table
    before issuing "drop table" command.
    Thanks a lot.
    Murty


  2. Re: How to drop a temp table using "if exists" logic ?

    Could you expand on why do you need to test if it exists before you
    drop it?

    If you just drop it and it doesn't exist the drop will have no effect
    other than that a status other than 0 will be returned from the drop
    command. If you don;t test that status there will be no problem.


    Veeru71 wrote:
    > Hi
    > Is there a way to drop a temp table in Informix using some kind of "if
    > exists" logic ?
    > In oter words I need to test for the existence of the temp table
    > before issuing "drop table" command.
    > Thanks a lot.
    > Murty



  3. Re: How to drop a temp table using "if exists" logic ?

    On 6 Dec 2006 01:18:15 -0800, scottishpoet wrote:
    > Could you expand on why do you need to test if it exists before you
    > drop it?
    >
    > If you just drop it and it doesn't exist the drop will have no effect
    > other than that a status other than 0 will be returned from the drop
    > command. If you don;t test that status there will be no problem.
    >
    >
    > Veeru71 wrote:
    > > Hi
    > > Is there a way to drop a temp table in Informix using some kind of "if
    > > exists" logic ?
    > > In oter words I need to test for the existence of the temp table
    > > before issuing "drop table" command.
    > > Thanks a lot.
    > > Murty

    >
    > _______________________________________________
    > Informix-list mailing list
    > Informix-list@iiug.org
    > http://www.iiug.org/mailman/listinfo/informix-list
    >


    Temp tables disappear at the end of the session anyway, so if you have
    created it in this session it will exist, if you haven't created it
    then it will not exist !!

    Keith

  4. Re: How to drop a temp table using "if exists" logic ?

    You can attempt to drop the table in a stored procedure unconditionally and
    ignore the specific error condition if it does not exist:

    CREATE PROCEDURE drop_temp_table()
    ON EXCEPTION IN (-206) END EXCEPTION;
    DROP TABLE temp_table;
    END PROCEDURE;

    You can't soft-code the table name without using a Datablade. See also the
    newsgroup thread entitled "Creating generic routine to drop procedure" started
    two days ago.

    --
    Regards,
    Doug Lawry
    www.douglawry.webhop.org


    "Veeru71" wrote in message
    news:1165378712.116761.199740@j72g2000cwa.googlegroups.com...
    > Hi
    > Is there a way to drop a temp table in Informix using some kind of "if
    > exists" logic ?
    > In oter words I need to test for the existence of the temp table
    > before issuing "drop table" command.
    > Thanks a lot.
    > Murty




  5. Re: How to drop a temp table using "if exists" logic ?

    This is a perfectly logical think to want to do. So, don't be so harsh
    with Keith. I think I have asked this question several times in
    different ways to try to trick the correct answer out of someone.

    Here is why this is important to have. We use connection pooling so you
    may get a connection that has been used by someone else recently. The
    problem is that we have code that has bugs where they don't drop their
    temp tables or they may have errored out before dropping their temp
    tables. So we end up leaking temp tables sometimes.

    It would be nice to have a query that could give you a list of the
    temporary tables for your session.

    select * from syssesiontemp;

    might be a nice system table for example.

    This way the pooling code could clean up the session for the next user.
    Nothing like a dirty session in the pool to mess things up.

    Keith Simmons wrote:
    > On 6 Dec 2006 01:18:15 -0800, scottishpoet wrote:
    > > Could you expand on why do you need to test if it exists before you
    > > drop it?
    > >
    > > If you just drop it and it doesn't exist the drop will have no effect
    > > other than that a status other than 0 will be returned from the drop
    > > command. If you don;t test that status there will be no problem.
    > >
    > >
    > > Veeru71 wrote:
    > > > Hi
    > > > Is there a way to drop a temp table in Informix using some kind of "if
    > > > exists" logic ?
    > > > In oter words I need to test for the existence of the temp table
    > > > before issuing "drop table" command.
    > > > Thanks a lot.
    > > > Murty

    > >
    > > _______________________________________________
    > > Informix-list mailing list
    > > Informix-list@iiug.org
    > > http://www.iiug.org/mailman/listinfo/informix-list
    > >

    >
    > Temp tables disappear at the end of the session anyway, so if you have
    > created it in this session it will exist, if you haven't created it
    > then it will not exist !!
    >
    > Keith



  6. Re: How to drop a temp table using "if exists" logic ?

    Thanks Bozon for articulating so clearly the need for the existence of
    temp tables.
    Somehow I feel that it is always much cleaner to check for existence
    and drop it if exists
    EXPLICITY, rather than firing the 'drop table' command and ignoring
    the warning.
    I could not find a solution though :)
    - Murty


  7. Re: How to drop a temp table using "if exists" logic ?


    "Veeru71" wrote in message
    news:1165830720.623257.143700@j72g2000cwa.googlegroups.com...
    > Thanks Bozon for articulating so clearly the need for the existence of
    > temp tables.
    > Somehow I feel that it is always much cleaner to check for existence
    > and drop it if exists
    > EXPLICITY, rather than firing the 'drop table' command and ignoring
    > the warning.
    > I could not find a solution though :)
    > - Murty
    >


    This is the query to find out whether temp table already exists
    in your session.
    SELECT count(*)
    into w_count
    from sysmaster:systabnames s,sysmaster:systabinfo i
    where i.ti_partnum = s.partnum
    and sysmaster:BITVAL(i.ti_flags,'0x0020') = 1
    and s.tabname = 'your_tmp_table_name' ;


    The query will return 1 if the temp table exists.



  8. Re: How to drop a temp table using "if exists" logic ?

    Veeru71 wrote:
    > Thanks Bozon for articulating so clearly the need for the existence of
    > temp tables.
    > Somehow I feel that it is always much cleaner to check for existence
    > and drop it if exists
    > EXPLICITY, rather than firing the 'drop table' command and ignoring
    > the warning.
    > I could not find a solution though :)
    > - Murty
    >

    Do you also test for existence of a data row before you attempt to
    update it?
    Makes for a good buffer pool hit ratio if nothing else...

    I actually presented on that very topic at WAIUG last week.
    FWIW, in a language that has condition handlers the code to drop a table
    is a lot cleaner that that spattered with error-tests.

    Cheers
    Serge
    --
    Serge Rielau
    DB2 Solutions Development
    IBM Toronto Lab

    WAIUG Conference
    http://www.iiug.org/waiug/present/Fo...Forum2006.html

+ Reply to Thread