Named Parameters within JDBC

Christoph Gächter, Akadia AG, Information Technology, CH-3604 Thun
Phone: +41 33 335 86 21 / Fax: +41 33 335 86 25 /
EMail: christoph.gaechter@akadia.com

Overview

PL/SQL supports two different notations for procedure calls, namely positional notation and named notation. It’s up to the programmer to decide which method to use; the compiler doesn’t care one whit.

In daily business I was asked whether both notations are supported through JDBC. The positional notation seems to be familiar, the named notation should be too after reading this article!

The Test Procedure

In PL/SQL the following procedure is defined for test purpose:

PROCEDURE mytest
 Argument Name        Type          In/Out Default?
 -------------------  ------------- ------ --------
 P_MSISDN             VARCHAR2      IN
 P_STATUS             NUMBER(38)    OUT
 P_MESSAGE            VARCHAR2      OUT
 P_LANGUAGE           NUMBER(38)    IN     DEFAULT

Two input parameters are defined, one of them is mandatory, one of them is optional. Two output parameters are defined to get some info back from the called procedure.

Positional Notation

The positional notation is straight forward as it would be in PL/SQL:

StringBuffer sbQuery = new StringBuffer(1024);
Connection con = getConnection();
int language = getLanguage();

// Prepare a PL/SQL call
sbQuery.append( "{call mytest( ?, ?, ?" );
if ( language > 0 )
{
  sbQuery.append( ", ?" );
}
sbQuery.append( " )}" );
CallableStatement cstmt = con.prepareCall( sbQuery.toString() );

// 1st parameter is the MSISDN (input parameter)
cstmt.setString( 1, "41793333333" );

// 2nd parameter is the returned status (output parameter)
cstmt.registerOutParameter( 2, OracleTypes.INTEGER );

// 3nd parameter is the returned message (output parameter)
cstmt.registerOutParameter( 3, OracleTypes.VARCHAR );

// 4th parameter is the language code (optional input parameter)
if ( language > 0 )
{
  cstmt.setInt( 4, language );
}
cstmt.execute();

// Get the returned status
System.out.println(cstmt.getInt( 2 ));

// Get the returned message
System.out.println(cstmt.getString( 3 ));

The order of the parameters is given by the stored procedure. The optional input parameter p_language is only used, if the language is greater than zero.

Named Notation

Similar to PL/SQL the named notation can be used in JDBC:

StringBuffer sbQuery = new StringBuffer(1024);
Connection con = getConnection();
int language = getLanguage();

// Prepare a PL/SQL call
sbQuery.append( "{call mytest( p_msisdn => ?" );
sbQuery.append( ", p_message => ?" );
sbQuery.append( ", p_status => ?" );
if ( language > 0 )
{
  sbQuery.append( ", p_language => ?" );
}
sbQuery.append( " )}" );
CallableStatement cstmt = con.prepareCall( sbQuery.toString() );

// 1st parameter is the MSISDN (input parameter)
cstmt.setString( 1, "41793333333" );

// 2nd parameter is the returned message (output parameter)
cstmt.registerOutParameter( 2, OracleTypes.VARCHAR );

// 3rd parameter is the returned status (output parameter)
cstmt.registerOutParameter( 3, OracleTypes.INTEGER );

// 4th parameter is the language code (optional input parameter)
if ( language > 0 )
{
  cstmt.setInt( 4, language );
}
cstmt.execute();

// Get the returned message
System.out.println(cstmt.getString( 2 ));

// Get the returned status
System.out.println(cstmt.getInt( 3 ));

As you can see, this technique prefixes each argument with the name of the parameter, followed by “=>”, followed by the value. You may have also noticed that the arguments are in arbitrary positions – we don’t have to remember or use the order specified in the called procedure.

Conclusion

The advantage of named notation is the self-documenting code through the obvious assignment of the parameter and its value. Moreover you can define the used parameters in your preferred order without skipping unused parameters with null.

The disadvantage is slightly more typing effort.

If you have a choice, use named notation whenever it’s not obvious what the argument corresponds to. Use positional notation with common utility programs that have only one or two parameters, and their meaning is obvious.