Cayenne User Documentation
Executing a Stored Procedure

### Using QueryResponse to Process Complex Results

Previous chapter showed how to select a single set of data rows using a ProcedureQuery. In a more general case stored procedures can return multiple sets of data, either as ResultSets or via OUT parameters, execute update/delete/insert queries, etc. To collect the results of execution of such stored procedure, you need to run a query using context's `"performGenericQuery"` method and inspect returned QueryResponse.

    DataContext ctxt;

    // "my_procedure" is a name of a stored procedure,
    // that must exist in the DataMap
    ProcedureQuery query = new ProcedureQuery("my_procedure");

    // Set "IN" parameter values
    query.addParam("parameter1", "abc");
    query.addParam("parameter2", new Integer(3000));

    // run query
    QueryResponse result = ctxt.performGenericQuery(query);

    // check the results
    Iterator it = rowSets.iterator();
    while(result.next()) {
         if (result.isList()) {
              List list = result.currentList();
              // ...
         }
         else {
              int[] updateCounts = result.currentUpdateCount();
              // ...
         }
    }

### Using QueryResponse to Read OUT Parameters

Stored Procedure can return data back to the application as ResultSets or via OUT parameters. To simplify the processing of the query output, QueryResponse treats OUT parameters as if it was a separate ResultSet. If a stored procedure declares any OUT or INOUT parameters, QueryResponse will contain their returned values in the very first result list:

    DataContext ctxt;

    // "my_procedure" is a name of a stored procedure,
    // that must exist in the DataMap
    ProcedureQuery query = new ProcedureQuery("my_procedure");

    // Set "IN" parameter values
    query.addParam("paramter1", "abc");
    query.addParam("parameter2", new Integer(3000));

    // run query
    QueryResponse result = ctxt.performGenericQuery(query);

    // read OUT parameters
    List outList = result.firstList();

    if(outList.size() >  0) {
        Map outParameterValues = (Map) outList.get(0);
    }

.
Copyright ©2001-2007 Apache Software Foundation