[SOLVED] CallableStatement getResultSet returns null when using output parameter

CallableStatement getResultSet returns null when using output parameter

I'm getting some strange results when trying to retrieve multiple result sets from my stored procedure. The stored proc works fine if I execute directly from SQL server management studio.

myProc= conn.prepareCall("{? = call abc.foo(?, ?, ?, ?, ?, ?, ?, ?, ?)}");   
        myProc.registerOutParameter(1, java.sql.Types.INTEGER);
        myProc.setString(2, lookup.getEmailAddress());
        //...other parameters
        boolean isResultSet = myProc.execute(); //returns true
        dbReturnVal = myProc.getInt(1); //correctly retrieves return code
        ResultSet rs = myProc.getResultSet(); //returns NULL
        while (rs.next()) { //exception

This is confusing because of the javadoc for execute and getResultSet :

execute: Returns true if the first result is a ResultSet object; false if the first result is an update count or there is no result

getResultSet: the current result as a ResultSet object or null if the result is an update count or there are no more results

getResultSet should only return a null here if there are no more results right? However, I know from executing the query in SQL server that there are 3 result sets returned.Am I missing something here?

As a test, I ran getMoreResults() to see if I the resultSets were missing for some reason:

 dbReturnVal = myProc.getInt(1); //correctly retrieves return code
 if(myProc.getMoreResults())
        {
        ResultSet rs = myProc.getResultSet(); //STILL returns null
        while (rs.next()) { //exception
...

getMoreResults returns true but yet when I try to get the result set, it still returns a NULL. This isn't quite what I would want anyway because it would skip my result set but I should at least be retrieving the 2nd result set.

What could cause this? Is there something about the way that SQL Server is returning the result sets that is causing the issue or is there something that I'm missing? Any suggestions would be appreciated.

UPDATE

It turns out that this was related to the output parameter I created

myProc= conn.prepareCall("{? = call abc.foo(?, ?, ?, ?, ?, ?, ?, ?, ?)}");   
        myProc.registerOutParameter(1, java.sql.Types.INTEGER);

I removed the parameter and updated the call to

myProc= conn.prepareCall("{call abc.foo(?, ?, ?, ?, ?, ?, ?, ?, ?)}");   
        //myProc.registerOutParameter(1, java.sql.Types.INTEGER);
        //moved up my parameters by 1

And it worked. I get all 3 result sets correctly now. Since I don't care that much about the return code, this is probably fine. In the academic interest, why would the output parameter cause issues with retrieving the result sets?

Solution

I may be wrong, but I am seeing this call usage for first time

"{? = call abc.foo(?, ?, ?, ?, ?, ?, ?, ?, ?)}"

generally we set out parameter in the call inside its braces. As you you did in your updated call

You can check the usage here. http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/

Notice the stored procedure declaration. In stored procedures there is no way of returning a resultset without setting up OUT parameter in the declaration itself

Do you have access to source of the stored procedure to which you are making call to