db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Temporary tables don't work?
Date Sat, 02 Jul 2005 00:22:38 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Take a look at Global temporary table documentation at:
<a class="moz-txt-link-freetext" href="http://incubator.apache.org/derby/docs/10.0/manuals/reference/sqlj33.html#HDRDECLARETEMPTABLE">http://incubator.apache.org/derby/docs/10.0/manuals/reference/sqlj33.html#HDRDECLARETEMPTABLE</a><br>
<br>
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)<br>
<br>
ij&gt; declare global temporary table t (n varchar(15), u varchar(15))
not logged;<br>
0 rows inserted/updated/deleted<br>
ij&gt; <b>autocommit off;</b><br>
ij&gt;&nbsp; insert into session.t values('a','b');<br>
1 row inserted/updated/deleted<br>
ij&gt; select * from session.t;<br>
N&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|U<br>
-------------------------------<br>
a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|b<br>
<br>
1 row selected<br>
<br>
Satheesh<br>
<br>
John English wrote:<br>
<blockquote cite="mid42C5D8FC.8000404@brighton.ac.uk" type="cite">My
application needs to use a temporary table, but they don't seem to
  <br>
work with the embedded version. When I insert or update rows the
commands
  <br>
appear to work, but when I look at the table there is sometimes some of
  <br>
the data there and somerimes (more often) not.
  <br>
  <br>
I tried it with ij as a sanity check...
  <br>
  <br>
&nbsp; ij version 10.0
  <br>
&nbsp; ij&gt; connect 'foo';
  <br>
&nbsp; ij&gt; declare global temporary table t (n varchar(15), u
varchar(15)) not logged;
  <br>
&nbsp; 0 rows inserted/updated/deleted
  <br>
&nbsp; ij&gt; insert into session.t values('a','b');
  <br>
&nbsp; 1 row inserted/updated/deleted
  <br>
&nbsp; ij&gt; select * from session.t;
  <br>
&nbsp; N&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|U
  <br>
&nbsp; -------------------------------
  <br>
  <br>
&nbsp; 0 rows selected
  <br>
&nbsp; ij&gt;
  <br>
  <br>
Is this a bug, or am I doing something stupid? Can'y find anything
  <br>
like this in the buglist...
  <br>
  <br>
-----------------------------------------------------------------
  <br>
&nbsp;John English&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| <a class="moz-txt-link-freetext" href="mailto:je@brighton.ac.uk">mailto:je@brighton.ac.uk</a>
  <br>
&nbsp;Senior Lecturer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| <a class="moz-txt-link-freetext" href="http://www.it.bton.ac.uk/staff/je">http://www.it.bton.ac.uk/staff/je</a>
  <br>
&nbsp;Dept. of Computing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
| ** NON-PROFIT CD FOR CS STUDENTS **
  <br>
&nbsp;University of Brighton&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;
-- see <a class="moz-txt-link-freetext" href="http://burks.bton.ac.uk">http://burks.bton.ac.uk</a>
  <br>
-----------------------------------------------------------------
  <br>
  <br>
  <br>
  <br>
</blockquote>
</body>
</html>


Mime
View raw message