JDBC Programmer Reference › Procedures › Using Named Parameters

Using Named Parameters

A CallableStatement object, used for calling SQL procedures, can support binding of parameters using the parameter names defined in CA IDMS by the CREATE PROCEDURE statement. This technique is an alternative to identifying each parameter by an ordinal index corresponding to a parameter marker ('?') in the SQL CALL statement. Named parameters are useful for procedures which have large numbers of parameters, particularly if default values can be used. Parameters can be bound in any order; a parameter can also be skipped completely if an acceptable default value has been defined in the procedure.

To use named parameters, an SQL CALL statement is prepared which contains markers for necessary parameters, for example:

CallableStatement cstmt = conn.prepareCall("{ CALL MYPROC(?, ?, ?) })";

Each parameter used as IN or INOUT is then bound to a value using the setXXX method that is appropriate for the parameter's data type. Each parameter used as OUT must be registered using the registerOutputParameter method and specifying the expected data type. The parameter names for both types of methods must have been defined in the CREATE PROCEDURE statement in CA IDMS.

cstmt.setString("INPARM", "First");
cstmt.setString("IOPARM", "Second");
cstmt.registerOutParameter( "OUTPARM", java.sql.types.STRING);

After the statement has been executed, INOUT and OUT parameters can be retrieved using the parameter names:

String io = cstmt.getString("IOPARM");
String out = cstmt.getString("OUTPARM");

Named parameter binding cannot be mixed with ordinal binding on the same CallableStatement object; an SQLException is thrown if this is attempted.

For more information about named parameters, see the Java documentation for the Java Platform API Specification or the JDBC 4.0 Specification.