skip to main content
Designing JDBC Applications for Performance Optimization : Selecting JDBC Objects and Methods : Using Parameter Markers as Arguments to Stored Procedures
  

Try DataDirect Drivers Now

Using Parameter Markers as Arguments to Stored Procedures

When calling stored procedures, always use parameter markers for argument markers instead of using literal arguments. JDBC drivers can call stored procedures on the database server either by executing the procedure as a SQL query or by optimizing the execution by invoking a Remote Procedure Call (RPC) directly on the database server. When you execute a stored procedure as a SQL query, the database server parses the statement, validates the argument types, and converts the arguments into the correct data types.
Remember that SQL is always sent to the database server as a character string, for example, {call getCustName(12345)}. In this case, even though the application programmer may have assumed that the only argument to getCustName() was an integer, the argument is actually passed inside a character string to the server. The database server parses the SQL query, isolates the single argument value 12345, and converts the string 12345 into an integer value before executing the procedure as a SQL language event.
By invoking a RPC on the database server, the overhead of using a SQL character string is avoided. Instead, the JDBC driver constructs a network packet that contains the parameters in their native data type formats and executes the procedure remotely.

Case 1: Not Using a Server-Side RPC

In this example, the stored procedure getCustName() cannot be optimized to use a server-side RPC. The database server must treat the SQL request as a normal language event, which includes parsing the statement, validating the argument types, and converting the arguments into the correct data types before executing the procedure.
CallableStatement cstmt = conn.prepareCall("call getCustName(12345)");
ResultSet rs = cstmt.executeQuery();

Case 2: Using a Server-Side RPC

In this example, the stored procedure getCustName() can be optimized to use a server-side RPC. Because the application avoids literal arguments and calls the procedure by specifying all arguments as parameters, the JDBC driver can optimize the execution by invoking the stored procedure directly on the database as an RPC. The SQL language processing on the database server is avoided and execution time is greatly improved.
CallableStatement cstmt = conn.prepareCall("call getCustName(?)}");
cstmt.setLong(1,12345);
ResultSet rs = cstmt.executeQuery();