• 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

Reusing Statement Object

 
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

Please tell me whether this is possible or not:

ResultSet rs = stmt.executeQuery("Some Select Query.....");
while(rs.next()){

ResultSet rs1 = stmt.executeQuery("Second Select Query.....");

}


Regards,
Vineela
[ February 01, 2006: Message edited by: Vineela Devi ]
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you tried it?

The answer is generally 'no'. Some JDBC driver implementations may support such a syntax, but it is far from standard. In the 'lucky' case that the first statement return 0 or 1 results (in which case it should be an 'if' statement not a 'while' loop), then it would not produce an error.

Where it is likely to produce an error is not on the second result set creation, but on the second call to rs.next().
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
BTW, please use a meaningful subject line for your posts in the future, since "Help pls" isn't very descriptive. For more information, consult the guide: http://faq.javaranch.com/view?HowToAskQuestionsOnJavaRanch
 
Vineela Devi
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Iam not able to get what the problem will be on second call to rs.next().

What I feel is since I already have a handle to the first ResultSet ,using the same stmt object again may not give any error.

Correct me If Iam wrong.

Regards,
Vineela
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I reckon what you feel and what java does ain't always the same thing. The second call to rs.next() produces the following error:

From the ResultSet API:

"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."

Therefore when you call the second query on the original statement object, you force the first result set to be closed. Any future calls to the first result set, will produce an error. This does not mean you cannot use the same statement with multiple queries and result set, it just means you have to read the results in the order you execute them. Scattered results, such as those in nested events must be read using separate statements.
[ February 01, 2006: Message edited by: Scott Selikoff ]
 
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Vineela Devi:
Hi All,

Please tell me whether this is possible or not:

ResultSet rs = stmt.executeQuery("Some Select Query.....");
while(rs.next()){

ResultSet rs1 = stmt.executeQuery("Second Select Query.....");

}


Regards,
Vineela

[ February 01, 2006: Message edited by: Vineela Devi ]



Not only is it not possible, it's probably not desireable. A good DBA who knows SQL well can probably write a JOIN query that will bring back all the data you need in one round trip with one Statement.

You're committing the "classic" sin of doing an (n+1) query. The query outside the loop returns n rows, and then you perform a network roundtrip evrey time you execute the loop. It'll perform like a pig if n is large, because network latency is the bottleneck.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic