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

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

  • Comments 12

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.

Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
  • If you are using executeUpdate() to execute a query or stored procedure containing multiple SQL statements,

  • For stored procedures, where do the return value and output parameters fit into this picture?

    Thanks,

    Mike

  • For the record, if you've just got a normal Statement (which might include several SQL selects/inserts/updates/etc.), then it appears you can just do the following with one .execute(String sql):

    Statement stmt = con.createStatement();

    try {

    boolean hasResults = stmt.execute(sql);

    do {

    if (hasResults) printResults(stmt.getResultSet(),"\t");

    else System.out.printf("%d rows affected\n\n", stmt.getUpdateCount());

    hasResults = stmt.getMoreResults();

    } while (hasResults || stmt.getUpdateCount() != -1);

    } catch (Exception e) {

    System.err.println("Error with: " + sql);

    e.printStackTrace();

    } finally {

    stmt.close();

    }

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

  • 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

  • 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.

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

  • Sorry, the above given code "boolean hasResults = stmt.execute(sql);" DOESNT work if the sql calls a stored proc that updates some rows and then returns a result set.

    Instead, use

                   boolean hasResults = stmt.execute(sql);

                   boolean moreResults = stmt.getMoreResults();

                    if (hasResults || moreResults) {

                    .....

    THIS WORKS!! it took me 2 hours to figure this out, as ALL the official MSDN examples only use the "hasResults" flag at the outset, rather than both. I would recommend using both for safety.

    HTH

  • 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.

  •   Remember: to execute a complex query with a mix of DDL, DML and result sets, and possible errors

    =========================

    but now, I have to execute multiple SQLS which includes query, DDL, DML and so on, will you please tell me how can resolve it,

    Thanks very much

  • @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

  • 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;

    }

Page 1 of 1 (12 items)