java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2)

Question

@WebMethod(operationName = "SearchOR")

  public  SearchOR getSearchOR (@WebParam(name = "comp")
  String comp, @WebParam(name = "name")
 String name) {
            //TODO write your implementation code here:
     SearchOR ack = null;

    try{
     String simpleProc = "{ call getuser_info_or(?,?)}";
        CallableStatement cs = con.prepareCall(simpleProc);
        cs.setString(1, comp);
        cs.setString(2, name);
        **ResultSet rs = cs.executeQuery();**


      while (rs.next()) {

           ve.add(rs.getString(1));
           ve.add(rs.getString(2));

        }}catch ( Exception e) {
        e.printStackTrace();
        System.out.print(e);
    }
    return ack;
}

I am getting error at portion i have made bold.It is pointing to that location.My Query is here:

DELIMITER $$

DROP PROCEDURE IF EXISTS .`getuser_info_or$$ # MySQL returned an empty result set (i.e. zero rows).`

CREATE PROCEDURE .getuser_info_or``

( IN comp VARCHAR(100), IN name VARCHAR(100), OUT Login VARCHAR(100), OUT email VARCHAR(100) )

BEGIN SELECT sLogin, sEmail INTO Login, email FROM ad_user WHERE company = comp OR sName=name; END $$

# MySQL returned an empty result set (i.e. zero rows).

DELIMITER ;

Answer 1

I believe your problem is your call does not match the method definition. In your definition you have two parameters: IN comp, IN name, OUT Login, OUT email. So your call should be:

String simpleProc = "{ call getuser_info_or(?, ?, ?, ?)}";

And from the javadocs:

If used, the result parameter must be registered as an OUT parameter.

So you would need to add those registrations:

cs.registerOutParameter(3, Types.VARCHAR);
cs.registerOutParameter(4, Types.VARCHAR);

The stored procedure as defined does not create a resultset for the retrieved data, but rather places it in the out parameters. To fetch them you will need to access them via the callable statement:

String login = cs.getString(3);
String email = cs.getString(4);