CJ <spambox1@[EMAIL PROTECTED]
> writes:
> On Dec 12, 7:05 am, Dyreatn...@[EMAIL PROTECTED]
wrote:
>> CJ <spamb...@[EMAIL PROTECTED]
> writes:
>> > I can't seem to use "declare create tem****ary 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 TEM****ARY 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 TEM****ARY 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 TEM****ARY 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 TEM****ARY 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 tem****ary 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 tem****ary table as:
DECLARE GLOBAL TEM****ARY 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
|
