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 resultado.next(); 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.

TIA,
Diana.

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...

Ciao,

René