Passing parameters of data type ARRAY to Java routines

You can pass ARRAY data type parameters to and from Java™ procedures.

About this task

When your stored procedure is created with the ARRAY data type parameters, your stored procedure can accept or return a variable number of input data or return data of the same data type with a single parameter.

For example, you can pass all the names of students in a class to a procedure without knowing the number of students with a single parameter.

Procedure

To pass a parameter of type ARRAY:

  1. The ARRAY data type must be already defined. To define an array type, the CREATE TYPE statement must be executed.
  2. The procedure definition must include a parameter of the defined type. The following CREATE PROCEDURE statement example accepts a user-defined ARRAY data type IntArray:

Example

CREATE PROCEDURE inArray (IN input IntArray)
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'MyProcs:MyArrayProcs!inArray';
In the procedure definition, the array parameter is typed as java.sql.Array. Within the procedure, the argument is mapped to a Java array using the getArray() method, as shown in the following example. Notice the use of Integer rather than int (or other primitive types) for arrays.
static void inArray(java.sql.Array input)
{
Integer[] inputArr = (Integer [])input.getArray();
int sum = 0;
for(int i=0, i < inputArr.length; i++)
{
sum += inputArr[i];
}
}