Expecting multiple ResultSets, but only get one

Question

I have a stored procedure which returns multiple result sets as follows,

  create proc test as
  begin
  select '1'
  select a,b into #temp from TABLE1
  select * from #temp
  select '2'
  select 'Done'
  end

And my java call is,

 CallableStatement stmt = null;
 String procString= "EXEC test";
 stmt = conn.prepareCall(procString);
 boolean results = stmt.execute();
 System.out.println(results);
 do {
      if(results) {
         rs = stmt.getResultSet();
         while (rs.next()) {
          System.out.println(rs.getString(1) + ", " );
         }
         rs.close();
    }
   System.out.println();
   results = stmt.getMoreResults();
   System.out.println("results - "+results);
  } while(results);

So as per the above snippet, the output should have all the 4 selects. But I just get the first select and nothing else. I just removed the second select which does insert into temp table, after which I get all the 4 selects.

Why does this happen?

Answer 1

You are misinterpreting the meaning of the boolean returned by getMoreResults:

There are no more results when the following is true:

// stmt is a Statement object
((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

Returns:
true if the next result is a ResultSet object; false if it is an update count or there are no more results

As you haven't got a SET NOCOUNT ON in your stored procedure, the SELECT ... INTO ... will generate an update count. This will be returned after the first select. Only when getMoreResults returns false and getUpdateCount returns -1 can you be sure that there are no more results.

Your current code will exit the do .. while as soon as getMoreResults has returned false. You either need to add SET NOCOUNT ON to your stored procedure, or process multiple results taking into account the update counts.

To correctly process multiple results with update counts you need to do something like:

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

I copied the above from my answer to another question, it is similar but not exactly the same. The rest of that answer might provide some more details: Java SQL: Statement.hasResultSet()?