sp_spaceused and multiple JDBC result sets

I'm quite familiar with using JDBC's statement class to execute a query and return a result set:

ResultSet rs = conn.createStatement().executeQuery("select * from employee");
while (rs.next())
System.out.println("Employee: " + rs.getString("name"));

Recently, though, I encountered a variation of this that I hadn't seen before: a statement which may return multiple result sets.

I was trying to enhance an internal tool which I use for performance benchmarking. This tool connects to a database, and summarizes the database contents. The tool currently:

• Enumerates each of the user tables in the database
• executes select count * from table on the table, to get a count of the number of rows.
• sorts the list of tables by the number of rows, and
• prints out the top ten tables and the number of rows in each

So far, it's been a very useful tool. But I wanted to enhance it, to display the overall disk space used by each table. Unfortunately, this becomes vendor specific, as there doesn't appear to be any standard-to-all-databases way to ask "how much disk space is this table using?"

So, since I'm currently working with Microsoft SQL Server, I wanted to invoke the sp_spaceused system procedure, to get information about the space. So I needed to enhance my tool to do:

statement.execute("exec sp_spaceused 'my-table-name'");

This is where things get weird, because Statement.execute is a weird beast. Here's the description from the documentation:

Executes the given SQL statement, which may return multiple results. In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts. Normally you can ignore this unless you are (1) executing a stored procedure that you know may return multiple results or (2) you are dynamically executing an unknown SQL string.

The execute method executes an SQL statement and indicates the form of the first result. You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).

Guess what? I found myself in one of those "uncommon" situations! sp_spaceused is a rather bizarre stored procedure that may return one result set, or it may return two result sets. It returns a single result set in the case I was using, where I want to get the space used by a single table. It returns two result sets in the case where you pass no arguments to the procedure, in which case it gives information about the overall space used by the entire database.

So what does this mean from a practical point of view? Well, the JDBC documentation sort of hints at the fun:

Because the method execute handles the cases that are out of the ordinary, it is no surprise that retrieving its results requires some special handling.

The documentation goes on for several pages to describe all the various intricacies of different things that a stored procedure might return, then offers a snippet of code to handle the general case:

stmt.execute(string-with-unknown-results);
while (true)
{
  int rowCount = stmt.getUpdateCount();
  if (rowCount > 0) // this is an update count
  {
      // handle the row count ...
      stmt.getMoreResults();
      continue;
  }
  if (rowCount == 0) // this is a DDL command or it performed 0 updates
  {
      // handle the DDL command case ...
      stmt.getMoreResults();
      continue;
  }
  // if we get here, we have either a result set, or no more results
  ResultSet rs = stmt.getResultSet();
  if (rs != null)
  {
      // call rs.getMetaData() to learn about the result set "shape"
      // handle the result set, if it's the one you want ...
      while (rs.next())
      {
          // process a row from this result set ...
      }
      rs.close();
      stmt.getMoreResults();
      continue;
  }
  // if we get here, we have fully exhausted the results from stmt.execute()
  break;
}
stmt.close();

Other sources have slight variations of this code. This is nightmarish, but having worked my way through it, I think I understand it, and even better, when I coded it up, my calls to sp_spaceused work! I haven't yet encountered a stored procedure which can return a combination of result sets and update counts, but when I do, I'm not going to be quite so confused about how to handle it.

Best of all, now I can start trying to figure out why my database is so big!