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 context;

    // "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.addParameter("parameter1", "abc");
    query.addParameter("parameter2", new Integer(3000));

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

    // check the results
    for (result.reset(); result.next();) {
         if (response.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 context;

    // "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.addParameter("paramter1", "abc");
    query.addParameter("parameter2", new Integer(3000));

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

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

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

.
Copyright ©2001-2010 Apache Software Foundation