Professional Web Applications Themes

Handling temporary tables in Informix - Informix

Hi, My Informix 4gl program has functions, which: 1) Creates a temp table, loads data into it 2) Access the temporary table Now in 2) , there is a line which resets connection to database by : DATABASE <database name> Note that this database name is same as used in 1) Now the program gives SQL error 206 for table not found at runtime. My query is whether this behaviour ( automatically dropping temporary tables, when database connection is reset) is consistent for Informix versions 7.2/7.31/9/3. Is there is any way to handle this. Currently using Informix 9.3 on HP-UX ...

  1. #1

    Default Handling temporary tables in Informix

    Hi,

    My Informix 4gl program has functions, which:
    1) Creates a temp table, loads data into it
    2) Access the temporary table

    Now in 2) , there is a line which resets connection to database by :
    DATABASE <database name>
    Note that this database name is same as used in 1)

    Now the program gives SQL error 206 for table not found at runtime.

    My query is whether this behaviour ( automatically dropping temporary
    tables, when database connection is reset) is consistent for Informix
    versions 7.2/7.31/9/3.

    Is there is any way to handle this.

    Currently using Informix 9.3 on HP-UX 11.

    Regards,
    Suket
    Suket Guest

  2. #2

    Default Re: Handling temporary tables in Informix

    On 3 Sep 2003 20:34:15 -0700, [email]suketsinhaindiatimes.com[/email] (Suket) wrote:
    >Hi,
    >
    >My Informix 4gl program has functions, which:
    >1) Creates a temp table, loads data into it
    >2) Access the temporary table
    >
    >Now in 2) , there is a line which resets connection to database by :
    >DATABASE <database name>
    >Note that this database name is same as used in 1)
    >
    >Now the program gives SQL error 206 for table not found at runtime.
    >
    >My query is whether this behaviour ( automatically dropping temporary
    >tables, when database connection is reset) is consistent for Informix
    >versions 7.2/7.31/9/3.
    >
    Yes, that is why they are called "temporary".
    >Is there is any way to handle this.
    Make "permanent" table or don't "reconnect" to database. Why are you doing it,
    anyway?

    Nebojsa
    ------------------------------------
    Remove spam block (DELETE_) to reply
    Nebojsa Sevo Guest

  3. #3

    Default Re: Handling temporary tables in Informix


    The scope of a temporary table is limited to the life
    of the session, so do all your processing inside of a
    transaction to avoid all the hard work of building the
    temp table.
    begin work;
    create temp table tab1;
    insert into tab1 ..........;
    select blah from tab1 ;
    commit work;

    Hope it helps.
    -Abraham

    --- Suket <suketsinhaindiatimes.com> wrote:
    > Hi,
    >
    > My Informix 4gl program has functions, which:
    > 1) Creates a temp table, loads data into it
    > 2) Access the temporary table
    >
    > Now in 2) , there is a line which resets connection
    > to database by :
    > DATABASE <database name>
    > Note that this database name is same as used in 1)
    >
    > Now the program gives SQL error 206 for table not
    > found at runtime.
    >
    > My query is whether this behaviour ( automatically
    > dropping temporary
    > tables, when database connection is reset) is
    > consistent for Informix
    > versions 7.2/7.31/9/3.
    >
    > Is there is any way to handle this.
    >
    > Currently using Informix 9.3 on HP-UX 11.
    >
    > Regards,
    > Suket

    __________________________________
    Do you Yahoo!?
    Yahoo! SiteBuilder - Free, easy-to-use web site design software
    [url]http://sitebuilder.[/url]
    sending to informix-list
    Abraham Kirubakaran Guest

  4. #4

    Default Re: Handling temporary tables in Informix

    Suket wrote:
    > My Informix 4gl program has functions, which:
    > 1) Creates a temp table, loads data into it
    > 2) Access the temporary table
    >
    > Now in 2) , there is a line which resets connection to database by :
    > DATABASE <database name>
    > Note that this database name is same as used in 1)
    >
    > Now the program gives SQL error 206 for table not found at runtime.
    >
    > My query is whether this behaviour ( automatically dropping temporary
    > tables, when database connection is reset) is consistent for Informix
    > versions 7.2/7.31/9/3.
    >
    > Is there is any way to handle this.
    >
    > Currently using Informix 9.3 on HP-UX 11.
    And which version of I4GL?

    Other people gave you the 'hard luck' story -- I'm going to give you a
    couple of alternative comments that might help.

    First of all, in the days of SE's dominance, there was a trick which
    people pulled with temp tables which did involve closing a database
    with a temp table and then opening another database and relying on a
    bug in SE which kept the table around between the two. However,
    IMNSHO, it was never anything other than a bug and never worked
    reliably with OnLine or IDS (AFAICR). And it didn't work reliably
    with SE if you weren't using local databases.

    Secondly, you probably can connect to the database multiple times,
    using the CONNECT statement (probably WITH CONCURRENT TRANSACTIONS).
    However, you cannot access a temp table on one connection inside
    another connection - but you can pull data out of the temp table on
    connection1 into memory, and then push it back to the database on
    connection2. If there's a single database involved as stated, this is
    pretty pointless -- don't go disconnecting and reconnecting if you
    want to keep your temp tables around. But there can be cirstances
    (such as database1 is logged and database2 is unlogged) where you
    cannot do distributed operations between the two.


    --
    Jonathan Leffler #include <disclaimer.h>
    Email: [email]jlefflerearthlink.net[/email], [email]jlefflerus.ibm.com[/email]
    Guardian of DBD::Informix v2003.04 -- [url]http://dbi.perl.org/[/url]

    Jonathan Leffler Guest

  5. #5

    Default Re: Handling temporary tables in Informix



    Don't connect to the other database.

    If you need information on the other database use sintax:
    a) <database>:<table_name> -- iif on same server
    b) <database><server>:<table_name> -- iif on another server

    If you need to do it as you describe, unload the temp table to file
    (unload) connect to the other database, create the temporary table and
    load the file you previously created... Don't suggest doing so.

    Chucho!

    Suket wrote:
    > Hi,
    >
    > My Informix 4gl program has functions, which:
    > 1) Creates a temp table, loads data into it
    > 2) Access the temporary table
    >
    > Now in 2) , there is a line which resets connection to database by :
    > DATABASE <database name>
    > Note that this database name is same as used in 1)
    >
    > Now the program gives SQL error 206 for table not found at runtime.
    >
    > My query is whether this behaviour ( automatically dropping temporary
    > tables, when database connection is reset) is consistent for Informix
    > versions 7.2/7.31/9/3.
    >
    > Is there is any way to handle this.
    >
    > Currently using Informix 9.3 on HP-UX 11.
    >
    > Regards,
    > Suket
    >
    >
    --


    Atte,


    Jesús Antonio Santos Giraldo
    [email]jeansagimyrealbox.com[/email]
    [email]jeansaginetscape.net[/email]

    sending to informix-list
    Jean Sagi Guest

Similar Threads

  1. Temporary tables and disk activity
    By Phil Endecott in forum PostgreSQL / PGSQL
    Replies: 11
    Last Post: December 13th, 10:24 PM
  2. MySql temporary tables
    By Andu in forum PHP Development
    Replies: 1
    Last Post: September 16th, 03:31 AM
  3. Replies: 1
    Last Post: September 8th, 02:20 AM
  4. Temporary Tables
    By Phil Jackson in forum IBM DB2
    Replies: 2
    Last Post: August 22nd, 05:05 AM
  5. global temporary tables
    By Blair Adamache in forum IBM DB2
    Replies: 0
    Last Post: July 31st, 07:44 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139