11 Replies Latest reply: Jun 12, 2008 11:49 AM by masijade RSS

    unable to succesfully execute SQL update statement [JDBC MySQL]

    807601
      I am totally new to JDBC programming using MySQL as the DB.
      I have trouble updating values in the employee_bak table, when there is a key match with employee table.
      import java.sql.*;
      
      public class Employee {
      
      public static Connection con;
           
          public static void main(String[] args) 
          {
              try 
                {
                     Class.forName("com.mysql.jdbc.Driver");
      
                     con = DriverManager.getConnection("jdbc:mysql://localhost/training","root", "");
      
                     java.util.Date util_today = new java.util.Date();
                     java.sql.Date sql_today = new java.sql.Date(util_today.getTime());
      
                     Employee emp = new Employee();
      
                     // Below statement should be executed only once
                     emp.createEmployee("employee");
                     emp.createEmployee("employee_bak");
                     
                     //pre-populating employee table
                     //emp.insertEmployee("employee",1004,"John Smith",5671.34f,sql_today);
                     //emp.insertEmployee("employee",1005,"Jane Doe",5000.86f, sql_today);
                   //emp.insertEmployee("employee",1034,"Mary Jane",6784.57f, sql_today);
                   //emp.insertEmployee("employee",1054,"James Seymour",4675.47f, sql_today);
                   //emp.insertEmployee("employee",1258,"Philip Rogue",5848.33f, sql_today);
      
                     //pre-populating employee_bak table
                     emp.insertEmployee("employee_bak",1004,"Jack Hand",5252.4f,sql_today);
                     emp.insertEmployee("employee_bak",1034,"Mary Beth",5000.42f,sql_today);
                     emp.insertEmployee("employee_bak",1258,"Phil Hersh",3500.65f,sql_today);
                     
                     emp.copyEmployeeTable(); // I can make it to this call, but get stuck inside
                   con.close();
              }
              catch(Exception e) { e.printStackTrace();}
          }
      
          public int createEmployee(String tname1) throws SQLException{
                Statement stmt = con.createStatement();
                int count = stmt.executeUpdate("CREATE TABLE " +tname1+" (emp_id INT, name VARCHAR(20), salary FLOAT, "+"doj DATE)");
                return count;
          }
          public int insertEmployee(String tname1, int emp_id1, String name1, float salary1, java.sql.Date doj1) throws SQLException{int count = 0;
                   PreparedStatement p;
                   p = con.prepareStatement("insert into "+tname1+" (emp_id, name, salary, doj) values(?,?,?,?) ");
                   p.setInt(1, emp_id1);
                   p.setString(2, name1);
                   p.setFloat(3, salary1);
                   p.setDate(4, doj1);
      
                   count = p.executeUpdate();
                   p.close();
                   return count;
          } 
          public int copyEmployeeTable() throws SQLException{
               int count = 0;
                  Statement stat = con.createStatement();
              String sql = "SELECT emp_id, name, salary, doj FROM employee ORDER BY emp_id";
              String sql2 = "SELECT emp_id, name, salary, doj FROM employee_bak ORDER BY emp_id";
                     
              ResultSet rs = stat.executeQuery(sql);
              ResultSet rs2 = stat.executeQuery(sql2);
              
              boolean found;
              while (rs.next()){ //err msg: Operation not allowed after ResultSet closed
      
                   found = false;
                   while (rs2.next()){ //compare the key emp_id, if there is match use an update query to match the values from employee table
                        if (rs.getInt("emp_id") == rs2.getInt("emp_id")){
                             found=true;
                             System.out.println("Found record in backup table\nUpdating record\n\n");
                             //use update method
      count = stat.executeUpdate("Update employee_bak set name="+rs.getString("name")+",salary="+rs.getFloat("salary")+",doj="+rs.getDate("doj")+" WHERE emp_id="+rs.getInt("emp_id")+")");
                             break;
                        }
                   }
              }
              
              }
              rs.close();
              stat.close();
              return count;
          }
      }
      This is the code in its entirety.
      Created 2 tables: employee and employee_bak.
      Successfully pre-populated both tables.
      I call the copyEmployeeTable method, if the keys match from both tables then update employee_bak from employee.
      I get the err msg: Operation not allowed after ResultSet closed.
      Plus, I am not sure if I am doing the executeUpdate properly.
        • 1. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
          masijade
          Read the API documentation. It clearly states that when you execute a query with a Statement, all previous ResultSets associated with that Statement will be automatically closed.

          You need to use separate Statement instances for each of those select queries, and a third for your update queries.
          • 2. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
            807601
            hi guy,
            You must that new statement object in if function. The fetch is in the process of advancing.
                    int count = 0;
                        Statement stat = con.createStatement();
                    Statement stat1 = con.createStatement();
                    String sql = "SELECT emp_id, name, salary, doj FROM employee ORDER BY emp_id";
                    String sql2 = "SELECT emp_id, name, salary, doj FROM employee_bak ORDER BY emp_id";
                           
                    ResultSet rs = stat.executeQuery(sql);
                    ResultSet rs2 = stat.executeQuery(sql2);
                    
                    boolean found;
                    while (rs.next()){ //err msg: Operation not allowed after ResultSet closed
             
                         found = false;
                         while (rs2.next()){ //compare the key emp_id, if there is match use an update query to match the values from employee table
                              if (rs.getInt("emp_id") == rs2.getInt("emp_id")){
                                   found=true;
                                   System.out.println("Found record in backup table\nUpdating record\n\n");
                                   //use update method
            count = stat1.executeUpdate("Update employee_bak set name="+rs.getString("name")+",salary="+rs.getFloat("salary")+",doj="+rs.getDate("doj")+" WHERE emp_id="+rs.getInt("emp_id")+")");
                                   break;
                              }
                         }
                    }
                    
                    }
                    rs.close();
                    stat.close();
                    stat1.close();
                    return count;
            • 3. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
              masijade
              This code is still wrong (and you shouldn't simply be pasting the "answer" code here anyway).

              rs will still be closed as soon as rs2 is created.

              Edit: And, of course, even if you are going to simply be throwing the SQLException, you should still do the rest in a try block with the closing statements in a finally block (there just simply won't be any catch blocks).
              • 4. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
                807601
                I have made further progress, I have now revised the code with the suggestions.
                But now I get a message saying rs2 cannot be resolved.
                import java.sql.*;
                public class Employee {
                
                public static Connection con;
                     
                    public static void main(String[] args) 
                    {
                        try {
                               Class.forName("com.mysql.jdbc.Driver");
                
                               con = DriverManager.getConnection("jdbc:mysql://localhost/training","root", "");
                
                               java.util.Date util_today = new java.util.Date();
                               java.sql.Date sql_today = new java.sql.Date(util_today.getTime());
                
                               Employee emp = new Employee();
                               
                               // Below statement should be executed only once
                               emp.createEmployee("employee");
                               emp.createEmployee("employee_bak");
                
                               //pre-populating employee table
                               emp.insertEmployee("employee",1004,"John Smith",5671.34f,sql_today);
                               emp.insertEmployee("employee",1005,"Jane Doe",5000.86f, sql_today);
                             emp.insertEmployee("employee",1034,"Mary Jane",6784.57f, sql_today);
                            emp.insertEmployee("employee",1054,"James Seymour",4675.47f, sql_today);
                             emp.insertEmployee("employee",1258,"Philip Rogue",5848.33f, sql_today);
                               //pre-populating employee_bak table
                               emp.insertEmployee("employee_bak",1004,"Jack Hand",5252.4f,sql_today);
                               emp.insertEmployee("employee_bak",1034,"Mary Beth",5000.42f,sql_today);
                               emp.insertEmployee("employee_bak",1259,"Phil Hersh",3500.65f,sql_today);
                               
                               emp.copyEmployeeTable();
                
                             con.close();
                        }
                        catch(ClassNotFoundException cnfe){ cnfe.printStackTrace(); }
                        catch(SQLException ioe) { ioe.printStackTrace(); }
                    }
                
                    public int createEmployee(String tname1) throws SQLException
                    {
                          Statement stmt = con.createStatement();
                          int count = stmt.executeUpdate("CREATE TABLE " +tname1+" (emp_id INT, name VARCHAR(25), salary FLOAT, "+"doj DATE)");
                          return count;
                    }
                    public int insertEmployee(String tname1, int emp_id1, String name1, float salary1, java.sql.Date doj1) throws SQLException{
                                                int count = 0;
                             PreparedStatement p;
                             p = con.prepareStatement("insert into "+tname1+" (emp_id, name, salary, doj) values(?,?,?,?) ");
                             p.setInt(1, emp_id1);
                             p.setString(2, name1);
                             p.setFloat(3, salary1);
                             p.setDate(4, doj1);
                
                             count = p.executeUpdate();
                             p.close();
                             return count;
                    } 
                    public int copyEmployeeTable() throws SQLException{
                         int count = 0;
                            Statement stat = con.createStatement();
                            Statement stat1 = con.createStatement();
                            Statement stat2 = con.createStatement();
                        String sql = "SELECT emp_id, name, salary, doj FROM employee ORDER BY emp_id";
                        String sql2 = "SELECT emp_id, name, salary, doj FROM employee_bak ORDER BY emp_id";
                               
                        ResultSet rs = stat.executeQuery(sql);
                        ResultSet rs2 = stat1.executeQuery(sql2);
                        
                        boolean found;
                        while (rs.next()){
                             found = false;
                             while (rs2.next()){ //compare the key emp_id, if there is match use an update query to match the values from employee table
                                  if (rs.getInt("emp_id") == rs2.getInt("emp_id")){
                                       found=true;
                                       System.out.println("Found record in backup table\nUpdating record\n\n");
                                       //use update method
                                       String sql3="Update employee_bak set name='"+rs.getString("name")+"',salary="+rs.getFloat("salary")+" WHERE emp_id="+rs.getInt("emp_id")+"";
                                       count = stat2.executeUpdate(sql3);
                                       break;
                                  }
                             }
                        }        
                        rs.close();
                        rs2.close();
                        stat.close();
                        stat1.close();
                        stat2.close();
                        return count;
                    }
                }
                • 5. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
                  807601
                  Hi,

                  First tell us where is this problem uccering i,e, line number and position.

                  However, I've found a problem with your code
                  boolean found;
                        while (rs.next()){
                               found = false;
                               while (rs2.next()){ //compare the key emp_id, if there is match use an update query to match the values from employee table
                                    if (rs.getInt("emp_id") == rs2.getInt("emp_id")){
                                         found=true;
                                         System.out.println("Found record in backup table\nUpdating record\n\n");
                                         //use update method
                                         String sql3="Update employee_bak set name='"+rs.getString("name")+"',salary="+rs.getFloat("salary")+" WHERE emp_id="+rs.getInt("emp_id")+"";
                                         count = stat2.executeUpdate(sql3);
                                         break;
                                    }
                               }
                          }   
                  If you go through your code provided, you have used two nested while loops. For every record found in rs, inner while loop will
                  run and advances the cursor of rs2 to the last record.

                  Now at this point, your rs2 will be of no worth as you cannot take back you cursor to the first row.

                  To resolve this problem, you should create scrollable resultset.
                  • 6. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
                    masijade
                    Why are you not solving the inner loop using SQL?
                    "SELECT a.emp_id, a.name, a.salary, a.doj, b.emp_id FROM employee a, employee_bak b where a.emp_id = b.emp_id (+) ORDER BY a.emp_id"
                    Now, the value of the last field is null when the record does not exist in employee_bak.

                    Don't use Java to solve a problem that SQL can solve better.

                    Even better would be to simply do the insert directly
                    "INSERT INTO employee_bak (emp_id, name, salary, doj) "
                       + "SELECT emp_id, name, salary, doj FROM employee "
                       + "WHERE emp_id NOT IN (SELECT emp_id FROM employee_bak)"
                    • 7. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
                      796254
                      masijade. wrote:
                      Why are you not solving the inner loop using SQL?
                      "SELECT a.emp_id, a.name, a.salary, a.doj, b.emp_id FROM employee a, employee_bak b where a.emp_id = b.emp_id (+) ORDER BY a.emp_id"
                      Now, the value of the last field is null when the record does not exist in employee_bak.

                      Don't use Java to solve a problem that SQL can solve better.

                      Even better would be to simply do the insert directly
                      "INSERT INTO employee_bak (emp_id, name, salary, doj) "
                      + "SELECT emp_id, name, salary, doj FROM employee "
                      + "WHERE emp_id NOT IN (SELECT emp_id FROM employee_bak)"
                      A MUCH better idea.

                      %
                      • 8. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
                        masijade
                        duffymo wrote:
                        A MUCH better idea.

                        %
                        Yep. Although the first is, at least IMHO, acceptable if you have to do other things (outside of the db) in addition to the insert (and then the insert should be done as a batch). ;-)
                        • 9. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
                          796254
                          masijade. wrote:
                          duffymo wrote:
                          A MUCH better idea.

                          %
                          Yep. Although the first is, at least IMHO, acceptable if you have to do other things (outside of the db) in addition to the insert (and then the insert should be done as a batch). ;-)
                          Agreed. I was thinking of all those people who want to use Java instead of batch when I read your post.

                          %
                          • 10. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
                            masijade
                            duffymo wrote:
                            Agreed. I was thinking of all those people who want to use Java instead of batch when I read your post.

                            %
                            I know. I always find those actions amusing. What is funniest, though, are those people that just won't accept that SQL is the better way.
                            • 11. Re: unable to succesfully execute SQL update statement [JDBC MySQL]
                              807601
                              Thanx, you solved my problem.
                              I changed it so that the resultsets are scrollable.
                              The program now works for me.
                              Aakash wrote:
                              Hi,

                              First tell us where is this problem uccering i,e, line number and position.

                              However, I've found a problem with your code
                              boolean found;
                              while (rs.next()){
                                   found = false;
                                   while (rs2.next()){ //compare the key emp_id, if there is match use an update query to match the values from employee table
                                        if (rs.getInt("emp_id") == rs2.getInt("emp_id")){
                                             found=true;
                                             System.out.println("Found record in backup table\nUpdating record\n\n");
                                             //use update method
                                             String sql3="Update employee_bak set name='"+rs.getString("name")+"',salary="+rs.getFloat("salary")+" WHERE emp_id="+rs.getInt("emp_id")+"";
                                             count = stat2.executeUpdate(sql3);
                                             break;
                                        }
                                   }
                              }   
                              If you go through your code provided, you have used two nested while loops. For every record found in rs, inner while loop will
                              run and advances the cursor of rs2 to the last record.

                              Now at this point, your rs2 will be of no worth as you cannot take back you cursor to the first row.

                              To resolve this problem, you should create scrollable resultset.