Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
  • Piet Souris
  • Frits Walraven
  • Carey Brown

A ResultSet within a Result Set

Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi. I am new to JDBC programming, and am running into what is probably a very basic problem. Bascially, what I am trying to do is first run 1 query on the database. Then, while iterating through the resultset, I want to use what's in that first result set to run another query on the database. What is happening is that my code is only running through the first resultset once (I know for sure that there are about 1500 results in it). If I comment out the code in the "while(" loop that runs the second query, the code loops through just fine. Obviously I am not doing the second query properly. I have tried re-using the same connection object, creating a new connection object, etc. and I can't get it right. Please help!. Relevant code below:
Connection connection= null;
ResultSet rs= null;
int count=0;
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
connection=DriverManager.getConnection("jdbc racle:thin:@", "test", "test");
Statement stmt = connection.createStatement();
//first get all yr/make/model/trim combos out of db:
String selectQuery = "select mod_year, mod_mak_make, mod_name, name from carsprod.v_style_info " +
"where mod_year>2000";
rs = stmt.executeQuery(selectQuery);
String baseYear= rs.getString(1);
String baseMake= rs.getString(2);
String baseModel= rs.getString(3);
String baseTrim= rs.getString(4);
StringBuffer path= new StringBuffer("http://blah.blah/";);
Connection conn=null;
conn= DriverManager.getConnection("jdbc racle:thin:@", "test", "test");
Statement st = conn.createStatement();
String sql=" SELECT style_mod_mak_make_to AS make, style_mod_midyear_to AS midyear, style_mod_name_to AS model, style_mod_year_to AS year, style_name_to "+
" FROM carsprod.style_comparisons_t " +
" WHERE style_mod_year = "+baseYear +
" AND style_mod_midyear_from = 1 " +
" AND style_mod_mak_make = '"+baseMake+"' " +
" AND style_mod_name = '"+baseModel+"' " +
" AND style_name = '"+baseTrim+"' " +
" GROUP BY style_mod_mak_make_to, style_mod_midyear_to, style_mod_name_to, style_mod_year_to, style_name_to ";
ResultSet r = stmt.executeQuery(sql);
catch(Exception e)
System.out.println("Exception in inner method: "+e);
if (conn != null){
try {conn.close();}
catch (SQLException e){
As I mentioned above, I have tried re-using the same connection object in the inner loop that i created in the outer loop, but that didn't work either. Any advice is appreciated!

Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When you call executeQuery() for a given instance of Statement, any existing ResultSet that's currently open will be closed. In other words, you can only have one ResultSet open at any given time for a single instance of Statement. To fix your code, you'll need to create a second Statement and use that one for the "inner" query so that the ResultSet generated by the "outer" query won't be closed.
Brett Spell
Author, Professional Java Programming
    Bookmark Topic Watch Topic
  • New Topic