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

CJ <spambox1@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 visit
http://db.apache.org/derby/derby_mail.html
.