Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Reusing Statement Object

 
Vineela Devi
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
  • 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 ]
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • 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
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • 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
  • 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
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • 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 ]
 
Michael Duffy
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic