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/07/02 01:59:56 UTC
Temporary tables don't work?
My application needs to use a temporary table, but they don't seem to
work with the embedded version. When I insert or update rows the commands
appear to work, but when I look at the table there is sometimes some of
the data there and somerimes (more often) not.
I tried it with ij as a sanity check...
ij version 10.0
ij> connect 'foo';
ij> declare global temporary table t (n varchar(15), u varchar(15)) not logged;
0 rows inserted/updated/deleted
ij> insert into session.t values('a','b');
1 row inserted/updated/deleted
ij> select * from session.t;
N |U
-------------------------------
0 rows selected
ij>
Is this a bug, or am I doing something stupid? Can'y find anything
like this in the buglist...
-----------------------------------------------------------------
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 don't work?
Posted by John English <je...@brighton.ac.uk>.
> Mamta replied to your earlier e-mail on the subject, here is her reply.
>
> http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3cd9619e4a05062221576e4e634a@mail.gmail.com%3e
Thanks. I never saw any replies to my earlier post so I wasn't sure
if it had gone through.
-----------------------------------------------------------------
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 don't work?
Posted by Daniel John Debrunner <dj...@debrunners.com>.
John English wrote:
> My application needs to use a temporary table, but they don't seem to
> work with the embedded version. When I insert or update rows the commands
> appear to work, but when I look at the table there is sometimes some of
> the data there and somerimes (more often) not.
>
> I tried it with ij as a sanity check...
>
> ij version 10.0
> ij> connect 'foo';
> ij> declare global temporary table t (n varchar(15), u varchar(15))
> not logged;
> 0 rows inserted/updated/deleted
> ij> insert into session.t values('a','b');
> 1 row inserted/updated/deleted
> ij> select * from session.t;
> N |U
> -------------------------------
>
> 0 rows selected
> ij>
>
> Is this a bug, or am I doing something stupid? Can'y find anything
> like this in the buglist...
Mamta replied to your earlier e-mail on the subject, here is her reply.
http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3cd9619e4a05062221576e4e634a@mail.gmail.com%3e
Dan.
Re: Temporary tables don't work?
Posted by John English <je...@brighton.ac.uk>.
> Hi, John,
>
> Does Mamta's post about temp tables on June 22 help? see
> http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3cd9619e4a05062221576e4e634a@mail.gmail.com%3e
Thanks for this -- I didn't see any replies to my earlier post so
assumed it had got lost. Something funny with my mail system it
seems. Thanks again for pointing this out.
-----------------------------------------------------------------
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 don't work?
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Hi, John,
Does Mamta's post about temp tables on June 22 help? see
http://mail-archives.apache.org/mod_mbox/db-derby-user/200506.mbox/%3cd9619e4a05062221576e4e634a@mail.gmail.com%3e
If neither of her strategies works for you, please let us know.
regards,
-jean
John English wrote:
> My application needs to use a temporary table, but they don't seem to
> work with the embedded version. When I insert or update rows the commands
> appear to work, but when I look at the table there is sometimes some of
> the data there and somerimes (more often) not.
>
> I tried it with ij as a sanity check...
>
> ij version 10.0
> ij> connect 'foo';
> ij> declare global temporary table t (n varchar(15), u varchar(15))
> not logged;
> 0 rows inserted/updated/deleted
> ij> insert into session.t values('a','b');
> 1 row inserted/updated/deleted
> ij> select * from session.t;
> N |U
> -------------------------------
>
> 0 rows selected
> ij>
>
> Is this a bug, or am I doing something stupid? Can'y find anything
> like this in the buglist...
>
> -----------------------------------------------------------------
> 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 don't work?
Posted by John English <je...@brighton.ac.uk>.
> Take a look at Global temporary table documentation at:
> http://incubator.apache.org/derby/docs/10.0/manuals/reference/sqlj33.html#HDRDECLARETEMPTABLE
>
> The default behavior is to DELETE rows on a commit. So, right after your
> insert, a COMMIT is issued, which is deleting the rows. So, you need to
> declare the temporary table with ON COMMIT PRESERVE ROWS option.
> Otherway to see your insert data is by setting AUTOCOMMIT OFF in IJ,
> which prevents issuing a COMMIT. (until you issue a commit)
Thanks, I obviously missed this when I was reading the manual.
-----------------------------------------------------------------
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
-----------------------------------------------------------------