Collaboration usability for a stored procedure ResultSet

The Column data of the ResultSets can be dragged-and-dropped from their XSC 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("===");
  }
}

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 method resultsAvailable() implicitly calls getMoreResults() when it is called more than once. You should not call both methods in your Java code. Doing so may result in skipped data from one of the ResultSets when more than one ResultSet is present.

• The methods available() and getResultSet() can not be used in conjunction with multiple ResultSets being open at the same time. Attempting to open more the one ResultSet at the same time closes the previous ResultSet. The recommended working pattern is:

  • Open one Result Set (ResultSet_1) and work with the data until you have completed your modifications and updates. Open ResultSet_2, (ResultSet_1 is now closed) and modify. When you have completed your work in ResultSet_2, open any additional ResultSets or close ResultSet_2.

  If you modify the ResultSet generated by the Execute mode of the Database Wizard, you need to assure the indexes match the stored procedure. By doing this, your ResultSet indexes are preserved.

• Generally, getMoreResults does not need to be called. It is needed if you do not want to use our enhanced methods and you want to follow the traditional JDBC calls on your own.

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