Professional Web Applications Themes

SP Temp Tables - Informix

Hi All, I am creating temp tables and dropping them after the processing is done in a stored procedure , however if users abort and try to run again we get the error " t_temp etc.. already in the database" . Is there any workaround to check if this temp table exists then drop it and create the temp table again .. Navdeep __________________________________________________ _______________ See when your friends are online with MSN Messenger 6.0. Download it now FREE! [url]http://msnmessenger-download.com[/url] sending to informix-list...

  1. #1

    Default SP Temp Tables


    Hi All,

    I am creating temp tables and dropping them after the processing is done in
    a stored procedure , however if users abort and try to run again we get the
    error " t_temp etc.. already in the database" . Is there any workaround to
    check if this temp table exists then drop it and create the temp table again
    ..


    Navdeep

    __________________________________________________ _______________
    See when your friends are online with MSN Messenger 6.0. Download it now
    FREE! [url]http://msnmessenger-download.com[/url]

    sending to informix-list
    navdeep virk Guest

  2. #2

    Default Re: SP Temp Tables

    If the user aborts in the SPL code you can trap on the exception and
    tidy up. Otherwise always drop the table first and then use the
    exception
    to resume a failed drop.

    navdeep virk wrote:
    >
    > Hi All,
    >
    > I am creating temp tables and dropping them after the processing is done in
    > a stored procedure , however if users abort and try to run again we get the
    > error " t_temp etc.. already in the database" . Is there any workaround to
    > check if this temp table exists then drop it and create the temp table again
    > .
    >
    > Navdeep
    >
    > __________________________________________________ _______________
    > See when your friends are online with MSN Messenger 6.0. Download it now
    > FREE! [url]http://msnmessenger-download.com[/url]
    >
    > sending to informix-list
    --
    Paul Watson #
    Oninit Ltd # Growing old is mandatory
    Tel: +44 1436 672201 # Growing up is optional
    Fax: +44 1436 678693 #
    Mob: +44 7818 003457 #
    [url]www.oninit.com[/url] #
    Paul Watson Guest

  3. #3

    Default Re: SP Temp Tables




    "navdeep virk" <nvirkmsn.com> wrote in message news:bnbo7c$k58$1terabinaries.xmission.com...
    >
    > Hi All,
    >
    > I am creating temp tables and dropping them after the processing is done in
    > a stored procedure , however if users abort and try to run again we get the
    > error " t_temp etc.. already in the database" . Is there any workaround to
    > check if this temp table exists then drop it and create the temp table again
    > .
    There are two ways of dealing with this problem.

    1. Create an exception block to ignore errors and drop the temp table at the
    start of the procedure. No error will be returned if the table does not exists.

    BEGIN
    ON EXCEPTION
    --
    END EXCEPTION WITH RESUME

    DROP TABLE TMP_TABLE ;

    END

    2. Run this query at the start of the SP.

    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.

    I would go for option (1).

    Ravi



    --

    email id is bogus


    rkusenet Guest

  4. #4

    Default Re: SP Temp Tables

    Here's a procedure I have that does the same kind of thing you're talking
    about. You could essentialy add the begin end block logic to the top of
    your procedure.

    Hope this helps.

    Gregg Walker

    Create Procedure ClearShiftEntries(
    pShiftCardID integer
    )

    --set debug file to "/tmp/ClearShiftEntries.out";
    --trace on;

    begin

    on exception in (-206)

    begin

    create temp table tShiftEntry (
    shift_card_id integer not null,
    line_num smallint default 0 not null,
    entry_time datetime year to second not null,
    clock_entry_id integer
    )
    with no log;

    end

    end exception with resume

    delete from
    tShiftEntry
    where
    shift_card_id = pShiftCardID;

    end

    End Procedure; -- ClearShiftEntries

    "navdeep virk" <nvirkmsn.com> wrote in message
    news:bnbo7c$k58$1terabinaries.xmission.com...
    >
    > Hi All,
    >
    > I am creating temp tables and dropping them after the processing is done
    in
    > a stored procedure , however if users abort and try to run again we get
    the
    > error " t_temp etc.. already in the database" . Is there any workaround to
    > check if this temp table exists then drop it and create the temp table
    again
    > .
    >
    >
    > Navdeep
    >
    > __________________________________________________ _______________
    > See when your friends are online with MSN Messenger 6.0. Download it now
    > FREE! [url]http://msnmessenger-download.com[/url]
    >
    > sending to informix-list

    Gregg Walker Guest

  5. #5

    Default Re: SP Temp Tables



    As you could see there are some "ways" to lead to this "issue"...

    What I wondwer is: Why this happen? Why a delete delay to delete?

    Funny...


    Chucho!

    navdeep virk wrote:
    > Hi All,
    >
    > I am creating temp tables and dropping them after the processing is done
    > in a stored procedure , however if users abort and try to run again we
    > get the error " t_temp etc.. already in the database" . Is there any
    > workaround to check if this temp table exists then drop it and create
    > the temp table again .
    >
    >
    > Navdeep
    >
    > __________________________________________________ _______________
    > See when your friends are online with MSN Messenger 6.0. Download it now
    > FREE! [url]http://msnmessenger-download.com[/url]
    >
    > sending to informix-list
    >
    >
    --


    Atte,


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

    sending to informix-list
    Jean Sagi Guest

  6. #6

    Default Re: SP Temp Tables

    do this before using the temp table

    if object_id("[tempdb].[dbo].[#TempTableName]") is not null drop #TempTableName
    create table #TempTableName
    (
    column1 DataType1,
    column2 DataType2,
    ...
    )


    This solves the problem.
    -Yordanos
    Unregistered Guest

  7. #7

    Default Re: SP Temp Tables

    do this before using the temp table

    if object_id("[tempdb].[dbo].[#TempTableName]") is not null drop #TempTableName
    create table #TempTableName
    (
    column1 DataType1,
    column2 DataType2,
    ...
    )


    This solves the problem.
    -Yordanos
    yordanos is offline Junior Member
    Join Date
    Feb 2011
    Location
    Maryland
    Posts
    1

Similar Threads

  1. Dynamic identification of Informix temp tables
    By ddodgeaz in forum Informix
    Replies: 2
    Last Post: August 29th, 09:31 PM
  2. Temp tables and jdbc 2.21.jc3 on IDS 9.4.UC1
    By Rajesh Kapur in forum Informix
    Replies: 0
    Last Post: August 26th, 09:59 PM
  3. Schedule maitenance or temp tables
    By Shawn in forum ASP Database
    Replies: 2
    Last Post: August 21st, 03:12 PM
  4. Accessing Temp Tables
    By Karthik Nagaraj in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 12th, 11:26 PM
  5. Question about optimizer with (big) temp tables
    By Eric Mamet in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 08:44 AM

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