You can also realize a performance gain from Oracle if you create the second
query statement only once using a bind variable (ID) and change ID through
your loop.
-----Original Message-----
From: Srinivas K. R. [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 26, 2000 10:11 PM
To: [EMAIL PROTECTED]
Subject: Re: Multiple/Nested Resultsets
We too had the same problem.
So we decided to use to separate statement object for different resultset.
It works fine with out any problem.
----- Original Message -----
From: Mark Weidner <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, December 27, 2000 3:22 AM
Subject: Multiple/Nested Resultsets
> Hi Everyone,
>
> I'm looking to use nested resultsets to retrieve data. Basically,
I
> need to retrieve data based on certain values retrieved by another
> resultset. The following code is a much simplified version of what I'd
like
> to do. The real SQL statements are much more complex, and it would be a
> huge performance hit to attempt to retrieve all the data in one ResultSet.
> It is my understanding that executing a query on an existing statement
> deletes the references to any previous resultset. Therefore, copying the
> data from a first resultset to an object would work (Vector/Hashtable?),
but
> there would be serious performance implications there as well. Any ideas?
> (I apologize....I come from the ASP world)
>
> Thanks.
>
> -Mark
>
> ---------------
>
> file://Connection con has already been created
>
> Statement stmt = con.createStatement();
> ResultSet rsQuestions = stmt.executeQuery("SELECT questionID FROM
> Questions");
>
> file://Loop through questions from first resultset
> while (rsQuestions.next()) {
>
> int ID = rsQuestions.getInt("questionID");
>
> file://Lookup results from second resultset which uses paramaters
from
> the first resultset
> ResultSet rsText = stmt.executeQuery("SELECT questionText FROM
Text
> WHERE questionID=" + ID);
> String sText = rsText.getString("questionText");
> out.println("Question ID: " + ID + " Question Text: " + sText)
> }
>
>
___________________________________________________________________________
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources: http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
>
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
