“ORA-01008：并非所有变量都绑定”错误 [英] "ORA-01008: not all variables bound" error

问题描述

我使用以下方法通过使用jdbc来计算工资单，但是ORA-01008：并非所有变量绑定错误都没有删除。

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

请知道吗？

我使用以下代码

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

推荐答案

你的问题在这里：

rs2 = pstmt.executeQuery(q2);

你告诉 PreparedStatement 执行SQL q2 ，而不是执行先前准备的SQL。这应该是：

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

rs2 = pstmt.executeQuery();

这是一个相当常见的错误，主要是由于的错误类设计造成的java.sql.Statement 及其子类型。

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

正如@RMT指出的那样，你在这里犯了同样的错误：

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

rs1 = pstmt.executeQuery(q1);

这并不重要，因为 q1中没有占位符，因此SQL按原样执行。但这仍然是错误的。

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

最后，你应该考虑在第一个<$ c $上调用 close（） c> PreparedStatement ，然后将 pstmt 变量重新分配给另一个变量。如果你不这样做，你就有可能发生泄密。

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.

这篇关于“ORA-01008：并非所有变量都绑定”错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！