The CallableStatementInterface
The CallableStatement interface extends PreparedStatement with methods
for executing and retrieving results from stored procedures.
13.3.1 Creating a CallableStatement Object
As with Statement and PreparedStatement objects, CallableStatement
objects are created by Connection objects. CODE EXAMPLE 13-18 shows the creation
of a CallableStatement object for calling the stored procedure ‘validate’, which
has a return parameter and two other parameters.
CallableStatement cstmt = conn.prepareCall(
“{? = call validate(?, ?)}”);
CODE EXAMPLE 13-18 Creating a CallableStatement object
All the examples in this chapter use the escape syntax for calling stored procedures.
See “Stored Procedures” on page 111.
13.3.2 Setting Parameters
CallableStatement objects may take three types of parameters: IN, OUT, and
INOUT. The parameter can be specified as either an ordinal parameter or a named
parameter. A value must be set for each parameter marker in the statement.
The number, type, and attributes of parameters to a stored procedure can be
determined using the DatabaseMetaData method getProcedureColumns.
Parameter ordinals, which are integers passed to the approriate setter method, refer
to the parameter markers (""?"") in the statement, starting at one. Literal parameter
values in the statement do not increment the ordinal value of the parameter markers.
In CODE EXAMPLE 13-19, the two parameter markers have the ordinal values 1 and 2.
CallableStatement cstmt = con.prepareCall(
""{CALL PROC(?, ""Literal_Value"", ?)}"");
cstmt.setString(1, ""First"");
cstmt.setString(2, ""Third"");
CODE EXAMPLE 13-19 Specifying ordinal parameters
Named parameters can also be used to specify specific parameters. This is especially
useful when a procedure has many parameters with default values. Named
parameters can be used to specify only the values that have no default value. The
name of a parameter corresponds to the COLUMN_NAME field returned by
DatabaseMetaData.getProcedureColumns.
In CODE EXAMPLE 13-20, the procedure COMPLEX_PROC takes ten parameters, but
only the first and fifth parameters, PARAM_1 and PARAM_5, are required.
CallableStatement cstmt = con.prepareCall(
""{CALL COMPLEX_PROC(?, ?)}"";
cstmt.setString(""PARAM_1"", ""Price"");
cstmt.setFloat(""PARAM_5"", 150.25);
CODE EXAMPLE 13-20 Specifying two input parameters to a stored procedure
Additional methods in the CallableStatement interface allow parameters to be
registered and retrieved by name.
The DatabaseMetaData.supportsNamedParameters method can be called to
determine if a JDBC driver and underlying data source support specifying named
parameters.
It is not possible to combine setting parameters with ordinals and with names in the
same statement. If ordinals and names are used for parameters in the same
statement, an SQLException is thrown.
Note – In some cases it may not be possible to provide only some of the parameters
for a procedure. For example, if the procedure name is overloaded, the data source
determines which procedure to call based on the number of parameters. Enough
parameters must be provided to allow the data source to resolve any ambiguity.
13.3.2.1 IN Parameters
IN parameters are assigned values using the setter methods as described in “Setting
Parameters” on page 98. In CODE EXAMPLE 13-21, a string parameter and a date
parameter are set.
cstmt.setString(1, “October”);
cstmt.setDate(2, date);
CODE EXAMPLE 13-21 Setting IN parameters
13.3.2.2 OUT Parameters
The method registerOutParameter must be called to set the type for each OUT
parameter before a CallableStatement object is executed. When the stored
procedure returns from execution, it will use these types to set the values for any
OUT parameters.
The values of OUT parameters can be retrieved using the appropriate getter
methods defined in the CallableStatement interface. CODE EXAMPLE 13-22 shows
the execution of a stored procedure with two OUT parameters, a string and float,
and the retrieval of the OUT parameter values.
CallableStatement cstmt = conn.prepareCall(
“{CALL GET_NAME_AND_NUMBER(?, ?)}"");
cstmt.registerOutParameter(1, java.sql.Types.STRING);
cstmt.registerOutParameter(2, java.sql.Types.FLOAT);
cstmt.execute();
// Retrieve OUT parameters
String name = cstmt.getString(1);
float number = cstmt.getFloat(2);
CODE EXAMPLE 13-22 Registering and retrieving OUT parameters
13.3.2.3 INOUT Parameters
Parameters that are both input and output parameters must be both set by using the
appropriate setter method and also registered by calling the
registerOutParameter method. The type implied by the setter method (see
TABLE B-1 in Appendix B “Data Type Conversion Tables‘‘) and the type supplied to
the method registerOutParameter must be the same.
CODE EXAMPLE 13-23 shows the stored procedure calc, which takes one INOUT float
parameter.
CallableStatement cstmt = conn.prepareCall(“{CALL CALC(?)}”);
cstmt.setFloat(1, 1237.98f);
ctsmt.registerOutParameter(1, java.sql.Types.FLOAT);
cstmt.execute();
float f = cstmt.getFloat(1);
CODE EXAMPLE 13-23 Executing a CallableStatement object with an INOUT parameter
13.3.3 Executing a CallableStatement Object
As with Statement and PreparedStatement objects, the method used to execute
a CallableStatement object depends on whether it returns a single ResultSet
object, an update count, or multiple mixed results.
13.3.3.1 Returning a Single ResultSet Object
CODE EXAMPLE 13-24 shows the execution of a CallableStatementobject that takes
one input parameter and returns a single ResultSet object.
CallableStatement cstmt = conn.prepareCall(“{CALL GETINFO(?)}”);
cstmt.setLong(1, 1309944422);
ResultSet rs = cstmt.executeQuery();
// process the results
while (rs.next()) {
...
}
rs.close();
cstmt.close();
CODE EXAMPLE 13-24 Executing a CallableStatement object that returns a single result
set
The method executeQuery throws an SQLException if the stored procedure does
not return a ResultSet object.
13.3.3.2 Returning a Row Count
CODE EXAMPLE 13-25 shows the execution of a CallableStatement object that
returns a row count.
CallableStatement cstmt = conn.prepareCall(“{call GETCOUNT(?)}”);
cstmt.setString(1, “Smith”);
int count = cstmt.executeUpdate();
cstmt.close();
CODE EXAMPLE 13-25 Executing a CallableStatement object returning an update count
If the stored procedure does not return a row count, the method executeUpdate
throws an SQLException.
13.3.3.3 Returning Unknown or Multiple Results
If the type or number of results returned by a CallableStatement object are not
known until run time, the CallableStatement object should be executed with the
method execute. The methods getMoreResults, getUpdateCount, and
getResultSet can be used to retrieve all the results.
The method execute returns true if the first result is a ResultSet object and
false if it is an update count.
When the method execute returns true, the method getResultSet is called to
retrieve the ResultSet object. When execute returns false, the method
getUpdateCount returns an int. If this number is greater than or equal to zero, it
indicates the number of rows that were affected by the statement. If it is -1, it
indicates that there are no more results.
If multiple results are being returned, the method getMoreResults can be called to
get the next result. As with the method execute, getMoreResults will return
true if the next result is a ResultSet object and false if the next result is a row
count or no more result are available.
CODE EXAMPLE 13-26 shows how to retrieve all the results from a
CallableStatment object.
CallableStatement cstmt = conn.prepareCall(procCall);
boolean retval = cstmt.execute();
ResultSet rs;
int count;
do {
if (retval == false) {
count = cstmt.getUpdateCount();
if (count == -1) {
// no more results
break;
} else {
// process row count
}
} else { // ResultSet
rs = cstmt.getResultSet();
// process ResultSet
}
retval = cstmt.getMoreResults();
while (true);
CODE EXAMPLE 13-26 Executing a callable statement that returns multiple results
By default, each call to the method getMoreResults closes any previous
ResultSet object returned by the method getResultSet. However, the method
getMoreResults may take a parameter that specifies whether a ResultSet object
returned by getResultSet should be closed. The Statement interface defines
three constants that can be supplied to the method getMoreResults:
■ CLOSE_CURRENT_RESULT — indicates that the current ResultSetobject should
be closed when the next ResultSet object is returned
■ KEEP_CURRENT_RESULT — indicates that the current ResultSet object should
not be closed when the next ResultSet object is returned
■ CLOSE_ALL_RESULTS — indicates that any ResultSet objects that have been
kept open should be closed when the next result is returned
If the current result is an update count and not a ResultSet object, any parameter
passed to getMoreResults is ignored.
To determine whether a driver implements this feature, an application can call the
DatabaseMetaData method supportsMultipleOpenResults.
ResultSet rs1 = cstmt.getResultSet();
rs1.next();
...
retval = cstmt.getMoreResults(Statement.KEEP_CURRENT_RESULT);
if (retval == true) {
ResultSet rs2 = cstmt.getResultSet();
rs2.next();
...
rs1.next();
}
retval = cstmt.getMoreResults(Statement.CLOSE_ALL_RESULTS);
...
CODE EXAMPLE 13-27 Keeping multiple results from a CallableStatement object open
13.4 Escape Syntax
The SQL string used in a Statement object may include JDBC escape syntax. Escape
syntax allows the driver to more easily scan for syntax that requires special
processing. Implementing this special processing in the driver layer improves
application portability.
Special escape processing might be needed for the following:
■ commonly used features that do not have standard syntax defined by SQL, or
where the native syntax supported by the underlying data source varies widely
among vendors. The driver may translate the escape syntax to a specific native
syntax in this case.
■ features that are not supported by the underlying data source but are
implemented by the driver.
Escape processing for a Statement object is turned on or off using the method
setEscapeProcessing, with the default being on. The RowSet interface also
includes a setEscapeProcessing method. The RowSet method applies to the
SQL string used to populate a RowSet object. The setEscapeProcessing method
does not work for a PreparedStatement object because its SQL string may have
been precompiled when the PreparedStatement object was created.
JDBC defines escape syntax for the following:
■ scalar functions
n date and time literals
n outer joins
■ calling stored procedures
■ escape characters for LIKE clauses
13.4.1 Scalar Functions
Almost all underlying data sources support numeric, string, time, date, system, and
conversion functions on scalar values. The escape syntax to access a scalar function
is:
{fn <function-name> (argument list)}
For example, the following code calls the function concat with two arguments to be
concatenated:
{fn concat(""Hot"", ""Java"")}
The following syntax gets the name of the current database user:
{fn user()}
Scalar functions may be supported by different data sources with slightly different
native syntax, and they may not be supported by all drivers. The driver will either
map the escaped function call into the native syntax or implement the function
directly.
Various DatabaseMetaData methods list the functions that are supported. For
example, the method getNumericFunctions returns a comma-separated list of the
Open Group CLI names of numeric functions, the method getStringFunctions
returns string functions, and so on.
Refer to Appendix C “Scalar Functions"" for a list of the scalar functions a driver is
expected to support. A driver is required to implement these functions only if the
data source supports them, however.
13.4.2 Date and Time Literals
Data sources differ widely in the syntax they use for date, time, and timestamp
literals. The JDBC API supports ISO standard format for the syntax of these literals,
using an escape clause that the driver translates to native syntax.
The escape syntax for date literals is:
{d 'yyyy-mm-dd'}
The driver will replace the escape clause with the equivalent native representation.
For example, the driver might replace {d ’1999-02-28’} with '28-FEB-99' if that
is the appropriate format for the underlying data source.
The escape syntax for TIME and TIMESTAMP literals are:
{t 'hh:mm:ss'}
{ts 'yyyy-mm-dd hh:mm:ss.f . . .'}
The fractional seconds (.f . . .) portion of the timestamp can be omitted.
13.4.3 Outer Joins
Outer joins are an advanced feature and are not supported by all data sources.
Consult relevant SQL documentation for an explanation of outer joins.
The escape syntax for an outer join is:
{oj <outer-join>}
where <outer-join> has the form:
table {LEFT|RIGHT|FULL} OUTER JOIN {table | <outer-join>} ON search-
condition
(Note that curly braces ({}) in the preceding line indicate that one of the items
between them must be used; they are not part of the syntax.) The following SELECT
statement uses the escape syntax for an outer join.
Statement stmt = con.createStatement();
stmt.executeQuery(""SELECT * FROM {oj TABLE1 "" +
""LEFT OUTER JOIN TABLE2 ON DEPT_NO = 003420930}"");
The JDBC API provides three DatabaseMetaData methods for determining the
kinds of outer joins a driver supports: supportsOuterJoins,
supportsFullOuterJoins, and supportsLimitedOuterJoins.
13.4.4 Stored Procedures
If a database supports stored procedures, they can be invoked using JDBC escape
syntax as follows:
{call <procedure_name> [(<argument-list>)]}
or, where a procedure returns a result parameter:
{? = call <procedure_name> [(<argument-list>)]}
The square brackets indicate that the (argument-list) portion is optional. Input
arguments may be either literals or parameter markers. See “Setting Parameters” on
page 103 for information on parameters.
The method DatabaseMetaData.supportsStoredProcedures returns true if the
database supports stored procedures.
