• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Nested ResultSets result in outter ResultSet being closed?

 
Ranch Hand
Posts: 267
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Bartender
Posts: 3323
86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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."
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
Bartender
Posts: 1357
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Good to know.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic