Adnan Adnan - 5 months ago 25
Java Question

"ORA-01008: not all variables bound" error

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

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.