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

On Dec 15, 2:05 pm, Dyreatn...@xxxxxxx wrote:
CJ <spamb...@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 visithttp://db.apache.org/derby/derby_mail.html

I have use SQL engines that use naming conventions such as
"#tableName" and "##tableName." In the former, one pound character (#)
the behavior is similar to the Derby declared global temporary table,
the latter, the double pound character (##) creates a temporary table
that is available to all within the scope of the schema.

IMHO, something labeled as "global" that restricts access to the
entity that created is not global in any definition I can imagine.

cj
.

Relevant Pages

  • Re: Global temporary tables in Java DB -- Apache Derby
    ... I found that adding "ON COMMIT PRESERVE ... ROWS," or as you noted turning off auto commit "corrects" my bug. ... This limitation is imposed by the SQL standard AFAICT. ... temporary table _cannot_ be shared between SQL sessions like the ...
    (comp.lang.java.databases)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... SQL CE/Mobile has a background thread that flushes the changes ... they run for long hours and we have not seen memory leaks so far. ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... So it is a kind of asynchronous commit which happens every 10 ... I am not sure of this claim as we have many applications built on SQL CE ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Access 2003 adp/proxy security - A substitute for SYSTEM_USER()
    ... We need a way for SQL to know the nt username that initiated the proxy ... Many stored procedures and views use a UDF that also depends on ... SyUserLogin and use HostIDand HostName() to find the right row. ... We've played with the Connection dialog settings, ...
    (microsoft.public.access.security)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)