• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Nested ResultSets result in outter ResultSet being closed?

 
Matt Kidd
Ranch Hand
Posts: 267
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So I'm writing a quick app to compare to large data sets. As they are large I put them into an H2 database figuring this would be an easy and quick solution. Headaches later, I've run into a problem where I can query one table but using that the ResultSet to query another ResultSet leads me to the following error:

org.h2.jdbc.JdbcSQLException: The object is already closed

This occurs in the outer while loop of the code below.



Is there something I'm missing about JDBC that is causing it to close one ResultSet as soon I create another ResultSet?
 
Tony Docherty
Bartender
Posts: 3054
59
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your problem maybe because you are reading from the same column more than once and the JDBC driver for your database doesn't support multiple reads. Try assigning the values to a local variable and using the variable to print out the value and create the query. The Java tutorial on result sets says "For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once."
 
Paul Clapham
Sheriff
Posts: 21548
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yeah, what you're missing is where the documentation for the Statement interface says

The API docs wrote:By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.
 
Claude Moore
Ranch Hand
Posts: 828
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good to know.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there any reason you can't just do this in a single SQL statement? Matching, sorting and filtering data is what SQL is for.
 
Matt Kidd
Ranch Hand
Posts: 267
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is why I've loved this place for so many years. Direct response in addition to other ways to think about implementation.

@Paul: Makes perfect sense.

@Chris: In all honesty, complexity, laziness, and this application is being built for this one time use. Plus I'm doing this all in memory at the moment using H2 and I'm trying to mitigate the H2 learning curve without changing to using a file.

Thanks all.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic