JDBC: Retaining multiple result sets from a query made in a loop

Hi

I'm planning a small Java app using JDBC and have question about making
multiple result sets in a loop.

My program will make a query once and then use one of the fields from
each row in the result set as an argument in a second query. It would
be nice to have a separate result set from each of the secondary
queries, but so far I see no easy way to do it.

In psuedo code the functionality would go something ike this:

Make a single query and get the results in result set RS1
While (there are unprocessed rows in RS1)
{
get a field from the current row in RS1
use the field to formulate a different query QUERY
ResultSet RS2 = statement.executeQuery(QUERY)
next row from RS1
}
Later on, maybe in a different method, or even a different class,
further process the result sets

This will not work since each trip through the loop destroys the
previous content of RS2. Ideally there should be a way to allocate
(new) a result set on the heap each trip through the loop, but I don't
see it.

I've got three alternatives in mind, in case there's no other way:

1. Process in line. Do all processing of each RS2's content right
inside the while loop. This way RS2 being destroyed next ime through
doesn't matter.

2. Formulate a set of JDBC batch statements and run them all at once
(assuming my Oracle database is compatible with this....)

3. Allocate a row set on the heap each time through the loop and copy
the result set to the new row set. Access the row sets by storing their
references in a ListArray that can be passed around at will.

To me the best thing would be a way to new a record set but directly on
the heap but I don't see how to do it.

I'm new to Java and JDBC and I'm sure problems like this have been
solved many times already.

Is there an accepted idiom in Java/JDBC for doing this sort of thing?

TIA,

Lenny Wintfeld

.

Relevant Pages

  • Re: LOOP WITHIN A LOOP
    ... Assuming you can define a set of forbidden values, a Query is probably the easiest way to go. ... But your criteria could be trickier than just an easily listed set. ... Public Function DataScore(_ ... The reason I asked the loop within a loop question was so that I can loop through all of the fields for each record anh have a running score. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: fiter records in class module
    ... You can run a temporary parameter query from a module. ... allows you to keep changing criteria in a loop. ... For help with creating an SQL string for your database, ... ' See if recordset contains records: ...
    (microsoft.public.access.modulesdaovba)
  • RE: Exporting to Excel
    ... The OpenQuery call in the Loop seems to be critical. ... Static Function GetUserIdAs String ... 'Open the specific query with the data to be exported ... You can use this to drive a loop that will export a worksheet per userid. ...
    (microsoft.public.access.externaldata)
  • Re: why excute query through JDBC much slow than query analyzer?
    ... why excute query through JDBC much slow than query analyzer? ... The SendStringParametersAsUnicode is a connection string attribute that is ... If you capture the Execution Plan in a SQL Profiler trace while executing ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Converting from Access to SQL2k via Excel- HELP!!!!!
    ... Then create a new query and paste the following into its SQL ... > In your database you'd need to start with a query that ... The function that you pointed me to allows me to concatenate all ... The problem that I have is that I need to put it into a loop ...
    (microsoft.public.access.conversion)
Loading