On Dec 14, 12:26 pm, Dyreatn...@[EMAIL PROTECTED]
wrote:
> CJ <spamb...@[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
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
tem****ary 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 "tem****ary table"
is truly tem****ary, but needed so long as the JVM is operating.
However the tem****ary table data should not persist in the DB.
Because of the limitations of "declare global tem****ary table" I will
implement the table another way.
Thanks for your, help.
cj
|
