[Springframework-developer] New - stored procedures returning result sets

[<tri...@tr...>]

I have checked in the changes for supporting stored procedures returning result
sets.  For Oracle you would declare an SqlOutParameter with a type of
oracle.jdbc.OracleTypes.CURSOR along with a RowCallbackHandler that will map or
process the returned rows.  

For databases where you don't explicitly declare the result sets that are
returned (SQL Server, Sybas, DB2) you would declare an SqlReturnResultSet
parameter with a RowCallBackHandler.  You can specify multiple parameters and
RowCallBackHandlers if your procedure returns multiple result sets.  

Postgres is supposed to support this in 7.4 and you would declare it the same
way as Oracle except the type would be java.sql.Types.OTHER.

This is an example of an Oracle procedure:

class MySPwithRS extends StoredProcedure {

  private List results = new LinkedList();
	
  MySPwithRS(DataSource ds) {
    super(ds, "pkg_test.get_emp");
    setFunction(true);
    declareParameter(new SqlOutParameter("rs", oracle.jdbc.OracleTypes.CURSOR,
      new RowCallbackHandlerImpl()));
    compile();
  }

  Object execute() {
    Map in = new HashMap();
    Map out = execute(in);
    out.put("rs", results);
    return out;
  }

  private class RowCallbackHandlerImpl implements RowCallbackHandler {
    public void processRow(ResultSet rs)  throws SQLException {
      results.add(rs.getString(2));
    }
  }
}

I have also made a change so if you call execute without calling compile, the
framework will automatically compile you prepared statement or stored procedure
instead of just throwing an exception.  This is more inline with how JDO handles
this.

Thomas