CLI0612E Invalid parameter number error when executing a stored procedure

[vikram nalagampalli]

Hello,
I am calling a stored procedure with the following signature. I have included the code as well as the error i am getting. Some one please help me out.

public class Select_OIG_List
{
public static void select_OIG_List ( String[] varExceptionType,String[] varExceptionText, ResultSet[] rs ) throws SQLException, Exception
Code i wrote to execute the stored procedure
private ArrayList listSSN(){
ArrayList list = new ArrayList();

try{

String procName = "IDS.select_OIG_List";
String sql = "CALL " + procName + "(?,?)";
callStmt = getConnection().prepareCall(sql);


// register the output parameters
callStmt.registerOutParameter(1, Types.CHAR);//exception type
callStmt.registerOutParameter(2, Types.CHAR);//exception text


System.out.println("Calling Stored procedure to retreive Result Set ........");
callStmt.execute();

// retrieve the output parameters
String exceptionType = callStmt.getString(3).trim();
String exceptionText = callStmt.getString(4).trim();



rs = callStmt.getResultSet();
if(rs == null){
System.out.println("Result set retreived Null");
System.exit(1);
}
ResultSetMetaData stmtInfo = rs.getMetaData();


if (stmtInfo == null){
System.out.println("MetaData information is null");
}
int noOfColumns = stmtInfo.getColumnCount();

while(rs.next()){
for (int i=1;i<=noOfColumns;i++){
String ssn =rs.getString(i);
list.add(ssn);
}
}
System.out.println("Exception type" + exceptionType);
System.out.println("Exception text" + exceptionText);
}
catch (SQLException sqle) {
sqle.printStackTrace();
}
catch (Exception e) {
e.printStackTrace();
}
return list;
}// End listSSN
/////////////ERROR//////////////////////////////////
COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0612E Invalid parameter nu
mber. SQLSTATE=S1093
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throwParamIndexError(SQLEx
ceptionGenerator.java:629)
at COM.ibm.db2.jdbc.app.DB2CallableStatement.validateParameter(DB2Callab
leStatement.java:1307)
at COM.ibm.db2.jdbc.app.DB2CallableStatement.validateParameter(DB2Callab
leStatement.java:1274)
at COM.ibm.db2.jdbc.app.DB2CallableStatement.getString(DB2CallableStatem
ent.java:447)
at OigMQ.listSSN(OigMQ.java:229)
at OigMQ.main(OigMQ.java:56)

[Piyush Daiya]

Hi,
You should change the following statements:-
// retrieve the output parameters
String exceptionType = callStmt.getString(3).trim();
String exceptionText = callStmt.getString(4).trim();
to
// retrieve the output parameters
String exceptionType = callStmt.getString(1).trim();
String exceptionText = callStmt.getString(2).trim();
HTH,
Piyush

[vikram nalagampalli]

Thanks piyush,
I did not even notice that. it obvioulsy solved my problem.
Thanks
Vikram

Originally posted by Piyush Daiya:
Hi,
You should change the following statements:-
// retrieve the output parameters
String exceptionType = callStmt.getString(3).trim();
String exceptionText = callStmt.getString(4).trim();
to
// retrieve the output parameters
String exceptionType = callStmt.getString(1).trim();
String exceptionText = callStmt.getString(2).trim();
HTH,
Piyush