Re: Temporary tables don't work?

[Satheesh Bandaram]

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) ij> declare global temporary table t (n varchar(15), u varchar(15)) not logged; 0 rows inserted/updated/deleted ij> autocommit off; ij>  insert into session.t values('a','b'); 1 row inserted/updated/deleted ij> select * from session.t; N              |U ------------------------------- a              |b 1 row selected Satheesh 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:[EMAIL PROTECTED]  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 -----------------------------------------------------------------