 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Max
Guest
|
 Posted: Mon Jul 21, 2003 8:03 pm Post subject: 2 queries (Resultset) |
 |
|
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
|
|
| Back to top |
|
 |
Andy Flowers
Guest
|
| Posted: Mon Jul 21, 2003 9:47 pm Post subject: Re: 2 queries (Resultset) |
|
|
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)";
"Max" <papouasied (AT) yahoo (DOT) com> wrote
| Quote: | 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
|
|
|
| Back to top |
|
|
Avi Abrami
Guest
|
| Posted: Tue Jul 22, 2003 12:12 am Post subject: Re: 2 queries (Resultset) |
|
|
Max wrote:
| Quote: |
In a method, I need to execute 2 queries. The arguments of the second
one are the results of the first 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 ?
|
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.
|
|
| Back to top |
|
|
Heinz Huber
Guest
|
| Posted: Wed Jul 23, 2003 7:40 am Post subject: Re: 2 queries (Resultset) |
|
|
Andy Flowers wrote:
| Quote: | 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();
}
|
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.
| Quote: | 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
|
|
| Back to top |
|
|
Andy Flowers
Guest
|
| Posted: Wed Jul 23, 2003 5:27 pm Post subject: Re: 2 queries (Resultset) |
|
|
<
| Quote: |
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
<
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
