Error with ResultSet when retrieving data from Stored Procedure


General J2EE: Error with ResultSet when retrieving data from Stored Procedure

  1. Hello,

    it's the first time a write a code to access a database and execute a stored procedure and I'm stuck with a problem when retrieving output data. The compilation is ok, but when I execute the program an exception is thrown by the ResultSet.getInt method.

    My code is:

       final String SQL = "{call myProc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
       CallableStatement stmt = conn.prepareCall (SQL);

       (<several input parameter definition>)

       stmt.registerOutParameter(11, java.sql.Types.INTEGER);

       ResultSet myResult= stmt.executeQuery ();

    Until this point everything is ok. The stored procedure is invoked and all the actions over the database are done.

    However, I need to retrieve the output parameter. I've tried it in two ways and in each case I get a different exception:

       int myIndex= myResult.getInt(1);
    throws the SQLException Invalid column index

       int myIndex= myResult.getInt("varMyIndex");
    varMyIndex is the name of the output variable in the stored procedure, although I also tried with "MyIndex", which is the column name... In both cases it throws the exception
    SQLException ClosedStatement

    I've got no idea about what's going on... :-(

    I tried also executing; before calling the getInt method, just in case the cursor wasn't in the correct position, but I still get the same exception.

    Please, if anybody have some seggestion or any idea about what is the matter, I'd appreciate the comment, as I need this to work as soon as possible.

  2. Hi Diana,

    from my study of the javadoc I found that you have to use the methods of the CallableStatement class to retrieve the registered OUT parameters:

    The following is an excerpt of the javadoc from the java.sql.CallableStatement:

       The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the get methods provided here.

    That means your code should look something like this:

        int myIndex= stmt.getInt(11);

    Since your registered '11' as the index of your OUT parameter, you should use it in the get method.

    I hope that helps, since I'm not an expert in calling stored procedures from Java...