how to read different data from one table and compare it

[xiaopeng shi]

hi, all, I have problem when i try to read different type of data from one table and compare it, it the data is matched, then insert into another table. My sample coding as folowing shows:
String bb1;
String bb2;
String strSQL="select * from testbuy where status='buy'";
ResultSet buy=stmt.executeQuery(strSQL);
String strSQL2="select * from testbuy where status='sell'";
while (buy.next()){
bb1=buy.getString("bb");
sell.first();
String strSQL2="select * from testsell";
sell=stmt.executeQuery(strSQL2);
while (sell.next()){
bb2=sell.getString("bb");
if (bb1.equals(bb2)){
String ss="insert into testmsg values ('bb1')";
rs=stmt.executeQuery(ss);
}
}
}
When I try to run it, it insert nothing, anyone can give me some advice?
Thank you very much!

[Kevin Mukhar]

It appears from the code you posted, that you are using the same Statement object for every operation. This could be the cause of your problem. The
Javadoc states: "Only one ResultSet object per Statement object can be open at any point in time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All statement execute methods implicitly close a statment's current ResultSet object if an open one exists."
Thus, when you execute

sell=stmt.executeQuery(strSQL2);

the ResultSet referenced by 'buy' is closed. And when you execute

rs=stmt.executeQuery(ss);

The ResultSet referenced by 'sell' is closed.
Also, unless you are absolutely sure that the Strings all have the same case, you may want to use equalsIgnoreCase() rather than just equals() for your String compares.
Also, rather than opening two ResultSets and iterating through them both, you might want to try just using a single query. Something like this:

select testbuy.*, testsell.* from testbuy, testsell where testbuy.status='buy' and testbuy.bb = testsell.bb

This query gets all the rows from testbuy where status is buy and joins them to all the rows from testsell where the two fields are equal. Thus in a single query, you get all the data that your code needed two queries to get. Now all you need is a single loop to go through each row, retrieve the appropriate data, and insert that data into a new table (remember to use a different Statement object).
[ November 25, 2002: Message edited by: Kevin Mukhar ]

[xiaopeng shi]

hi, Kevin Mukhar, thank you for your help, but when I put it in my real program, it seems it can read data from database, but can not write data. Could you help me to check it? Thank you very much!!!
The following is my source code
--------------------------------------------------String [][] buycol=new String[99][12];
String [][] sellcol=new String[99][12];
int [] buyagid=new int[99];
int [] sellagid=new int[99];
int [] buylprice=new int[99];
int [] buyhprice=new int[99];
int [] selllprice=new int[99];
int [] sellhprice=new int[99];

String strSQL="select agentbuy.*, agentsell.* from agentbuy, agentsell where agentbuy.iname=agentsell.iname and agentbuy.brand=agentsell.brand and agentbuy.city=agentsell.city and agentbuy.country=agentsell.country and agentbuy.hprice>agentsell.lprice and agentbuy.accinfo=agentsell.accinfo and agentbuy.status='no' and agentsell.status='no'";
ResultSet sellbuy=stmt.executeQuery(strSQL);
//Statement state=cont.createStatement();
int i=1;
while (sellbuy.next() && i<99){
buyagid[i]=sellbuy.getInt "agentbuy.agid");
buycol[i][1]=sellbuy.getString("agentbuy.uname");
buycol[i][2]=sellbuy.getString("agentbuy.iname");
buycol[i][3]=sellbuy.getString("agentbuy.actions");
buycol[i][4]=sellbuy.getString("agentbuy.brand");
buycol[i][5]=sellbuy.getString("agentbuy.city");
buycol[i][6]=sellbuy.getString("agentbuy.country");
buylprice[i]=sellbuy.getInt("agentbuy.lprice");
buyhprice[i]=sellbuy.getInt("agentbuy.hprice");
buycol[i][7]=sellbuy.getString("agentbuy.accinfo");

sellagid[i]=sellbuy.getInt("agentsell.agid");
sellcol[i][1]=sellbuy.getString("agentsell.uname");
sellcol[i][2]=sellbuy.getString("agentsell.iname");
sellcol[i][3]=sellbuy.getString("agentsell.actions");
sellcol[i][4]=sellbuy.getString("agentsell.brand");
sellcol[i][5]=sellbuy.getString("agentsell.city");
sellcol[i][6]=sellbuy.getString("agentsell.country");
selllprice[i]=sellbuy.getInt("agentsell.lprice");
sellhprice[i]=sellbuy.getInt("agentsell.hprice");
sellcol[i][7]=sellbuy.getString("agentsell.accinfo");
//System.out.println("sellcol[i][5]" + sellcol[i][5]);
i++;
}
for (int j=1; j<99; j++) {
String idsql="select msgid from numagid";
ResultSet id=stmt.executeQuery(idsql);
int msgid1=id.getInt("msgid");
int msgid2=msgid1+1;

String sellsql="insert into msg values ('"+ msgid2 +"','"+ buyagid[j] +"','"+ sellcol[j][1] +"','"+ buycol[j][2] +"','"+ buycol[j][1] +"','buy','"+ buycol[j][4] +"','"+ buycol[j][5] +"','"+ buycol[j][6] +"','"+ buylprice[j] +"','"+ buyhprice[j] +"')";
ResultSet sell=stmt.executeQuery(sellsql);//insert value

int msgid3=msgid1+2;
String buysql="insert into msg values ('"+ msgid3 +"','"+ sellagid[j] +"','"+ buycol[j][1] +"','"+ sellcol[j][2] +"','"+ sellcol[j][1] +"','sell','"+ sellcol[j][4] +"','"+ sellcol[j][5] +"','"+ sellcol[j][6] +"','"+ selllprice[j] +"','"+ sellhprice[j] +"')";
ResultSet buy=stmt.executeQuery(buysql);

String upagid="update numagid set msgid=msgid+2";
ResultSet update=stmt.executeQuery(upagid);
String upbuy="update agentbuy set status='yes' where agid=buyagid[j]";
ResultSet upbuy1=stmt.executeQuery(upbuy);
String upsell="update agentsell set status='yes' where agid=sellagid[j]";
ResultSet upsell1=stmt.executeQuery(upsell);
}

[Jamie Robertson]

what is the SQLException and where is it occurring?

[Jamie Robertson]

just a quick note;
This won't work:
ResultSet id=stmt.executeQuery(idsql);
int msgid1=id.getInt("msgid");
After executing a query, you need to call rs.next() to move the cursor to the first record, otherwise you will get an error.
Jamie

[xiaopeng shi]

hi,Jamie Robertson,
Thank you for your reply, after I tried many times, I found an error message with is Column not found, when I put a test message after buyagid[i]=sellbuy.getInt("agentbuy.agid");, the test message will not work, is it correct for this line? Am I use the correct get data method?
[ November 29, 2002: Message edited by: xiaopeng shi ]