Using named parameters in CALL statements in JDBC applications

The IBM® Data Server Driver for JDBC and SQLJ provides several ways to use named parameters when you call stored procedures. Named parameters use a different syntax from named parameter markers.

About this task

You can use named parameters in either or both of the following places in a JDBC application:

In the CALL statement
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.
In CallableStatement.setXXX, CallableStatement.getXXX, and CallableStatement.registerOutParameter methods
You can make your programs easier to read by specifying parameter names as they appear in the stored procedure definition, rather than the positions of the parameters in the definition.

Procedure

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

Invoke the Connection.prepareCall method with the CALL statement as its argument to create a CallableStatement object.
To indicate each parameter, you can use a parameter markers (?), or this syntax:
```
parameter-name=>?
```
parameter-name identifies a parameter in the CREATE PROCEDURE statement.
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 named parameter markers in the same CALL statement.
Invoke the CallableStatement.setXXX methods to pass values to the input parameters (parameters that are defined as IN or INOUT in the CREATE PROCEDURE statement).
You can assign values in either of the following ways:
- By position, using CallableStatement.setXXX(parameterIndex,…)
- By name, using CallableStatement.setXXX(parameterName,…)
  parameterName is a string that is enclosed in double quotation marks, whose value matches a parameter name in the CREATE PROCEDURE statement.
Invoke the CallableStatement.registerOutParameter method to register parameters that are defined as OUT in the CREATE PROCEDURE statement with specific data types.
Invoke CallableStatement.executeUpdate, CallableStatement.executeQuery, or CallableStatement.execute to execute the stored procedure.
If the stored procedure returns multiple result sets, retrieve those result sets.
You can register the output parameters in either of the following ways:
- By position, using CallableStatement.registerOutParameter(parameterIndex,…)
- By name, using CallableStatement.registerOutParameter(parameterName,…)
  parameterName is a string that is enclosed in double quotation marks, whose value matches a parameter name in the CREATE PROCEDURE statement.
Invoke the CallableStatement.getXXX methods to retrieve values from the OUT parameters or INOUT parameters.
You can retrieve values in either of the following ways:
- By position, using CallableStatement.getXXX(parameterIndex,…)
- By name, using CallableStatement.getXXX(parameterName,…)
  parameterName is a string that is enclosed in double quotation marks, whose value matches a parameter name in the CREATE PROCEDURE statement.
Invoke the CallableStatement.close method to close the CallableStatement object when you have finished using that object.

Example

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.

int hvRetCode;                       // Host variable for output parameter
Connection con;
CallableStatement cstmt;
ResultSet rs;
…

cstmt = con.prepareCall(
  "CALL SALS(retcode=>?,lowsal=>?,medsal=>?,highsal=>DEFAULT)");   1 
                                  // Prepare the Call statement.
                                  // Implicitly use the default
                                  // value for the last parameter
                                  // by omitting it.
cstmt.setDouble ("lowsal", 10000);                                 2 
cstmt.setDouble ("medsal", 50000);
cstmt.registerOutParameter ("retcode", Types.INTEGER);             3 
                                  // Register output parameter
cstmt.executeUpdate();            // Call the stored procedure     4 
hvRetCode = cstmt.getInt("retcode");                               6 
System.out.println("Return code from SALS call: " + hvRetCode);   
cstmt.close();                                                     7