Thanks! That was very helpful. with ON COMMIT PRESERVE ROWS it works fine.
/Jonas 2011/11/11 Rick Hillegas <[email protected]>: > On 11/11/11 4:39 AM, Jonas Pettersson wrote: >> >> DECLARE GLOBAL TEMPORARY TABLE SESSION.memtable (id int, name >> varchar(10)) NOT LOGGED >> > Hi Jonas, > > It's likely that you are running with autocommit turned on (this is the > default). Your temporary table was declared in a way which causes it to > throw away all of its rows at commit. You can preserve rows over commit by > adding another clause to the DECLARE GLOBAL TEMPORARY TABLE statement. The > following example may help: > > ij> connect 'jdbc:derby:memory:db;create=true'; > ij> declare global temporary table session.t_default (id int) not logged; > 0 rows inserted/updated/deleted > ij> declare global temporary table session.t_preserve (id int) on commit > preserve rows not logged; > 0 rows inserted/updated/deleted > ij> insert into session.t_default( id ) values ( 1 ); > 1 row inserted/updated/deleted > ij> insert into session.t_preserve( id ) values ( 1 ); > 1 row inserted/updated/deleted > ij> select * from session.t_default; > ID > ----------- > > 0 rows selected > ij> select * from session.t_preserve; > ID > ----------- > 1 > > 1 row selected > > Hope that's useful, > -Rick >
