CODEWITHSUNDEEP
java
user1690973
user1690973

Reputation: 19

Insert Query is inserting only first row from the result set in the database table

I am fetching records from table EmployeeLeave and storing it in a result set. Then I am inserting this result set in a database table but I am able to insert only the first row of the result set instead of all the rows.
How can i insert all the rows of the result set?

r = stmt.executeQuery("select * from EmployeeLeave where EmployeeNo='"+empno+"'");
while(r.next())
{
    for(int i=0;i<=r.getRow();i++)
    {
        int empid = r.getInt(1);
        String leave = r.getString(2);
        r = stmt.executeQuery("insert into ApprovedLeave values('"+empid+"','"+leave+"','True')");
    }
}

Upvotes: 0

Views: 581

Answers (3)

Reimeus
Reimeus

Reputation: 159844

You cannot call executeQuery while iterating through the ResultSet r. This will effectively reset your first ResultSet as well as your Statement, resulting in only one insert being made. To fix, create a new Statement.

For inserting executeUpdate should be used:

stmt2.executeUpdate("insert into ApprovedLeave values('"+empid+"','"+leave+"','True')");

Upvotes: 1

muniro
muniro

Reputation: 21

It may be because r pointing at the last row when you call the second method. Do you need to reset r before the next operation with it?

Upvotes: 0

GreyBeardedGeek
GreyBeardedGeek

Reputation: 30088

When you execute the inner quert for the first time, it closes the result set from the outer query, because you are re-using the same jdbc statement. For this to work, you'll need to use two different statement object instances.

However, there's really no reason to pull all of that data back to the application, just to send it back to the database. You could just do something like (syntax may vary slightly depending on database vendor):

"insert into ApprovedLeave select EmployeeNo, leave, 'True' from EmployeeLeave where EmployeeNo = '" + empno + "'"

And even better yet would be a PreparedStatement with:

"insert into ApprovedLeave select EmployeeNo, leave, 'True' from EmployeeLeave where EmployeeNo = ?"

where you then bind your empno variable to the statement

Upvotes: 1

Related Questions