Configuring a Procedure

You must configure a procedure implementation in SQLFire before you can invoke the procedure.

Before you configure a procedure, ensure that the procedure implemention is available in the SQLFire classloader. See Storing and Loading JAR Files in SQLFire.

The syntax for configuring a procedure in SQLFire is as follows. You designate a procedure as data-aware or data-independent when you invoke it. See Invoking a Procedure.

CREATE PROCEDURE procedure-name
([ procedure-parameter [, procedure-parameter] * ])
LANGUAGE JAVA
PARAMETER STYLE JAVA
{ NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA }
[ [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 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, including user-defined types (see Programming User-Defined Types).

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'