Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

multiple rows problem

 
sachin dabhade
Ranch Hand
Posts: 73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi all,
i have a problem in fetching multiple rows in servlet.
what i have to do is fetch 10 rows from one table and insert it into another.
i am using resultset.
when i display the content using rs.next(),it displays all the rows but when i execute the query to insert the rows in other table,only the first row is inserted.
please help me to come out of it.
sachin
 
prabhat kumar
Ranch Hand
Posts: 114
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can u show the code?
 
Prabhu Chandrasekaran
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I think you might be using the same statement variable for both selecting and updating the database. Use different statement variables and you will see that there will be no problems. Reply back about the outcome of this try.

Prabhu Chandrasekaran.
 
sachin dabhade
Ranch Hand
Posts: 73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by prabhat kumat:
can u show the code?

================
the code where i am getting problem is as follows
*********
Statement stmt;
ResultSet rs;
String name,id;
String q1="select name,id from cust_info where amount>5000";
stmt.executeQuery(q1);
while (rs.next()){
name=rs.getString(1);
id=rs.getString(2);
out.println(name+" : "+id);
// String q2="insert into cust_info2 values('"+name+"','"+id+"');
// stmt.executeUpdate(q2);
}
the above code displays all the rows been fetched from table cust_info.
But when i remove the comments,only the first row is inserted in the table cust_info2.
what could probably be the reason?

[This message has been edited by sachin dabhade (edited April 24, 2001).]
 
sachin dabhade
Ranch Hand
Posts: 73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by ersin eser:
String q2="insert into cust_info2 values('"+ name + "','"+id+"')";
[This message has been edited by ersin eser (edited April 24, 2001).]

i tried with that as well but only first row is getting inserted
thank you
 
Andrew Shafer
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Where to begin?
In your original code snipet, if you remove the comments, it won't even compile because q2 is not a valid string.
Further, reading your code, it isn't the first row that is getting inserted in to cust_info2, but the last one.
You have one variable each for name and id, then you use a loop to go through the result set, assigning name and id, then printing to the screen. At the end of the loop name and id will have the values of the last row from the result set. That is what will be inserted into the new table.
One solution would be to create name and id as arrays or vectors and then use another loop to execute all the insert statements.
The better solution would be to execute a SQL statement that will copy the rows from one table to another within the database, instead of reading them back in.
You would probably get better answers about JDBC in the JDBC forum.
Happy Coding
 
Yamuna Pattathil
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The code works as expected written like this..differences from above being a second statement object(which wont work otherwise) and formatted insert statements
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic