Null resultsets when calling Sybase stored procedure through JDBC

Question

I'm calling a Sybase stored procedure that returns multiple resultsets through JDBC. I need to get a specific result set that has a column named "Result" This is my code :

CallableStatement cs = conn.prepareCall(sqlCall);
cs.registerOutParameter(1, Types.VARCHAR);
cs.execute();
ResultSet rs=null;
int count = 1;
boolean flag = true;
while (count < 20000 && flag == true) {
    cs.getMoreResults();
    rs = cs.getResultSet();
    if (rs != null) {
        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        int columnsCount = resultSetMetaData.getColumnCount();
        if (resultSetMetaData.getColumnName(1).equals("Result")) {
            // action code resultset found 
            flag = false;
            // loop on the resultset and add the elements returned to an array list
            while (rs.next()) {
                int x = 1;
                while (x <= columnsCount) {
                   result.add(rs.getString(x));
                   x++;
                }
            }
            result.add(0, cs.getString(1));
        }
    }
    count++;
}

What happens here is that cs.getMoreResults returns a lot of null resultsets till it reaches the target one. I can't use cs.getMoreResults as loop condition because it returns false for null resultsets.

I put a fixed number to end the loop in condition the wanted result set wasn't returned to prevent it from going into infinite loop. It worked fine but I don't think this is right.

I think the null resultsets returned from the assignment in Sybase select @variable = value

Has anyone faced this before?

Answer 1

You are misinterpreting the return value of getMoreResults(). You are also ignoring the return value of execute(), this method returns a boolean indicating the type of the first result:

• true: result is a ResultSet
• false : result is an update count

If the result is true, then you use getResultSet() to retrieve the ResultSet, otherwise getUpdateCount() to retrieve the update count. If the update count is -1 it means there are no more results. Note that the update count will also be -1 when the current result is a ResultSet. It is also good to know that getResultSet() should return null if there are no more results or if the result is an update count (this last condition is why you get so many null values).

Now if you want to retrieve more results, you call getMoreResults() (or its brother accepting an int parameter). The return value of boolean has the same meaning as that of execute(), so false does not mean there are no more results!

There are only no more results if the getMoreResults() returns false and getUpdateCount() returns -1 (as also documented in the Javadoc)

Essentially this means that if you want to correctly process all results you need to do something like below:

boolean result = stmt.execute(...);
while(true) {
    if (result) {
        ResultSet rs = stmt.getResultSet();
        // Do something with resultset ...
    } else {
        int updateCount = stmt.getUpdateCount();
        if (updateCount == -1) {
            // no more results
            break;
        }
        // Do something with update count ...
    }
    result = stmt.getMoreResults();
}

My guess is that you are getting a lot of update counts before you get the actual ResultSet.

I am not really familiar with Sybase, but its cousin SQL Server has the 'annoying' feature to return update counts from stored procedures if you don't explicitly put SET NOCOUNT ON; at the start of the stored procedure.

NOTE: Part of this answer is based on my answer to Execute “sp_msforeachdb” in a Java application