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

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.

Comments

• Anonymous
  August 04, 2008
  The comment has been removed

• Anonymous
  August 05, 2008
  For stored procedures, where do the return value and output parameters fit into this picture? Thanks, Mike

• Anonymous
  September 26, 2008
  The comment has been removed

• Anonymous
  February 25, 2009
  If you've ever encountered this error with the Microsoft SQL Server JDBC Driver, you may be left scratching

• Anonymous
  May 11, 2009
  Thanks. Your explaination helped figuring out what was going wrong with part of our code. Actually, what I've been reading elsewhere on the net is not as accurate as your account. I'd still optimize the Java code a bit, but still great work. Thanks for posting. -Norb

• Anonymous
  January 12, 2010
  Thank you! executeQuery() gave us "SQLServerException: The statement did not return a result set when sp uses cursors", but this code fragment did the trick. It turned out that there were "0 update counts" before our ResultSet.

• Anonymous
  March 30, 2010
  Thank you so much. I had been trying several other ways. Finally found this and it workd for me. I am relaxed.

• Anonymous
  March 13, 2011
  The comment has been removed

• Anonymous
  February 14, 2012
  Thanks for the tip.  Ran into this as we were porting some DBs.   As a workaround, I turned off the update msgs by inserting this into my sql stored proc:  SET NOCOUNT ON;  That seems to work with the old code until I can get it fixed.

• Anonymous
  March 01, 2012
  The comment has been removed

• Anonymous
  March 03, 2012
  @hook.  Try posting your question on the Data Access forums.  You will reach a much wider audience including Microsoft MVP's & Microsoft Customer Support. social.msdn.microsoft.com/.../threads

• Anonymous
  January 22, 2013
  Beware that ypu may run into an endless loop with the example code if the server closes the connection after the query is executed. To avoid, add the following to the try-catch: 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.                       //However, it might also be that the connection was closed                       if(cs.isClosed())                           break;                        continue; }

• Anonymous
  March 21, 2015
  The comment has been removed