CODEWITHSUNDEEP
stored-proceduresjdbc
where_
where_

Reputation: 301

Is there a way to determine ahead the number of result sets to be returned when executing a stored procedure or function?

Let's assume the following dummy example in JDBC:

   Statement statement = connection.createStatement();
   statement.execute("call sp1(); select * from X;");
   
   int uc = statement .getUpdateCount();
   boolean isResult = (uc == -1);

   do
   {
      if( isResult )
      {
         ResultSet rs = statement.getResultSet();

         if( rs != null )
         {
            doSomethingWithRS( rs );
         }
      }
      else
      {
         System.out.println( "update count: " + uc );
      }
   }
   while( ( isResult = statement.getMoreResults() ) || ( uc = statement.getUpdateCount() ) != -1 );

It is known that some Databases/Drivers allow multiple statement execution (SQL Server as an example). The details of a Stored Procedure are NOT necessarily know ahead to a programmer.
Is there a way to determine ahead the number of ResultSets to be returned from Stored Procedure calls - so that they could be distinguished from the ResultSets returned for the consequent SELECT?

Upvotes: 1

Views: 44

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108981

No, there is nothing in JDBC that allows you to know that ahead of time, and usually there is not even an option to know that in the underlying database system. For example, in SQL Server it is entirely possible to write a stored procedure that depending on its inputs (including in the tables it accesses, environment configuration, etc) outputs zero, one, or dozens of result sets (and update counts). Such a thing is not decidable, without doing the execution itself.

The only thing you can do is interpret the results of execute() and getMoreResults() and getUpdateCount() and getResultSet() correctly, or alternatively don't write stored procedures that can have multiple or dynamic results.

As an aside, your initial code should not call getUpdateCount() to decide if it is a result set or not, it should use the return value of execute():

boolean isResultSet = statement.execute("call sp1(); select * from X;");
while (true) {
  if (isResultset) {
    // Null-check not necessary, a correctly implemented JDBC driver 
    // should never return null if execute or getMoreResults returned true
    try (ResultSet rs = statement.getResultSet()) {
      // process rs
    }
  } else {
    int uc = statement.getUpdateCount();
    if (uc == -1) {
      // when isResultSet is false and update count is -1, 
      // there are no more results
      break;
    }
    System.out.println("update count: " + uc);
  }
  isResultSet = statement.getMoreResults()
}

Upvotes: 1

Related Questions