On Fri, Sep 14, 2007 at 11:11:37AM -0400, Agustin Schapira wrote: > Hi, > > I am having problems with SQL TEMPORARY TABLES: even when I insert > rows into them, they remain empty. See for example:
Isn't sql great. The auto commit simply cleared your data. So or have one transaction with start transaction / commit or add 'ON COMMIT PRESERVE ROWS' to your create statement. Niels > > monetdb-> create local temporary table d (id int); > Operation successful > > monetdb-> insert into d values(1); > 1 affected row > > monetdb-> select * from d; > +--------+ > | id | > +========+ > +--------+ > 0 rows > > > > If I create a TEMPORARY TABLE from a SELECT ... WITH DATA statement, > it gets the correct structure but no data. Also, if I later insert > rows into the temporary table, it still remains empty. Here's a > sample session that demonstrates the problem: > > monetdb-> create table a (id int); > Operation successful > > monetdb-> insert into a values(1); > 1 affected row > > monetdb-> select * from a; > +--------+ > | id | > +========+ > | 1 | > +--------+ > 1 row > > > monetdb-> create temporary table b as select * from a with data; > Operation successful > > monetdb-> select * from b; > +--------+ > | id | > +========+ > +--------+ > 0 rows > > > monetdb-> insert into b values(5); > 1 affected row > > monetdb-> select * from b; > +--------+ > | id | > +========+ > +--------+ > 0 rows > > > monetdb-> insert into b select * from a; > 1 affected row > > monetdb-> select * from b; > +--------+ > | id | > +========+ > +--------+ > 0 rows > > > > This happens with the latest release, compiled yesterday with the -- > nightly='stable' switch: > > > # MonetDB Server v5.0.1 > # Copyright (c) 1993-2007 CWI, all rights reserved > # Compiled for i686-apple-darwin8.10.1/32bit with 32bit OIDs > dynamically linked > Database: MonetDB 1.18.3 > Driver: MonetDB Native Driver 1.6 (Steadfast/MCL-1.0 20070913) > auto commit mode: on > > > By the way, this used to work with MonetDB Server v5.0.0_beta1_2 > (although the WITH DATA option didn't load the data: I had to create > the structure and then INSERT into the table to get the rows). > > Any ideas? Am I doing something wrong? Are TEMPORARY TABLES not > supposed to be used like this? > > Thanks for your help, > > -- Agustin > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Microsoft > Defy all challenges. Microsoft(R) Visual Studio 2005. > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ > _______________________________________________ > Monetdb-developers mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/monetdb-developers -- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: [EMAIL PROTECTED] ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Monetdb-developers mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-developers
