"ORA-01008: not all variables bound" error

Question

I am using following method for calculating payroll by using jdbc but "ORA-01008: not all variables bound" error is not removing.

Any idea please?

I am using following code

public double getPayroll(){
            ResultSet rs = null;
            ResultSet rs1 = null;
            ResultSet rs2 = null;

            Connection conn = null;
            PreparedStatement pstmt = null;
            try {
                    conn = getDBConnection();
                    double dailyPay=0,basicPay=0,payroll2=0;
                    int houseRent=0,convAllow=0,noOfPresents=0,empId=0;
                    String q = "select e_id from employee";
                    pstmt = conn.prepareStatement(q);
                    rs = pstmt.executeQuery();
                    while (rs.next()) {
                        empId=rs.getInt(1);
                        String q1 = "select count(att_status) from attendance where att_status='p'";
                        pstmt = conn.prepareStatement(q1);
                        rs1 = pstmt.executeQuery(q1);
                        while(rs1.next()){
                            noOfPresents=rs1.getInt(1);
                            String q2 = "select e_salary,e_house_rent,e_conv_allow from employee where e_id=?";
                            pstmt = conn.prepareStatement(q2);
                            pstmt.setInt(1,empId);
                            rs2 = pstmt.executeQuery(q2);
                            while(rs2.next()){
                                dailyPay=rs2.getInt(1)/22;
                                houseRent=rs2.getInt(2);
                                convAllow=rs2.getInt(3);
                                basicPay=dailyPay*noOfPresents;
                                payroll2+=basicPay+houseRent+convAllow;
                            } 
                        }
                    }
                    return payroll2;
             }catch (Exception e) {
              e.printStackTrace();
              return 0.0;
            } finally {
              try {
                rs.close();
                pstmt.close();
                conn.close();
              } catch (Exception e) {
                e.printStackTrace();
              }
            }
} 

Answer 1

Your problem is here:

rs2 = pstmt.executeQuery(q2);

You're telling the PreparedStatement to execute the SQL q2, rather than executing the SQL previously prepared. This should just be:

rs2 = pstmt.executeQuery();

This is a fairly common mistake, caused mainly by the bad class design of java.sql.Statement and its subtypes.

As @RMT points out, you make the same mistake here:

rs1 = pstmt.executeQuery(q1);

This doesn't matter so much, since there are no placeholders in q1, so the SQL executes as-is. It's still wrong, though.

Lastly, you should consider calling close() on the first PreparedStatement, before re-assigning the pstmt variable to another one. You risk a leak if you don't do that.

Answer 2

                            pstmt = conn.prepareStatement(q2);
                            pstmt.setInt(1,empId);
                            rs2 = pstmt.executeQuery(q2);

You have already created the prepared statement with the query q2 and bound the variable empId to it. if you now invoke pstmt.executeQuery(q2), the variable binding is lost. The JDBC driver probably parses the unbound sql q2 when you execute pstmt.executeQuery(q2).