DB2 Version 9.7 for Linux, UNIX, and Windows

Using named parameters in CALL statements in SQLJ applications

You can use named parameters to map host variable names in a CALL statement to the parameter names in the stored procedure definition.

With named parameters, you do not need to specify parameters in the CALL statement in the same order that they appear in the stored procedure definition. In addition, you do not need to specify all parameters in the CALL statement. Unspecified parameters take the default values that are specified in the stored procedure definition.

To use named parameters with CALL statements, follow these steps:

In the CALL statement, assign values to IN or INOUT host variables.
The named parameters point to the host variables. The rules for naming of named parameters and assignment to named parameters must follow the rules for named parameters in SQL CALL statements. You can explicitly assign the default value or the null value to a named parameter by specifying the DEFAULT keyword or the NULL keyword. For parameters for which a default value is specified in the CREATE PROCEDURE statement, you can implicitly assign the default values to named parameters by omitting those parameters from the CALL statement. You can omit parameters only if all of the omitted parameters have default values in the stored procedure definition.

You cannot mix named parameters and unnamed parameters in the same CALL statement.
Process output (OUT or INOUT) parameters.
If the stored procedure returns multiple result sets, retrieve those result sets.

The following code illustrates calling a stored procedure that has the following definition:

CREATE PROCEDURE SALS (
  OUT retcode INTEGER,
  IN lowsal DOUBLE,
  IN medsal DOUBLE, 
  IN highsal DOUBLE DEFAULT 100000, 
  IN department CHAR(3) DEFAULT '---')
SPECIFIC JDBC_SALS
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA 
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'MYJAR:MyClass.sals'

The input parameters in the CALL statement are represented by named parameters. The third and fourth parameters are called with the default values for the stored procedure. The numbers to the right of selected statements correspond to the previously-described steps.

double hvLowSal=10000;               // Host variables for input parameters
double hvMedSal=50000;
int hvRetCode;                       // Host variable for output parameter
…
#sql [myConnCtx] {CALL SALS(retcode=>:OUT hvRetCode,               1 
                    lowsal=>:IN hvLowSal,
                    medsal=>:IN hvMedSal,
                    highsal=>DEFAULT)};
                                  // Call the stored procedure.
                                  // Implicitly use the default
                                  // value for the last parameter
                                  // by omitting it.
System.out.println("Return code from SALS call: " + hvRetCode);    2