Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

A ResultSet within a Result Set

 
Katie McCann
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(rs.next())" 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;
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
connection=DriverManager.getConnection("jdbc racle:thin:@100.1.1.100:1541:nes", "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);
while(rs.next())
{
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;
try
{
conn= DriverManager.getConnection("jdbc racle:thin:@100.1.1.100:1541:nes", "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);
while(r.next())
{
path.append("&checkbox="+URLEncoder.encode(r.getString(4))+";"+URLEncoder.encode(r.getString(1))+";"+URLEncoder.encode(r.getString(3))+";"+URLEncoder.encode(r.getString(5))+";1");
}
}
catch(Exception e)
{
System.out.println("Exception in inner method: "+e);
}
finally
{
if (conn != null){
try {conn.close();}
catch (SQLException e){
e.printStackTrace();
}
}
}
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!

 
Brett Spell
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Katie,
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic