You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by John English <je...@brighton.ac.uk> on 2005/06/23 01:43:45 UTC
Temporary tables not working???
I can't seem to get temporary tables to work for my application. As
a sanity check, i started ij and issued the following commands:
ij> declare global temporary table t1 (n varchar(100), u varchar(15), x float)
not logged;
0 rows inserted/updated/deleted
ij> insert into session.t1 values('Hazelden, D.P.E.','DPEH',null);
1 row inserted/updated/deleted
ij> select * from session.t1;
N
|U |X
--------------------------------------------------------------------------------
-----------------------------------------------------------
0 rows selected
ij> insert into session.t1 values('O''Reilly, A.','AO''R',null);
1 row inserted/updated/deleted
ij> select * from session.t1;
N
|U |X
--------------------------------------------------------------------------------
-----------------------------------------------------------
0 rows selected
In my application, I find that sometimes one of the inserts "succeeds"
in that data ends up in the table. Other times, not. No exceptions are
thrown to indicate an error (which according to TFM will delete all rows
in the table). Using ij, it's more consistent (it just never works AFAICT)
but although the insertions seem to be successful ("1 row inserted" and
suchlike), the table is still empty.
The version I'm using is from incubating-derby-snapshot-10.0-165169.zip,
FWIW.
Is this a bug, or am I doing something completely crass? Advice please!
-----------------------------------------------------------------
John English | mailto:je@brighton.ac.uk
Senior Lecturer | http://www.it.bton.ac.uk/staff/je
Dept. of Computing | ** NON-PROFIT CD FOR CS STUDENTS **
University of Brighton | -- see http://burks.bton.ac.uk
-----------------------------------------------------------------
Re: Temporary tables not working???
Posted by Mamta Satoor <ms...@gmail.com>.
Hi John,
In Derby, autocommit is true by default. And, the temporary tables by
default have ON COMMIT DELETE ROWS enabled on them. And that is why, after
insert, when you do a select, you don't see any rows in the temporary table.
If you would like temporary tables to hold onto their data over commit, you
need to declare them as follows
declare global temporary table t1 (n varchar(100), u varchar(15), x float)
on commit preserve rows not logged;
Or alternatively, set autocommit to false and then your syntax for declare
global temporary table will be sufficient and data won't be lost until the
next commit.
Hope this helps,
Mamta
On 6/22/05, John English <je...@brighton.ac.uk> wrote:
>
> I can't seem to get temporary tables to work for my application. As
> a sanity check, i started ij and issued the following commands:
>
> ij> declare global temporary table t1 (n varchar(100), u varchar(15), x
> float)
> not logged;
> 0 rows inserted/updated/deleted
> ij> insert into session.t1 values('Hazelden, D.P.E.','DPEH',null);
> 1 row inserted/updated/deleted
> ij> select * from session.t1;
> N
> |U |X
>
> --------------------------------------------------------------------------------
> -----------------------------------------------------------
>
> 0 rows selected
> ij> insert into session.t1 values('O''Reilly, A.','AO''R',null);
> 1 row inserted/updated/deleted
> ij> select * from session.t1;
> N
> |U |X
>
> --------------------------------------------------------------------------------
> -----------------------------------------------------------
>
> 0 rows selected
>
> In my application, I find that sometimes one of the inserts "succeeds"
> in that data ends up in the table. Other times, not. No exceptions are
> thrown to indicate an error (which according to TFM will delete all rows
> in the table). Using ij, it's more consistent (it just never works AFAICT)
> but although the insertions seem to be successful ("1 row inserted" and
> suchlike), the table is still empty.
>
> The version I'm using is from incubating-derby-snapshot-10.0-165169.zip,
> FWIW.
>
> Is this a bug, or am I doing something completely crass? Advice please!
>
> -----------------------------------------------------------------
> John English | mailto:je@brighton.ac.uk
> Senior Lecturer | http://www.it.bton.ac.uk/staff/je
> Dept. of Computing | ** NON-PROFIT CD FOR CS STUDENTS **
> University of Brighton | -- see http://burks.bton.ac.uk
> -----------------------------------------------------------------
>
>