Results 1 to 6 of 6
  1. #1
    bittu218 is offline Member
    Join Date
    Jun 2010
    Posts
    2
    Rep Power
    0

    Default JDBC | Unable to update multiple records

    In my jdbc java code, there is a strange issue. Below is what I am trying to do:
    1. Fetch all records from table A into a resultset (table A has 85 records)
    2. Insert all the records fetched from table A into table B (I have cloned table B from table A and hence they have the same exact structure)
    3. I am able to traverse through all the rows in the resultset that contains the 85 records from table A

    Issue
    While updating the table B, I see that only the 1st record from table a gets inserted into table B. For some reason all other rows are not inserted. Looks like if I introduce the insert statement in my while loop using which I am traversing through the resultset, it traverses through only the 1st row and just exists. I have this code in the try-catch block but, do not see any exception raised

    Can the gurus suggest, what I am missing here? Below is the code snippet:
    //get a statement from the connection2
    Statement stmt2 = dbConnection2.createStatement();
    //execute the query on stmt2
    ResultSet rs2 = stmt2.executeQuery("SELECT * FROM A");
    String insertSqlStatement ="";

    int i=1;

    try{
    while (rs2.next()){
    insertSqlStatement = "INSERT INTO B (COL1,COL2,COL3) VALUES ('" rs2.getString(1) "','" rs2.getString(2) "'," rs2.getString(3) "')";
    System.out.println(rs2.getString(1) ";" +rs2.getString(2) ";" rs2.getString(3));
    stmt2.executeUpdate(insertSqlStatement);
    System.out.println("Inserted row :" +i);
    i+;
    }
    }
    catch (Exception e){
    System.out.println("I am in update block");
    System.out.println("Exception :" +e.getMessage());
    }
    dbConnection2.commit();

  2. #2
    Norm's Avatar
    Norm is offline Moderator
    Join Date
    Jun 2008
    Location
    Eastern Florida
    Posts
    17,792
    Rep Power
    25

    Default

    This sounds more like a database question. Have you posted it on that forum.

  3. #3
    bittu218 is offline Member
    Join Date
    Jun 2010
    Posts
    2
    Rep Power
    0

    Default

    Hmmm, ok. No I have not. Will do, thx.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    As soon as you do this:
    stmt2.executeUpdate(insertSqlStatement);
    You close any open resources on the original execution of stmt2...which means the
    resultset is closed. Surprised it didn't throw an exception, though.

    I will say, though, that this can be done in a single INSERT statement, with no need to do the SELECT, loop, and individual INSERTs.

  5. #5
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    I would suggest you use SELECT INTO

    this is from my sql website and also this one

    this is from w3schools

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    How is SELECT INTO any different to doing the INSERT equivalent?
    Apart from not being a standard, that is...

Similar Threads

  1. JDBC that multiple methods will use
    By evermore in forum JDBC
    Replies: 3
    Last Post: 03-16-2010, 08:27 AM
  2. Mysql/JDBC update query problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-11-2009, 10:56 PM
  3. unable to update MYSQL with values from jsp page
    By koushika in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 03-27-2008, 02:35 AM
  4. Getting number of records (JDBC 2.0)
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-11-2008, 09:51 AM
  5. Query with 1 record vs. multiple records
    By anderma8 in forum JDBC
    Replies: 5
    Last Post: 07-10-2007, 12:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •