multiple rows problem

[sachin dabhade]

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]

can u show the code?

[Prabhu Chandrasekaran]

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]

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]

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]

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]

The code works as expected written like this..differences from above being a second statement object(which wont work otherwise) and formatted insert statements

Statement st = conn.createStatement ();
Statement st2 = conn.createStatement ();
String query = "select col1, col2 from table1";
String a, b, insQuery;
ResultSet rs = st.executeQuery(query);
while(rs.next()) {
a = rs.getString(1);
b = rs.getString(2);
insQuery = "insert into table2 values('" + a +"', '" + b + "')";
out.write(insQuery);
st2.executeUpdate(insQuery);
}