ResultSet Thru ResultSet

[Sanjay Deshpande]

I have a result set rs.
Depending on some value from rs i wanna get another resultset rs2with diff query.
How to do this?
I ve code like this

Connection con1=DriverManager.getConnection(URL);
Statement stmt1 = con1.createStatement();
String qry="SELECT PONumber FROM Order";
ResultSet rs= stmt1.executeQuery(qry);
try
{
    while (rs.next())
    {
        Statement stmt2 = con1.createStatement();
        String qry2="SELECT BuyerName FROM Order where PONumber='"+rs.getString("PONumber")+"'";
        ResultSet rs2= stmt2.executeQuery(qry2);
        rs2.next();
        {
            out.println(rs2.getString("BuyerName"));
        }
        rs2.close();
        stmt2.close();
    }
    rs.close();
    con1.close();
}
catch()

IS IT OK?
[ Edited by Dave to format code ]
[ May 19, 2002: Message edited by: David O'Meara ]

[Jamie Robertson]

you might want to change

rs2.next();
{
out.println(rs2.getString("BuyerName")); 
}

to this

if ( rs2.next() )
{
out.println(rs2.getString("BuyerName")); 
}
else
{
//process for no records found
}

Also, if you expect many rs1's, then it might be worth your while to use a prepared statement in the while loop.
Jamie

[Mahesh Mamani]

U may also consider putting creating and closing the Statement calls for the second rs outside the loop. U may check time-difference among the two processes and decide.

MSM