[SOLVED] Squirrel SQL Editor does not return multiple results from a stored procedure with 3 select statements

Squirrel SQL Editor does not return multiple results from a stored procedure with 3 select statements

I have a stored procedure which is run in Squirrel SQL editor for Sybase. The stored procedure has 3 select statements, but when executed , it returns only Ithe result of first select statement.

What I have tried: Executed the stored procedure using Java SQL API. I see the same behavior. Also, executed the same stored procedure in Toad for Sybase and see the same behavior.

Our company uses an 3rd party database execution tool for legacy C++ apps. That library returns all the 3 result sets as expected. We are removing this library for some reason.

Solution

Use CallableStatement#execute() to execute the stored procedure which returns multiple ResultSets.

It returns a boolean.

If true : If current result is a ResultSet If false : The current result is a UpdateCount

What is a UpdateCount? When you execute a stored procedure in SQL Editor, you will get some information in Console saying, for example, "4 rows affected". This message is called an UpdateCount.

Database returns these messages as well as the resultsets in the response to API.

Traverse through the Results returned.

    boolean hasResults = stmt.execute();
    
    while(true) {
      if(hasResults) {
          //Has results is true if Current result is ResultSet
        //Add code here to parse your result values 
      } else {
          if(stmt.getUpdateCount() == -1) break; // you have reached the end of all data (updatecount + resultset)
    }
    hasResults = stmt.getMoreResults(); // GetMoreResults return true if the next result is ResultSet else it returns false if its updatecount.
}

If you don't want the UpdateCounts to be returned as part of results from database, you include the statement SET NOCOUNT ON in your stored procedures