"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();
              }
            }
} 

Exorcist

At what point are you getting this exception? – Monteverdi

Is attendance somehow linked to employee? Otherwise the select count(att_status) for each e_id does not make sense (as it will never change) – Ditter

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.

Tritanopia

Problem have solved.Thanks very much for your time.But i am confused that why i am not getting exception in this line of code rs1 = pstmt.executeQuery(q1); – Exorcist

@Exorcist - rs1 doesn't throw exceptions because q1 doesn't have any bind variables – Nearby

+1, good catch. Retrieving select count(att_status) inside the loop is also not necessary as it does not depend on the e_id (at least not in the example. – Ditter

I feel silly doing this to. I blame years of being forced to use ORM, even though plain old JDBC is superior as far as I am concerned. – Transcend

Answer 2

One reason might be that you cannot re-use the instance of pstmt like that. You have to use a separate PreparedStatement instance in each level of the loop.

Are you aware that this can be done with just a single statement as well?

Edit:
Assuming there is a relation between employee and attendance, something like this would return the sum in a single request:

select sum( (e_salary / 22) * att_count + e_house_rent + e_conv_allow )
from (
    select emp.e_salary
           emp.e_house_rent,
           emp.e_conv_allow, 
           (select count(att.att_status) from attendance att where att.e_id = mp.e_id) s att_count
    from employee emp
) t 

If indeed attendance is not linked to employee, just leave out the where clause in the nested select.

Ditter

No i am not aware that how it can be done using single prepared statement.Kindly tell me that how can i do that by using single prepared statement? – Exorcist

Answer 3

                            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).

Nearby

Answer 4

UPDATE TESTCP SET CP_KEY2 =?, CP_DESC =?, CP_MAKER =?, CP_MAKER_DT =SYSDATE, CP_STATUS ='M' WHERE CP_LANGUAGE = ? AND CP_ENG_CODE = ? AND CP_KEY1 =? AND CP_LANGUAGE =?

In the above query we have 7 in parameter but if in your java code PreparedStatement you have set only 6 parameter values .

That time also this error will occur.

Guv