1.0

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 on page 161 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 on page 151
• Construct a Result Set with OutgoingResultSet on page 151
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 on page 152 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.
151
Using the Procedure Provider API