Re: Nest SQL queries and result sets?

From:

steve <steve@aol.com>

Newsgroups:

comp.lang.java.help

Date:

Sat, 5 Aug 2006 14:41:44 +0800

Message-ID:

<eb1eir02272@news4.newsguy.com>

On Fri, 28 Jul 2006 06:02:34 +0800, Mo wrote
(in article <1154037754.863609.184420@s13g2000cwa.googlegroups.com>):

I'm trying to use a while loop to loop through a result set, and inside
that results set call another query. Like....

database_conn1 = new DBConnection(url, user, pass);
database_conn2 = new DBConnection(url, user, pass);

ResultSet records_conn1 = database_conn1.executeQuery(sqlstatment01);

while (records_conn1.next()) {

          records1_conn.getString(1);

          ResultSet records_conn2 =
database_conn2.executeQuery(sqlstatment02);

          while (records_conn2.next()) {
                  records2_conn.getString(1);
          }

}

(I have try and catch around the while statements, just trying to keep
the code simple)

What happens is the outside result set gets the first record, the
inside record gets all of it's results and then it gives me a null
pointer exception. I'm not sure if it is because the inside result set
closes down everything or what. Is this even possible, or is there a
better way to do this?

you cannot, but what you can do is return a subquery of the query within the
loop.

you could for example do this:

select a,b, function(xy) innerset from atable,

where function is a subquery.

when you recover the data in java the query returns a resultset in a
resultset.

steve