Populating Result Sets

The SQLFire API provides different ways to construct result sets in a procedure implementation.

A procedure must open and generate a ResultSet either from the default connection (jdbc:default:connection) or from the connection that is obtained from the ProcedureExecutionContext object. SQLFire ignores result sets that are generated in any other manner. If your implementation requires a result set from a thin client connection or from a connection to an external database, then create an OutgoingResultSet to populate the results from the connection.

See Using Result Sets and Cursors for more information about using result sets in SQLFire.

SQLFire returns ResultSets to the application through the CallableStatement, in the order that they are defined in the procedure body. Your procedure implementation can return fewer ResultSets than are defined in the DYNAMIC RESULT SETS clause; construct only those ResultSets that you require.

Create all PreparedStatement or other Statement objects directly in the body of the procedure method that requires those objects. Do not attempt to cache statement objects as static variables. In contrast to a JDBC client application method, a Java procedure method cannot hold onto a JDBC object after it completes. Also, do not close a statement that generates a ResultSet, because doing so closes the ResultSet itself.

The SQLFire API provides two ways to help you construct a result sets in the procedure implementation:

Execute a Query to Populate a Result Set

The ProcedureExecutionContext provides a getConnection() method that returns a nested JDBC connection. You use this connection (or the default connection) to populate one or more ResultSets with a nested query. Data for the nested query is not manifested until next() is called on the ResultSet. SQLFire calls next() on the ResultSets as necessary to stream the required rows.

For example:
Connection cxn = context.getConnection();
Statement stmt = cxn.createStatement();
resultSet1[0] = stmt.executeQuery("select * from Bar where foo > 42");
resultSet2[0] = stmt.executeQuery("select * from Bar where foo <= 42");

SQLFire creates the one-element ResultSet arrays that hold the returned ResultSets.

Note: Do not close the connection or the statement that you use to create the result set, because doing so also closes the result set.

Remember that a data-aware procedure might be invoked with a WHERE clause that restricts execution to one or more SQLFire members, and the procedure implementation itself might execute a nested query on the same table. By default, nested queries execute only on those SQLFire members that were scoped at procedure invocation. Using the <local> and <global> Escape Syntax with Nested Queries describes how to override the default scope for nested queries when required by your procedure implementation.

Construct a Result Set with OutgoingResultSet

As an alternative, the procedure can obtain an empty OutgoingResultSet object from the ProcedureExecutionContext or default connection, and then invoke addColumn() for each column of the result set followed by addRow() for each row. You can skip the initial calls to addColumn() if you want to use default column names, such as "c1," "c2," and so forth.

When you use this method to construct a result set, SQLFire can immediately stream results after a call to addRow(), even while the procedure implementation continues to add rows.

For example:
OutgoingResultSet rs1 = context.getOutgoingResultSet(1);
rs1.addColumn("field1");
rs1.addColumn("field2");

for (int i = 0; i < 10; i++) {
  rs1.addRow(new Object[i, String.valueOf(i)]);
}
rs1.endResults();
Note: Do not close the connection or the statement that you use to create the result set, because doing so also closes the result set.