1.1

[ [DYNAMIC] RESULT SETS integer]
EXTERNAL NAME 'procedure_external_class.method'
The procedure-name is a SQL identifier that you can use to call the procedure implementation in SQLFire. The
procedure_external_name specifies the actual static class_name.method_name of the Java procedure
implementation. Using the Procedure Provider API on page 159 provides more information about implementing
procedures.
One or more procedure-parameter entries use the syntax:
[ { IN | OUT | INOUT } ] [parameter_name] DataType
Each parameter entry should match a corresponding parameter in the procedure's Java implementation. SQLFire
supports the data types described in Data Types on page 604, including user-defined types (see Programming
User-Defined Types on page 167).
The results of a procedure, if any, can be supplied as OUT parameters, INOUT parameters, or as dynamic result
sets. A client retrieves OUT parameters using methods in java.sql.CallableStatement.
The NO SQL, CONTAINS SQL, READS SQL DATA, and MODIFIES SQL DATA options are used to describe
the type of SQL statements that the procedure uses. Choose one of the available options:
• NO SQL indicates that the stored procedure does not execute any SQL statements.
• CONTAINS SQL indicates that the procedure does not execute SQL statements that read nor modify SQL
data.
• READS SQL DATA indicates that the procedure does not execute SQL statements that modify SQL data, but
may issue other statements (such as SELECT statements) to read data.
• MODIFIES SQL DATA indicates that the procedure can execute any SQL statement except those that are
specifically disallowed in stored procedures. SQLFire uses MODIFIES SQL DATA as the default.
SQLFire throws an exception if a procedure attempts to execute SQL statements that conflict with the NO SQL,
CONTAINS SQL, or MODIFIES SQL DATA setting.
RESULT SETS indicates the estimated upper bound of returned result sets for the procedure. Access result sets
by calling the getMoreResults() and getResultSet() statements in java.sql.Statement.
EXTERNAL NAME specifies the fully qualified class name and method name of the procedure to create.
Example
CREATE PROCEDURE SALES.TOTAL_REVENUE(IN S_MONTH INTEGER,
IN S_YEAR INTEGER, OUT TOTAL DECIMAL(10,2))
LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA EXTERNAL
NAME
'com.sqlfire.funcs.Revenue.calculateRevenueByMonth'
Configuring a Custom Result Processor
Data-aware procedures use a separate result processor to merge the procedure results from multiple SQLFire
members. You can use the default SQLFire result processor, or implement and configure your own result processor
implementation to customize the merge behavior.
Using the Procedure Provider API on page 159 describes how to implement a custom result processor.
vFabric SQLFire User's Guide154
Developing Applications with SQLFire