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);
}