Procedure Parameters

When you configure a procedure using the CREATE PROCEDURE statement, SQLFire assembles the method parameters and passes them to the procedure implementation class using Java reflection.

The different types of parameters are handled in the following ways:
  • IN and INOUT parameters are passed to the procedure implementation as single-element arrays.
  • DYNAMIC RESULT SETS specified in the CREATE PROCEDURE statement cause additional method arguments to be appended, one for each dynamic result set. Each argument is treated as a ResultSet[] type. SQLFire passes in each argument as a single-element array containing a null value.

    For example, if you specify DYNAMIC RESULT SETS 2, SQLFire appends two additional ResultSet[] arguments, each with a single null value.

    ResultSets are returned to the application through the CallableStatement, in the order that they are defined in the procedure body. See Populating Result Sets.

  • The procedure implementation class can optionally specify a ProcedureExecutionContext parameter as the last parameter. SQLFire then passes in the procedure context object, which the implementation can use to determine information about the execution context.
    Note: The CREATE PROCEDURE and CALL statements should not reference the procedure context object as a procedure parameter.
For example, if the Java method signature of the procedure implementation is:
package com.acme.MyProc; 

import java.sql.*; 

public class MyProc 
  {public static void myMethod(String inParam1, 
                               Integer[] outParam2, 
                               Date[] inoutParam3, 
                               Widget[] inoutParam4, 
                               ResultSet[] resultSet1, 
                               ResultSet[] resultSet2) { 
   ...
  }
}
You would configure the procedure in SQLFire using a statement similar to:
CREATE PROCEDURE myProc (IN inParam1 VARCHAR(10), 
                         OUT outParam2 INTEGER, 
                         INOUT DATE inoutParam3, 
                         INOUT WidgetType inoutParam4)

LANGUAGE JAVA 
PARAMETER STYLE JAVA 
READS SQL DATA 
DYNAMIC RESULT SETS 2 
EXTERNAL NAME 'com.acme.MyProc.myMethod'
Note: The formal parameter names are used as an example, and the actual parameter names need not match.
Note: A user-defined type (WidgetType) implementation would have to be created elsewhere in this example.
This same CREATE PROCEDURE statement could be used even if the Java implementation included a ProcedureExecutionContext in the method signature, as in the following example.
package com.acme.MyProc; 

import java.sql.*; 

public class MyProc 
   {public static void myMethod(String inParam1, 
                               Integer[] outParam2, 
                               Date[] inoutParam3, 
                               Widget[] inoutParam4, 
                               ResultSet[] resultSet1, 
                               ResultSet[] resultSet2,
                               ProcedureExecutionContext context) { 
   ...
  }
}