Recently, though, I encountered a variation of this that I hadn't seen before: a statement which may return multiple result sets.
ResultSet rs = conn.createStatement().executeQuery("select * from employee");
System.out.println("Employee: " + rs.getString("name"));
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
select count * from tableon 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, 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:
This is where things get weird, because Statement.execute is a weird beast. Here's the description from the documentation:
statement.execute("exec sp_spaceused 'my-table-name'");
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.
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.
executemethod executes an SQL statement and indicates the form of the first result. You must then use the methods
getUpdateCountto retrieve the result, and
getMoreResultsto move to any subsequent result(s).
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:
int rowCount = stmt.getUpdateCount();
if (rowCount > 0) // this is an update count
// handle the row count ...
if (rowCount == 0) // this is a DDL command or it performed 0 updates
// handle the DDL command case ...
// 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 ...
// process a row from this result set ...
// if we get here, we have fully exhausted the results from stmt.execute()
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!