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

On Dec 14, 12:26 pm, Dyreatn...@xxxxxxx wrote:
CJ <spamb...@xxxxxxxxxxxxxx> writes:
On Dec 12, 7:05 am, Dyreatn...@xxxxxxx wrote:
CJ <spamb...@xxxxxxxxxxxxxx> 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
.

Relevant Pages