CODEWITHSUNDEEP
javasqloracle-database
Manish Kumar Sharma
Manish Kumar Sharma

Reputation: 13442

java.sql.SQLException: ORA-01008: not all variables bound

I am trying to insert some values into an Oracle Database 10g Express Edition retrieved through a form. Here is the code,

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:

https://i.sstatic.net/1TEIK.png

and here is the COMPLAINT_DESCRIPTION table,

https://i.sstatic.net/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.

Also, I left no value as null in the form and all the values were constraint to the table format.

Upvotes: 1

Views: 17319

Answers (1)

headlikearock
headlikearock

Reputation: 695

Perhaps just try calling executeUpdate() with no parameters. You've already set the query string when you created the PreparedStatement. 

 ps.executeUpdate()

Upvotes: 17

Related Questions