Re: Global temporary tables in Java DB -- Apache Derby

CJ <spambox1@xxxxxxxxxxxxxx> writes:

[snip]
No, dt, I type in the jist of the logic rather than cut/paste. After
reading again the section, I found that adding "ON COMMIT PRESERVE
ROWS," or as you noted turning off auto commit "corrects" my bug.
Unfortunately, I also read that though the syntax is "declare GLOBAL
temporary table" the table is not truly global, but specific to the
connection from which it was created.

This limitation is imposed by the SQL standard AFAICT. In SQL all
temporary tables, local or global, are local to the session.

"... the contents of a _global_ temporary table or a created local
temporary table _cannot_ be shared between SQL sessions like the
contents of a persistent base table. In addition, the contents of a
created _local_ temparary table cannot be shared between modules or
embedded SQL programs in a single SQL session." (Understanding the new
SQL: A complete guide, Melton & Simon, 1993)

Don't ask me what "modules and embedded SQL programs" would be in the
context of JDBC :)

I was looking to create that
would not become part of the Java DB "database" and thus not exist
when the JVM terminates abnormally. The data in the "temporary table"
is truly temporary, but needed so long as the JVM is operating.
However the temporary table data should not persist in the DB.

The temporary table disappears as soon as you close the connection. But
you cannot share the temp table with other connections, so if that is
what you need I guess you are out of luck :(

Because of the limitations of "declare global temporary table" I will
implement the table another way.

I'm sorry it doesn't work for you. Btw. the DECLARE GLOBAL TEMPORARY
syntax is not standard SQL. You can log that as a Java DB/Derby bug if
you want. See

http://db.apache.org/derby/DerbyBugGuidelines.html

--
dt

Questions about Derby/Java DB? Please visit
http://db.apache.org/derby/derby_mail.html
.

Relevant Pages

  • Re: Problem with XMLType indexing. Please confirm
    ... pasted your script verbatim into 10g release 10.1.0.2.0 personal on ... metalink for the bug fix. ... SQL> drop table bug; ... XMLType( ...
    (comp.databases.oracle.server)
  • RE: Undeclared tag ID % is used in a FOR XML Explicit Query
    ... As you have a test environment you should be able to test the change ... > In fact we have multiple SQL environment running the same config and Query, ... > on the SQL server running on Windows 2003. ... but is in fact a bug in SQL sever. ...
    (microsoft.public.sqlserver.programming)
  • Re: Global temporary tables in Java DB -- Apache Derby
    ... I found that adding "ON COMMIT PRESERVE ... This limitation is imposed by the SQL standard AFAICT. ... temporary table _cannot_ be shared between SQL sessions like the ... The temporary table disappears as soon as you close the connection. ...
    (comp.lang.java.databases)
  • Re: Quick Question
    ... but I'm not sure if it's a bug in SQL CE: ... Thanks for the information on RDA. ... >> Are there common reaons why a subscription would hang? ...
    (microsoft.public.sqlserver.ce)
  • Re: Bug in Database Maintenance Plans Enterprise Manager and MSDE v SQL
    ... Yep, this is a bug. ... Cannot Edit or Delete Database Maintenance Plan on MSDE Installation ... Looking for a SQL Server replication book? ... > The bug I am reporting is with the "Remove after X Weeks/Days" option for> Database Backup, Transaction Backup. ...
    (microsoft.public.sqlserver.msde)