Returning values from Java via stored procedures

You can use stored procedures created using the EXTERNAL NAME LANGUAGE JAVA as wrappers around Java methods. This section describes how to write your Java method to exploit OUT or INOUT parameters in the stored procedure.

Java does not have explicit support for INOUT or OUT parameters. Instead, you can use an array of the parameter. For example, to use an integer OUT parameter, create an array of exactly one integer:

public class Invoice 
{
  public static boolean testOut( int[] param )
  {
    param[0] = 123;
    return true;
  }
}

The following procedure uses the testOut method:

CREATE PROCEDURE testOut( OUT p INTEGER )
EXTERNAL NAME 'Invoice.testOut([I)Z'
LANGUAGE JAVA;

The string ([I)Z is a Java method signature, indicating that the method has a single parameter, which is an array of integers, and returns a Boolean value. You must define the method so that the method parameter you want to use as an OUT or INOUT parameter is an array of a Java data type that corresponds to the SQL data type of the OUT or INOUT parameter.

To test this, call the stored procedure with an uninitialized variable.

CREATE VARIABLE zap INTEGER;
CALL testOut( zap );
SELECT zap;

The result set is 123.

For more information about the syntax, including the method signature, see CREATE PROCEDURE statement (web services).