Java SQL Exception Missing IN or OUT parameter at index:: 1

Question

This seems to be a frequent question around here, but no specific answer. I've never encountered the issue before. The code is here:

public static Reservation retrieveReservation()throws IOException,
   SQLException{
   Reservation testRsv = new Reservation();
   try {
      Connection con = null;

      Class.forName("oracle.jdbc.driver.OracleDriver");
      con=DriverManager.getConnection(
         "jdbc:oracle:thin:@wewe",
         "ewew",
         "sdsds");
      con.setAutoCommit(false);
      try {
         PreparedStatement prepareStatement = con.prepareStatement("SELECT *
         FROM SMD_RESERVATION_INSTANCES WHERE id = ?");
         ResultSet rset = prepareStatement.executeQuery();
         prepareStatement.setString(1, localIDTest);
         prepareStatement.executeUpdate();
         prepareStatement.close();
         con.commit();
         if(rset.next()){
            retrievedID = rset.getString("ID");
            Blob blob = rset.getBlob("RESERVATIONINST");
            status = rset.getString("STATUS");
            long blobLength = blob.length();
            int pos = 1;   // position is 1-based
            int len = 10;
            byte[] bytes = blob.getBytes(pos, len);
            InputStream is = blob.getBinaryStream();
            ObjectInputStream ois = new ObjectInputStream(is);
            testRsv = (Reservation)ois.readObject();
         }

         // System.out.println("Map Size: " + retrievedmap.size());
         rset.close();
         con.close();
      }catch(IOException ioe){
         System.err.print(ioe);
      }
   }catch(ClassNotFoundException cnfe){
      System.err.print(cnfe);
   }
   return testRsv;
}

it produces the following:

java.sql.SQLException: Missing IN or OUT parameter at index:: 1

Any idea why? I've re-checked the variables as parameters that is the ID and its not null and has a value.

KV Prajapati · Accepted Answer · 2011-12-10 06:35:21Z

4

You have to change the order of statement,

PreparedStatement prepareStatement = con.prepareStatement("SELECT * FROM 
            SMD_RESERVATION_INSTANCES WHERE id = ?");
  prepareStatement.setString(1, localIDTest);
  ResultSet rset = prepareStatement.executeQuery();

remove these statement (don't close the connection while you are reading).

prepareStatement.executeUpdate();
prepareStatement.close();
con.commit();

answered Dec 10, 2011 at 6:35

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens Schauder · Accepted Answer · 2011-12-10 06:37:01Z

2

You create a statement requiring one parameter, execute it and only after that you set the parameter:

        PreparedStatement prepareStatement = con.prepareStatement("SELECT * FROM 
        SMD_RESERVATION_INSTANCES WHERE id = ?");
        ResultSet rset = prepareStatement.executeQuery();
        prepareStatement.setString(1, localIDTest);

this should work:

        PreparedStatement prepareStatement = con.prepareStatement("SELECT * FROM 
        SMD_RESERVATION_INSTANCES WHERE id = ?");
        prepareStatement.setString(1, localIDTest);
        ResultSet rset = prepareStatement.executeQuery();

answered Dec 10, 2011 at 6:37

Jens Schauder

82.6k35 gold badges199 silver badges379 bronze badges

Collectives™ on Stack Overflow

Java SQL Exception Missing IN or OUT parameter at index:: 1

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related