Use execute() and getMoreResults() methods for those pesky complex SQL queries

First published on MSDN on Aug 01, 2008

Per JDBC spec, the Statement.executeUpdate() and Statement.executeQuery() methods are to be used only with queries that produce a single update count or result set, respectively.  If you need to execute multiple SQL statements in a single query, perhaps through a stored procedure call, then you should use Statement.execute() to execute the query and Statement.getMoreResults() to process all of the results.  However, in my not so humble opinion, the execute() and getMoreResults() methods aren’t exactly simple to use properly.  For example, execute() and getMoreResults() return false when the result is an update count.  But they also return false when there are no more results.  Ok, so how do you tell whether you have an update count or no more results?  You need to check the return value from a subsequent call to Statement.getUpdateCount() or Statement.getResultSet() .  One way to process all results (including errors) from a complex query would be to use code like this:

CallableStatement cs = con.prepareCall("{call myStoredProc()}");

int resultNum = 0;

while ( true )

{

boolean queryResult;

int rowsAffected;

if (1 == ++resultNum)

{

try

{

queryResult = cs.execute();

}

catch (SQLException e)

{

// Process the error

System. out .println("Result " + resultNum + " is an error: " + e.getMessage());

// When execute() throws an exception, it may just be that the first statement produced an error.

// Statements after the first one may have succeeded.  Continue processing results until there

// are no more.

continue ;

}

}

else

{

try

{

queryResult = cs.getMoreResults();

}

catch (SQLException e)

{

// Process the error

System. out .println("Result " + resultNum + " is an error: " + e.getMessage());

// When getMoreResults() throws an exception, it may just be that the current statement produced an error.

// Statements after that one may have succeeded.  Continue processing results until there

// are no more.

continue ;

}

}

if (queryResult)

{

ResultSet rs = cs.getResultSet();

// Process the ResultSet

System. out .println("Result " + resultNum + " is a ResultSet: " + rs);

rs.close();

}

else

{

rowsAffected = cs.getUpdateCount();

// No more results

if (-1 == rowsAffected)

{

--resultNum;

break ;

}

// Process the update count

System. out .println("Result " + resultNum + " is an update count: " + rowsAffected);

}

}

System. out .println("Done processing " + resultNum + " results");

Remember: to execute a complex query with a mix of DDL, DML and result sets, and possible errors, always use execute() to execute the query and use getUpdateCount(), getResultSet(), and getMoreResults() to process the results.

--David Olix [SQL Server]

This post is provided 'as is' and confers no express or implied warranties or rights.