java.sql.SQLException: ORA-01008: 并非所有变量都绑定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24434466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
java.sql.SQLException: ORA-01008: not all variables bound
提问by pulp_fiction
I am trying to insert some values into an Oracle Database 10g Express Edition retrieved through a form. Here is the code,
我正在尝试将一些值插入到通过表单检索的 Oracle Database 10g Express Edition 中。这是代码，
package com.cid_org.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//import java.util.Collection;
//import java.util.Iterator;
import java.util.Map;
public class CrimeReportPOJO {
private Map<String,String[]> complaintFormData;
private Connection connection;
private int complaintID=0;
public CrimeReportPOJO(Map<String,String[]> complaintFormData,Connection connection){
this.complaintFormData = complaintFormData;
this.connection = connection;
sendCrimeData();
}
private void sendCrimeData(){
try{
String query_VICTIM_DETAILS ="INSERT INTO VICTIM_DETAILS ("+
"VICTIM_FIRST_NAME,"+
"VICTIM_MIDDLE_NAME,"+
"VICTIM_LAST_NAME,"+
"VICTIM_UID_NO,"+
"VICTIM_AGE,"+
"VICTIM_GENDER,"+
"VICTIM_ADDRESS,"+
"NEAREST_POLICE_CHOWKI,"+
"VICTIM_ZIP_CODE,"+
"VICTIM_PHONE_NO,"+
"VICTIM_EMAIL_ADDRESS,"+
"COMPLAINT_ID)"+
"VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
String query_VICTIMIZER_DETAILS ="INSERT INTO VICTIM_DETAILS ("+
"VICTIMIZER_BUSINESS_NAME,"+
"VICTIMIZER_FIRST_NAME,"+
"VICTIMIZER_MIDDLE_NAME,"+
"VICTIMIZER_LAST_NAME,"+
"VICTIMIZER_GENDER,"+
"VICTIMIZER_ADDRESS,"+
"VICTIMIZER_ZIP_CODE,"+
"VICTIMIZER_PHONE_NO,"+
"VICTIMIZER_EMAIL_ADDRESS,"+
"COMPLAINT_ID)"+
"VALUES(?,?,?,?,?,?,?,?,?,?)";
String query_COMPLAINT_DESCRIPTION ="INSERT INTO COMPLAINT_DESCRIPTION ("+
"COMPLAINT_ID,"+
"COMPLAINT_DESC,"+
"COMPLAINT_TIME_STAMP)"+
"VALUES(?,?,?)";
String query_COMPLAINT_COUNT_DETAILS ="INSERT INTO COMPLAINT_COUNT_DETAILS("+
"VICTIM_UID_NO,"+
"COMPLAINT_COUNT)"+
"VALUES(?,?)";
PreparedStatement ps = connection.prepareStatement(query_VICTIM_DETAILS);
/*Now, lets extract the data to be inserted from the map object
* and call the setString() methods on those values */
/*Collection<String[]> c = complaintFormData.values();
Iterator<String[]> it = c.iterator();
int i=1;
while(it.hasNext()){
String[] s = it.next();
ps.setObject(i, (Object)s[0] );
i++;
}*/
String[] s= complaintFormData.get("personal_first_name");
System.out.println(s[0]);
System.out.println(s);
ps.setString(1,s[0]);
s= complaintFormData.get("personal_middle_name");
ps.setString(2,s[0]);
s= complaintFormData.get("personal_last_name");
ps.setString(3,s[0]);
s= complaintFormData.get("personal_aadhar_card_no");
System.out.println(Integer.parseInt(s[0]));
ps.setInt(4,Integer.parseInt(s[0]) );
s= complaintFormData.get("personal_age");
ps.setInt(5,Integer.parseInt(s[0]));
s= complaintFormData.get("personal_gender");
ps.setString(6,s[0]);
s= complaintFormData.get("personal_address");
ps.setString(7,s[0]);
s= complaintFormData.get("police_chowki");
ps.setString(8,s[0]);
s= complaintFormData.get("personal_zip_code");
ps.setInt(9,Integer.parseInt(s[0]));
s= complaintFormData.get("personal_phone_no");
ps.setInt(10,Integer.parseInt(s[0]));
s= complaintFormData.get("personal_email_id");
ps.setString(11,s[0] );
System.out.println(s[0]);
/*To insert complaint ID into the last column COMPLAINT_ID I am
* calling getComplaintID() method which tells me whether its the
* first time I am inserting into the database or there are already
* complaints registered*/
complaintID = getComplaintID();
System.out.println(complaintID);
if(complaintID==-2){
//First time
ps.setInt(12,1);
}
else{
ps.setInt(12,++complaintID);
}
ps.executeUpdate(query_VICTIM_DETAILS);
}catch(Exception e){
e.printStackTrace(System.out);
System.out.println(e);
}
}
private int getComplaintID(){
String query = "SELECT MAX(COMPLAINT_ID)"+
"FROM COMPLAINT_DESCRIPTION";
try {
PreparedStatement ps = connection.prepareStatement(query);
ResultSet rs = ps.executeQuery();
if(rs.next()){
return rs.getInt(1);
}
else{
/*-2 indicates that the table is empty and the first
**complaint is being registered*/
return -2;
}
} catch (SQLException e) {
e.printStackTrace();
}
/*The syntax compels to return an integer value*/
return -1;
}
}
The problem is that I am getting the following exception:
问题是我收到以下异常：
java.sql.SQLException: ORA-01008: not all variables bound
The stack trace is:
堆栈跟踪是：
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1615)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1580)
at com.cid_org.model.CrimeReportPOJO.sendCrimeData(CrimeReportPOJO.java:130)
at com.cid_org.model.CrimeReportPOJO.<init>(CrimeReportPOJO.java:20)
at com.cid_org.controller.CrimeReportControllerServlet.doPost(CrimeReportControllerServlet.java:52)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:722)
Here is the VICTIM_DETAILS table:
这是 VICTIM_DETAILS 表：
http://i.stack.imgur.com/1TEIK.png
http://i.stack.imgur.com/1TEIK.png
and here is the COMPLAINT_DESCRIPTION table,
这是 COMPLAINT_DESCRIPTION 表，
http://i.stack.imgur.com/qxvPN.png
http://i.stack.imgur.com/qxvPN.png
What I have tried: I inserted the following statement
我尝试过的：我插入了以下语句
System.out.println(complaintID);
after calling the getComplaintID() method and the output that I got on the console matches what is actually already in the table(I have cross checked it) and this indicates that the code reaches there successfully which also indicates that getComplaintID() method successfully executed.
在调用 getComplaintID() 方法之后，我在控制台上得到的输出与表中实际已经存在的内容相匹配（我已经交叉检查了它），这表明代码成功到达那里，这也表明 getComplaintID() 方法成功执行.
Also, I left no value as null in the form and all the values were constraint to the table format.
此外，我在表单中没有留下任何值为 null 的值，并且所有值都受表格式的约束。
采纳答案by headlikearock
Perhaps just try calling executeUpdate() with no parameters. You've already set the query string when you created the PreparedStatement.
也许只是尝试不带参数调用 executeUpdate() 。您在创建 PreparedStatement 时已经设置了查询字符串。
ps.executeUpdate()
