2 queries (Resultset)

Thread view:

Max - 21 Jul 2003 21:03 GMT

hi,

In a method, I need to execute 2 queries. The arguments of the second one
are the results of the 1st one:

String query1 = "Select A from table1"; // A = SQL_CODE
String query1 = "Select Z, Y from table1 where A = code";
PreparedStatement ps = conn.prepareStatement(query1);
PreparedStatement psa = conn.prepareStatement(query2);

String code;

ResultSet rs = ps.executeQuery();
while(rs.next()){
code = rs.getString(SQL_CODE);
}
// output can be several lines

how do i do to pass "code " as a parameter to my 2nd query ?

thanks

Reply to this Message

Andy Flowers - 21 Jul 2003 22:47 GMT

String query1 = "Select A from table1"; // A = SQL_CODE
String query1 = "Select Z, Y from table1 where A = ?"; // note the question
mark
PreparedStatement ps = conn.prepareStatement(query1);
PreparedStatement psa = conn.prepareStatement(query2);

String code;

ResultSet rs = ps.executeQuery();
while(rs.next())
{
code = rs.getString(SQL_CODE);
psa.setString(1,code); // set the value required
psa.executeQuery();
}

better still combine the SQL into one statement wih a sub-query

String query1 = "Select Z, Y from table1 where A in (Select A from table1)";

> hi,
>
[quoted text clipped - 17 lines]
>
> thanks

Reply to this Message

Heinz Huber - 23 Jul 2003 08:40 GMT

> String query1 = "Select A from table1"; // A = SQL_CODE
> String query1 = "Select Z, Y from table1 where A = ?"; // note the question
[quoted text clipped - 11 lines]
> psa.executeQuery();
> }

I wouldn't recommend this without using two different connections.
AFAIK, you can only have 1 open resultset per connection. Therefore, the
second executeQuery invalidates the first resultset and it might stop
working or produce incorrect results.

> better still combine the SQL into one statement wih a sub-query
>
> String query1 = "Select Z, Y from table1 where A in (Select A from table1)";

This is the way to go!

Regards,
Heinz

Reply to this Message

Andy Flowers - 23 Jul 2003 18:27 GMT

<<SNIP>>

> I wouldn't recommend this without using two different connections.
> AFAIK, you can only have 1 open resultset per connection. Therefore, the
> second executeQuery invalidates the first resultset and it might stop
> working or produce incorrect results.

Not true. A Connection has nothing to do with ResultSet, that's the job of
the Statement

A Statement can only have one active result set at any one point in time.

In summary

Connection has 0..many active Statement has 0..1 active ResultSet

<<SNIP

Reply to this Message

Avi Abrami - 22 Jul 2003 01:12 GMT

> In a method, I need to execute 2 queries. The arguments of the second
> one are the results of the first one:
[quoted text clipped - 13 lines]
>
> how do i do to pass "code " as a parameter to my 2nd query ?

Max,
It appears to me that you have several typing mistakes in the code
you posted. I assume that you wish to "SELECT" data from two
different tables. Here is what I think your SQL query should look
like (based on my assumptions):

select Z, Y from table2 where A in (select A from table1)

Good Luck,
Avi.

Reply to this Message

2 queries (Resultset)

Free Magazines