File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes how to read different data from one table and compare it Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to read different data from one table and compare it" Watch "how to read different data from one table and compare it" New topic
Author

how to read different data from one table and compare it

xiaopeng shi
Greenhorn

Joined: Nov 25, 2002
Posts: 3
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
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
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

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

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:

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
Greenhorn

Joined: Nov 25, 2002
Posts: 3
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

what is the SQLException and where is it occurring?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Greenhorn

Joined: Nov 25, 2002
Posts: 3
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 ]
 
 
subject: how to read different data from one table and compare it
 
Similar Threads
insert problem to db2 tables from jsp page,please help very urgent
String Tokenizer in JSP
Servlet hang when performing to much insert function
Storing The ResultSet
Pass data between managed/ backing beans