Program works with HSQLDB but gives "the object is already closed" with H2

[Aivar]

Hi,

H2 (ver. 2011-03-14) gives me "the object is already closed" exception
for a call to ResultSet#next().

I understood from previous messages about this post, that this could
be caused by keeping too many JDBC objects alive at the same time. At
the time of this exception I have 23 PreparedStatement's and 2
ResultSet's (at least these are what I know about).

I tried to increase limits by running this before loading the driver:
System.setProperty("h2.serverCachedObjects", "20000");

but I didn't see any effect.

When I run same program with HSQLDB, then everything goes fine.

Is there something I could do to track down the problem? Can I somehow
ask from Connection, how many this kind of objects are alive?

greetings,
Aivar

[Thomas Mueller]

Hi,

> I tried to increase limits by running this before loading the driver:
>  System.setProperty("h2.serverCachedObjects", "20000");

You need to set this property on the server. For example, if you start
the server using the command line, use:

java -Dh2.serverCachedObjects=20000 ...

20000 is probably too much, but let's try. If this is really the
problem then I would like to find out why it fails for you. Could you
please use a higher log level (append ;TRACE_LEVEL_FILE=3 to the
database URL) and post or send me the *.trace.db files from both the
server and the client(s)?

Regards,
Thomas

[Aivar]

I'm using the embedded version. Here's how I create the connection:

System.setProperty("h2.serverCachedObjects", "20000");

Class.forName("org.h2.Driver");
String path =
AlvorCachePlugin.getDefault().getStateLocation().append("/
cache_h2").toPortableString();
String url = "jdbc:h2:" + path + ";TRACE_LEVEL_FILE=3";
return DriverManager.getConnection(url, "SA", "");

The shortest trace I could create while still reproducing the
exception is 288KB, so I uploaded it here:
http://aivarannamaa.ee/cache_h2.trace.zip

I counted again the maximum number of open ResultSet-s, and can now
confidently say that it's 4 (when running the same program that
produced the trace, but using HSQLDB). In addition there are 23 open
PreparedStatement-s and 1 Connection. I need several open ResultSet-s
for retrieving recursive data structures.

regards,
Aivar

On Mar 23, 9:33 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

According to the .trace.db file, this isn't a bug in H2, but actually
a problem in the application. It works for HSQLDB because HSQLDB
doesn't follow the JDBC specification in this case. What happens in H2
is: the application tries to read from a result set that is already
closed, because the the statement that produced the result set was
re-run. This doesn't fail with HSQLDB because it doesn't close the old
result set, which is a violation of the JDBC specification. Test case:

stat.execute("create table test(id int)");
stat.execute("insert into test values(1)");
PreparedStatement prep = conn.prepareStatement("select * from test");
ResultSet rs1 = prep.executeQuery();
prep.executeQuery();
rs1.next();

According to my test, the last line (rs1.next()) fails for H2,
PostgreSQL, MySQL, and Apache Derby (and fail with all databases that
follow the JDBC specification in this regard). It works for HSQLDB.

I found out this is the problem using the .trace.db file:

line 3859: ResultSet rs147 = prep34.executeQuery();
line 3940: ResultSet rs149 = prep34.executeQuery();
line 3952: rs147.next();

The relevant text in the JDBC specification is in the ResultSet class
level Javadoc, "A ResultSet object is automatically closed when the
Statement object that generated it is closed, re-executed, or used to
retrieve the next result from a sequence of multiple results."

By the way, h2.serverCachedObjects only applies to the TCP server (as
documented), but you are using an embedded database, so
h2.serverCachedObjects can't possibly help.

Regards,
Thomas

[Aivar]

Thanks for the explanation!

Did I understood correcty that when I need to use 2 ResultSet-s
(originating from same SELECT query) simultaneously, then I need to
prepare 2 different PreparedStatement-s ?

Aivar


On Mar 25, 9:52 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Aivar]

Cool, I got it working! :)

Just had to get rid of my manual PreparedStatement pooling. I just
didn't know about JDBC statement pooling :)

Thanks again for the help!

Aivar.

[Maaartin]

On Mar 28, 10:17 am, Aivar <aivar.anna...@gmail.com> wrote:
> Did I understood correcty that when I need to use 2 ResultSet-s
> (originating from same SELECT query) simultaneously, then I need to
> prepare 2 different PreparedStatement-s ?

Yes.

On Mar 28, 11:22 am, Aivar <aivar.anna...@gmail.com> wrote:
> Cool, I got it working! :)
>
> Just had to get rid of my manual PreparedStatement pooling. I just
> didn't know about JDBC statement pooling :)

Neither I did. Is there something like this? IMHO, it's just that "A

ResultSet object is automatically closed when the Statement object

that generated it is closed, re-executed, or...".

[Aivar]

> > Just had to get rid of my manual PreparedStatement pooling. I just
> > didn't know about JDBC statement pooling :)
>
> Neither I did. Is there something like this? IMHO, it's just that "A
> ResultSet object is automatically closed when the Statement object
> that generated it is closed, re-executed, or...".

I read about it from here:
http://www.ibm.com/developerworks/java/library/j-jdbcnew/
Previously I was caching PreparedStatements for reuse The problem was
that sometimes I was reusing a PreparedStatement when its ResultSet
was not finished. Turns out that JDBC does similar kind of caching
under the hood, so that when I do several times:
prepareStatement(sql)
then only first time the query needs to be is compiled.

Aivar

[Maaartin]

On Mar 28, 6:14 pm, Aivar <aivar.anna...@gmail.com> wrote:
> > > Just had to get rid of my manual PreparedStatement pooling. I just
> > > didn't know about JDBC statement pooling :)
>
> > Neither I did. Is there something like this? IMHO, it's just that "A
> > ResultSet object is automatically closed when the Statement object
> > that generated it is closed, re-executed, or...".
>
> I read about it from here:http://www.ibm.com/developerworks/java/library/j-jdbcnew/
> Previously I was caching PreparedStatements for reuse The problem was
> that sometimes I was reusing a PreparedStatement when its ResultSet
> was not finished. Turns out that JDBC does similar kind of caching
> under the hood, so that when I do several times:

That's funny news, actually 10 years old, nonetheless new for me. And
quite strange. I don't get it; it's JDBC 3.0, but doesn't it mean you
need also a JDBC 3.0 driver in order for that to work?

>   prepareStatement(sql)
> then only first time the query needs to be is compiled.

How did you find out that it actually doesn't get compiled each time?

[Aivar]

> How did you find out that it actually doesn't get compiled each time?

Basically, I used wishful thinking :)
Additionally, after the change, the performance didn't degrade and
Statement#isPoolable returned true with H2, so I concluded that it
must be the case.

A.