Global temporary tables in Java DB -- Apache Derby

On Dec 12, 7:05 am, Dyreatn....DeleteThis@sun.com wrote:
> CJ writes:
> > I can't seem to use "declare create temporary table" to function in
> > Java DB, JVM 1.6.0_03 Linux. After starting an embedded Derby server,
> > I issue the following;
>
> > stmt.execute("SET SCHEMA myschema");
> > stmt.execute("DECLARE CREATE TEMPORARY TABLE SESSION.mytable (fld INT
> > NOT NULL) NOT LOGGED");
> > stmt.execute("INSERT INTO SESSION.mytable VALUES(1)");
> > System.err.println(stmt.getUpdateCount());
> > stmt.execute("SELECT * FROM SESSION.mytable");
>
> > The getUpdateCount after insert is one (1), however the query does not
> > return a resultset. What am I doing incorrectly?
>
> Statement.execute(String) doesn't return a result
> set. Statement.executeQuery(String) on the other hand, does.
>
> --
> dt
>
> Questions about Derby/Java DB? Please visithttp://db.apache.org/derby/derby_mail.html

Thanks dt. While you are correct that Statement.execute() does not
return a ResultSet object, Statement.getResultSet() should return a
ResultSet if Statement.execute(String) returns true.

Perhaps my post was incomplete. While stmt.execute("SELECT * FROM
SESSION.mytable") indicates at least one ResultSet was returned, no
data is available from the ResultSet returned from
"stmt.getResultSet()".
 >> Stay informed about: Global temporary tables in Java DB -- Apache Derby 

CJ writes:

> On Dec 12, 7:05 am, Dyreatn... RemoveThis @sun.com wrote:
>> CJ writes:
>> > I can't seem to use "declare create temporary table" to function in
>> > Java DB, JVM 1.6.0_03 Linux. After starting an embedded Derby server,
>> > I issue the following;
>>
>> > stmt.execute("SET SCHEMA myschema");
>> > stmt.execute("DECLARE CREATE TEMPORARY TABLE SESSION.mytable (fld INT
>> > NOT NULL) NOT LOGGED");
>> > stmt.execute("INSERT INTO SESSION.mytable VALUES(1)");
>> > System.err.println(stmt.getUpdateCount());
>> > stmt.execute("SELECT * FROM SESSION.mytable");
>>
>> > The getUpdateCount after insert is one (1), however the query does not
>> > return a resultset. What am I doing incorrectly?
>>
>> Statement.execute(String) doesn't return a result
>> set. Statement.executeQuery(String) on the other hand, does.
>>
>> --
>> dt
>>
>> Questions about Derby/Java DB? Please visithttp://db.apache.org/derby/derby_mail.html
>
> Thanks dt. While you are correct that Statement.execute() does not
> return a ResultSet object, Statement.getResultSet() should return a
> ResultSet if Statement.execute(String) returns true.
>
> Perhaps my post was incomplete. While stmt.execute("SELECT * FROM
> SESSION.mytable") indicates at least one ResultSet was returned, no
> data is available from the ResultSet returned from
> "stmt.getResultSet()".

Did you, in fact, copy the exact code you are running into your initial
news posting? When I try

stmt.execute("DECLARE CREATE TEMPORARY TABLE SESSION.mytable (fld INT
NOT NULL) NOT LOGGED");

I get a syntax error:

Exception in thread "main" java.sql.SQLSyntaxErrorException: Syntax
error: Encountered "CREATE" at line 1, column 9

According to the manual (reference guide)
http://db.apache.org/derby/docs/dev/ref/

the correct syntax is

DECLARE GLOBAL TEMPORARY TABLE SESSION.mytable (fld INT NOT NULL) NOT
LOGGED

When I try:
c.setAutoCommit(false);
Statement stmt = c.createStatement();
try { stmt.execute("CREATE SCHEMA MYSCHEMA"); } catch (SQLException e) {}
stmt.execute("SET SCHEMA MYSCHEMA");
stmt.execute("DECLARE GLOBAL TEMPORARY TABLE SESSION.mytable (fld INT NOT NULL) NOT LOGGED");
stmt.execute("INSERT INTO SESSION.mytable VALUES(1)");
System.err.println(stmt.getUpdateCount());
stmt.execute("SELECT * FROM SESSION.mytable");
ResultSet rs = stmt.getResultSet();
while(rs.next()) {
System.out.println("rs:"+rs.getInt(1));
}

it prints

1
rs:1

as expected.

Note that you do need to turn auto commit off (it is on by default),
because otherwise the rows in your temporary table will
disappear. The manual says:

"DELETE ROWS

All rows of the table will be deleted if no hold-able cursor is open on
the table. This is the default value for ON COMMIT."


Alternatively you can declare your temporary table as:

DECLARE GLOBAL TEMPORARY TABLE SESSION.mytable (fld INT NOT NULL) ON
COMMIT PRESERVE ROWS NOT LOGGED

HTH

--
dt

Questions about Derby/Java DB? Please visit
http://db.apache.org/derby/derby_mail.html
 >> Stay informed about: Global temporary tables in Java DB -- Apache Derby 

On Dec 14, 12:26 pm, Dyreatn... DeleteThis @sun.com wrote:
> CJ writes:
> > On Dec 12, 7:05 am, Dyreatn... DeleteThis @sun.com wrote:
> >> CJ writes:
> >> > I can't seem to use "declare create temporary table" to function in
> >> > Java DB, JVM 1.6.0_03 Linux. After starting an embedded Derby server,
> >> > I issue the following;
>
> >> > stmt.execute("SET SCHEMA myschema");
> >> > stmt.execute("DECLARE CREATE TEMPORARY TABLE SESSION.mytable (fld INT
> >> > NOT NULL) NOT LOGGED");
> >> > stmt.execute("INSERT INTO SESSION.mytable VALUES(1)");
> >> > System.err.println(stmt.getUpdateCount());
> >> > stmt.execute("SELECT * FROM SESSION.mytable");
>
> >> > The getUpdateCount after insert is one (1), however the query does not
> >> > return a resultset. What am I doing incorrectly?
>
> >> Statement.execute(String) doesn't return a result
> >> set. Statement.executeQuery(String) on the other hand, does.
>
> >> --
> >> dt
>
> >> Questions about Derby/Java DB? Please visithttp://db.apache.org/derby/derby_mail.html
>
> > Thanks dt. While you are correct that Statement.execute() does not
> > return a ResultSet object, Statement.getResultSet() should return a
> > ResultSet if Statement.execute(String) returns true.
>
> > Perhaps my post was incomplete. While stmt.execute("SELECT * FROM
> > SESSION.mytable") indicates at least one ResultSet was returned, no
> > data is available from the ResultSet returned from
> > "stmt.getResultSet()".
>
> Did you, in fact, copy the exact code you are running into your initial
> news posting? When I try
>
> stmt.execute("DECLARE CREATE TEMPORARY TABLE SESSION.mytable (fld INT
> NOT NULL) NOT LOGGED");
>
> I get a syntax error:
>
> Exception in thread "main" java.sql.SQLSyntaxErrorException: Syntax
> error: Encountered "CREATE" at line 1, column 9
>
> According to the manual (reference guide)http://db.apache.org/derby/docs/dev/ref/
>
> the correct syntax is
>
> DECLARE GLOBAL TEMPORARY TABLE SESSION.mytable (fld INT NOT NULL) NOT
> LOGGED
>
> When I try:
> c.setAutoCommit(false);
> Statement stmt = c.createStatement();
> try { stmt.execute("CREATE SCHEMA MYSCHEMA"); } catch (SQLException e) {}
> stmt.execute("SET SCHEMA MYSCHEMA");
> stmt.execute("DECLARE GLOBAL TEMPORARY TABLE SESSION.mytable (fld INT NOT NULL) NOT LOGGED");
> stmt.execute("INSERT INTO SESSION.mytable VALUES(1)");
> System.err.println(stmt.getUpdateCount());
> stmt.execute("SELECT * FROM SESSION.mytable");
> ResultSet rs = stmt.getResultSet();
> while(rs.next()) {
> System.out.println("rs:"+rs.getInt(1));
> }
>
> it prints
>
> 1
> rs:1
>
> as expected.
>
> Note that you do need to turn auto commit off (it is on by default),
> because otherwise the rows in your temporary table will
> disappear. The manual says:
>
> "DELETE ROWS
>
> All rows of the table will be deleted if no hold-able cursor is open on
> the table. This is the default value for ON COMMIT."
>
> Alternatively you can declare your temporary table as:
>
> DECLARE GLOBAL TEMPORARY TABLE SESSION.mytable (fld INT NOT NULL) ON
> COMMIT PRESERVE ROWS NOT LOGGED
>
> HTH
>
> --
> dt
>
> Questions about Derby/Java DB? Please visithttp://db.apache.org/derby/derby_mail.html

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. 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.

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

Thanks for your, help.
cj
 >> Stay informed about: Global temporary tables in Java DB -- Apache Derby 

CJ wrote:
> No, dt, I type in the jist [sic] of the logic rather than cut/paste.

It is easier to get useful advice if you quote your code and error messages
precisely.

--
Lew
 >> Stay informed about: Global temporary tables in Java DB -- Apache Derby 

CJ 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
 >> Stay informed about: Global temporary tables in Java DB -- Apache Derby 

On Dec 15, 2:05 pm, Dyreatn....TakeThisOut@sun.com wrote:
> CJ 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
 >> Stay informed about: Global temporary tables in Java DB -- Apache Derby 

CJ writes:

[snip]

> 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.

It may not seem reasonable, but that is what

ISO/IEC 9075-2:2003
Section 4.14.2
9th paragraph

states.

--
dt

Questions about Derby/Java DB? Please visit
http://db.apache.org/derby/derby_mail.html
 >> Stay informed about: Global temporary tables in Java DB -- Apache Derby