Using Database Operations

Manipulating the ResultSet and Update Count Returned by Stored Procedure

For Stored Procedures that return ResultSets and Update Count, the following methods are provided to manipulate the ResultSet:

DB2 stored procedures do not return records as ResultSets, instead, the records are returned through output reference cursor parameters. Reference Cursor parameters are essentially ResultSets.

The resultsAvailable() method, added to the PreparedStatementAgent class, simplifies the whole process of determining whether any results, be it Update Counts or ResultSets, are available after a stored procedure has been executed. Although JDBC provides three methods (getMoreResults(), getUpdateCount(), and getResultSet()) to access the results of a stored procedure call, the information returned from these methods can be quite confusing to the inexperienced Java JDBC programmer and they also differ between vendors. You can simply call resultsAvailable() and if Boolean true is returned, you can expect either a valid Update Count when getUpdateCount() is called and/or the next ResultSet has been retrieved and made available to one of the ResultSet nodes defined for the Stored Procedure OTD, when that node’s available() method returns true.

Frequently, Update Counts information that is returned from a Stored Procedures is insignificant. You should process returned ResultSet information and avoid looping through all of the Update Counts. The following three methods control exactly what information should be returned from a stored procedure call. The enableResultSetsOnly() method, added to the PreparedStatement Agent class allows only ResultSets to be returned and thus every resultsAvailable() called only returns Boolean true if a ResultSet is available. Likewise, the enableUpdateCountsOnly() causes resultsAvailable() to return true only if an Update Count is available. The default case of enableResultsetsAndUpdateCount() method allows both ResultSets and Update Counts to be returned.

Collaboration usability for a stored procedure ResultSet

The Column data of the ResultSets can be dragged-and-dropped from their nodes to the Business Rules. Below is a code snippet that can be generated by the Collaboration Editor:


while (getSPIn().getSpS_multi().resultsAvailable())
{
if (getSPIn().getSpS_multi().getUpdateCount() > 0)
  {
    System.err.println("Updated "+getSPIn().getSpS_multi()
.getUpdateCount()+" rows");
  }

  if (getSPIn().getSpS_multi().getNormRS().available())
  {
    while (getSPIn().getSpS_multi().getNormRS().next())
    {
      System.err.println("Customer Id   = "+getSPIn().getSpS_multi().
getNormRS().getCustomerId());
      System.err.println("Customer Name = "+getSPIn().getSpS_multi()
getNormRS().getCustomerName());
      System.err.println();
    }
    System.err.println("===");
  }
  else if (getSPIn().getSpS_multi().getDbEmployee().available())
  {
    while (getSPIn().getSpS_multi().getDbEmployee().next())
    {
      System.err.println("EMPNO    = "+getSPIn().getSpS_multi().
getDbEmployee().getEMPNO());
      System.err.println("ENAME    = "+getSPIn().getSpS_multi().
getDbEmployee().getENAME());
      System.err.println("JOB      = "+getSPIn().getSpS_multi().
getDbEmployee().getJOB());
      System.err.println("MGR      = "+getSPIn().getSpS_multi().
getDbEmployee().getMGR());
      System.err.println("HIREDATE = "+getSPIn().getSpS_multi().
getDbEmployee().getHIREDATE());
      System.err.println("SAL      = "+getSPIn().getSpS_multi().
getDbEmployee().getSAL());
      System.err.println("COMM     = "+getSPIn().getSpS_multi().
getDbEmployee().getCOMM());
      System.err.println("DEPTNO   = "+getSPIn().getSpS_multi().
getDbEmployee().getDEPTNO());
      System.err.println();
    }
    System.err.println("===");
  }
}

Note –

resultsAvailable() and available() cannot be indiscriminately called because each time they move ResultSet pointers to the appropriate locations.


After calling "resultsAvailable()", the next result (if available) can be either a ResultSet or an UpdateCount if the default "enableResultSetsAndUpdateCount()" was used.

Because of limitations imposed by some DBMSs, it is recommended that for maximum portability, all of the results in a ResultSet object should be retrieved before OUT parameters are retrieved. Therefore, you should retrieve all ResultSet(s) and Update Counts first followed by retrieving the OUT type parameters and return values.

The following list includes specific ResultSet behavior that you may encounter:

The DBWizard Assistant expects the column names to be in English when creating a ResultSet.