jdbc-execute-method

execute method:
The execute method should be used only when it is possible that a statement may return more than one ResultSet object, more than one update count, or a combination of ResultSet objects and update counts. These multiple possibilities for results, though rare, are possible when one is executing certain stored procedures or dynamically executing an unknown SQL string (that is, unknown to the application programmer at compile time). For example, a user might execute a stored procedure and that stored procedure could perform an update, then a select, then an update, then a select, and so on. Typically, someone using a stored procedure will know what it returns.

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. For instance, suppose it is known that a procedure returns two result sets. After using the method execute to execute the procedure, one must call the method getResultSet to get the first result set and then the appropriate getXXX methods to retrieve values from it. To get the second result set, one needs to call getMoreResults and then getResultSet a second time. If it is known that a procedure returns two update counts, the method getUpdateCount is called first, followed by getMoreResults and a second call to getUpdateCount.

Those cases where one does not know what will be returned present a more complicated situation. The method execute returns true if the result is a ResultSet object and false if it is a Java int. If it returns an int, that means that the result is either an update count or that the statement executed was a DDL command. The first thing to do after calling the method execute, is to call either getResultSet or getUpdateCount. The method getResultSet is called to get what might be the first of two or more ResultSet objects; the method getUpdateCount is called to get what might be the first of two or more update counts.

When the result of an SQL statement is not a result set, the method getResultSet will return null. This can mean that the result is an update count or that there are no more results. The only way to find out what the null really means in this case is to call the method getUpdateCount, which will return an integer. This integer will be the number of rows affected by the calling statement or -1 to indicate either that the result is a result set or that there are no results. If the method getResultSet has already returned null, which means that the result is not a ResultSet object, then a return value of -1 has to mean that there are no more results. In other words, there are no results (or no more results) when the following is true:

((stmt.getResultSet() == null) && (stmt.getUpdateCount() == -1))

If one has called the method getResultSet and processed the ResultSet object it returned, it is necessary to call the method getMoreResults to see if there is another result set or update count. If getMoreResults returns true, then one needs to again call getResultSet to actually retrieve the next result set. As already stated above, if getResultSet returns null, one has to call getUpdateCount to find out whether null means that the result is an update count or that there are no more results.

When getMoreResults returns false, it means that the SQL statement returned an update count or that there are no more results. So one needs to call the method getUpdateCount to find out which is the case. In this situation, there are no more results when the following is true:

((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

The code below demonstrates one way to be sure that one has accessed all the result sets and update counts generated by a call to the method execute:

stmt.execute(queryStringWithUnknownResults);
while (true) {
int rowCount = stmt.getUpdateCount();
if (rowCount > 0) { // this is an update count
System.out.println("Rows changed = " + count);
stmt.getMoreResults();
continue;
}
if (rowCount == 0) { // DDL command or 0 updates
System.out.println(" No rows changed or statement was DDL
command");
stmt.getMoreResults();
continue;
}

// if we have gotten this far, we have either a result set
// or no more results

ResultSet rs = stmt.getResultSet;
if (rs != null) {
. . . // use metadata to get info about result set columns
while (rs.next()) {
. . . // process results
stmt.getMoreResults();
continue;
}
break; // there are no more results