Results 1 to 2 of 2
  1. #1
    sabith79 is offline Member
    Join Date
    Nov 2011
    Posts
    2
    Rep Power
    0

    Default problem when inserting a row with prepared Statement

    HI everyone,

    I'm new to Java and I am trying to write a program that selects employees from the database, calculates their average salary and then inserts all new lines to a newly created table in the database.
    I am having issues with inserting the tables.
    I am able to print to the screen all rows with the employees and their calculation correctly, I got the message that the lines were inserted but when queriying the table, it's empty.

    I'm posting the code:
    Java Code:
       stmt_for_insert = conn.createStatement();
       System.out.println("******************************************************");
       System.out.println("Inserting records into table .");
       System.out.println("EMP_No: "+ "\t" + "EMP_NAME: " + "\t\t" + "DEPT: " + "\t\t" + "Sal: " + "\t\t" + "Comm: " + "\t" + " Yearly_SAL: " );
       
     //  stmt=conn.createStatement();
     
       
       int x_emp_no=0, x_dept_no=0, x_sal=0, x_comm=0, x_yearly_sal_calc=0;
       String x_emp_name="AA";
       String x_dept_name="AA";
       ResultSet rs_emp = stmt.executeQuery("SELECT empno FROM emp ORDER BY sal ASC");
       ResultSet rs_calculate = stmt_for_insert.executeQuery("select e.empno, e.ename, e.deptno, e.sal, nvl(e.comm,0), d.dname from emp e, dept d where d.deptno = e.deptno(+) and e.deptno =" + dept_num+"ORDER BY empno ASC" );
    //dept_num is a variable taken from the keyboard as an input
    //   while ( rs_emp.next() ) { // Loop through the result set
      // x_emp_no = rs_emp.getInt(1); 
       while(rs_calculate.next())
       {
      // System.out.println("emp number is"+x_emp_no);
       x_emp_no = rs_calculate.getInt(1); 
       x_emp_name=rs_calculate.getString(2); // System.out.println("emp name is"+x_emp_name);
       x_dept_no=rs_calculate.getInt(3);  // System.out.println("dept no is"+x_dept_no);
       x_sal=rs_calculate.getInt(4); // System.out.println("sal is  "+x_sal);
       x_comm=rs_calculate.getInt(5); //System.out.println("comm is  "+x_comm);
      // x_dept_name=rs_emp.getString(6); System.out.println("dept name is"+x_dept_name);
      
     //  ResultSet rs_calculate = stmt.executeQuery("SELECT empno, ename FROM dept ORDER BY empno ASC");
      //       System.out.println (count + " rows were inserted");
      //    rs_tables.close();   
     //  while ( rs_calculate.next() ) { // Loop through the result set 
       x_yearly_sal_calc = x_sal*12+x_comm; // System.out.println("YEARLY SAL is  "+x_yearly_sal_calc);
         System.out.println("******************************************************");
         System.out.println( String.format("%5d", x_emp_no) + "\t" + x_emp_name + "\t\t" + String.format("%5d", x_dept_no) + "\t" + "\t\t" + String.format("%5d", x_sal) +"\t\t" + String.format("%5d", x_sal) + "\t" + String.format("%5d", x_yearly_sal_calc));
         System.out.println("******************************************************");
       }  
    
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO table  (EMPNO, ENAME, DEPTNO, SAL, COMM, Y_SAL) values (?, ?, ?, ?, ?, ?)");
    
        pstmt.setInt (1, x_emp_no); // The 1sr ? is for DEPTNO
        pstmt.setString (2, x_emp_name); // The 2nd ? is for emp_NAME
        pstmt.setInt (3, x_dept_no); // The 3rd ? is for dept_no
     //   pstmt.setString (4, x_dept_name); // The 4th ? is for dept_name
        pstmt.setInt (4, x_sal); // The 5th ? is for SAL
        pstmt.setInt (5, x_comm); // The 6th ? is for comm 
        pstmt.setInt (6, x_yearly_sal_calc); // The 6th ? is for yearly_sal 
        pstmt.execute(); 
        pstmt.close();
     //  stmt_create.close();
       
      System.out.println("Records have been inserted.");
      
    //  rs_calculate.close();
    ///   }
     //  rs_emp.close();
       stmt_for_insert.close();
       //  conn.close();
       }
    The output of the screen saying the records were inserted but there's nothing in the table.
    Any help pls?

  2. #2
    sabith79 is offline Member
    Join Date
    Nov 2011
    Posts
    2
    Rep Power
    0

    Default Re: problem when inserting a row with prepared Statement

    Issue solved, thread may be closed.

Similar Threads

  1. Replies: 2
    Last Post: 05-04-2011, 11:56 AM
  2. JDBC Prepared Statement
    By Floetic in forum JDBC
    Replies: 4
    Last Post: 05-21-2009, 12:53 AM
  3. MySQL/JDBC prepared statement problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-12-2009, 12:21 AM
  4. Prblem in Prepared Statement
    By haneeshrawther in forum JDBC
    Replies: 2
    Last Post: 04-25-2008, 10:49 AM
  5. Using Prepared Statement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 10:22 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
  •