Parameter style JAVA procedures

The signature of PARAMETER STYLE JAVA stored procedures follows this format:

public static void method-name ( SQL-arguments, ResultSet[] result-set-array )
                                throws SQLException

method-name

Name of the method. During routine registration, this value is specified with the class name in the EXTERNAL NAME clause of the CREATE PROCEDURE statement.

SQL-arguments

Corresponds to the list of input parameters in the CREATE PROCEDURE statement. OUT or INOUT mode parameters are passed as single-element arrays. For each result set that is specified in the DYNAMIC RESULT SETS clause of the CREATE PROCEDURE statement, a single-element array of type ResultSet is appended to the parameter list.

result-set-array

Name of the array of ResultSet objects. For every result set declared in the DYNAMIC RESULT SETS parameter of the CREATE PROCEDURE statement, a parameter of type ResultSet[] must be declared in the Java method signature.

The following is an example of a Java stored procedure that accepts an input parameter, and then returns an output parameter and a result set:

public static void javastp( int inparm,
                            int[] outparm, 
                            ResultSet[] rs 
                          )
                   throws SQLException
{
  Connection con = DriverManager.getConnection( "jdbc:default:connection" );
  PreparedStatement stmt = null;
  String sql = "SELECT value FROM table01 WHERE index = ?";

  //Prepare the query with the value of index  
  stmt = con.prepareStatement( sql );
  stmt.setInt( 1, inparm );

  //Execute query and set output parm
  rs[0] = stmt.executeQuery();
  outparm[0] = inparm + 1;

  //Close open resources
  if (stmt != null) stmt.close();
  if (con != null) con.close();  

  return;
}

The corresponding CREATE PROCEDURE statement for this stored procedure is as follows:

CREATE PROCEDURE javaproc( IN in1 INT, OUT out1 INT )
  LANGUAGE java
  PARAMETER STYLE java 
  DYNAMIC RESULT SETS 1
  FENCED THREADSAFE
  EXTERNAL NAME 'myjar:stpclass.javastp'

The preceding statement assumes that the method is in a class called stpclass, located in a JAR file that has been cataloged to the database with the Jar ID myjar