Statements::JAVA3

The CallableStatement Interface

The CallableStatement interface extends PreparedStatement with methods
for executing and retrieving results from stored procedures.

Creating a CallableStatement Object

As with Statement and PreparedStatement objects, CallableStatement objects are created by Connection objects. CODE EXAMPLE 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 Creating a CallableStatement object All the examples in this chapter use the escape syntax for calling stored procedures.

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 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 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 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 Specifying two input parameters to a stored procedureAdditional 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.

IN Parameters

IN parameters are assigned values using the setter methods as described in “Setting
Parameters” In CODE EXAMPLE a string parameter and a date parameter are set.

cstmt.setString(1, “October”);
cstmt.setDate(2, date);
CODE EXAMPLE 13-21 Setting IN parameters

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 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 Registering and retrieving OUT parameters

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 and the type supplied to the method registerOutParameter must be the same.

CODE EXAMPLE 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 Executing a CallableStatement object with an INOUT parameter

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.

Returning a Single ResultSet Object

CODE EXAMPLE shows the execution of a CallableStatement object 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 Executing a CallableStatement object that returns a single resultset
The method executeQuery throws an SQLException if the stored procedure does not return a ResultSet object.

Returning a Row Count

CODE EXAMPLE 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 Executing a CallableStatement object returning an update count If the stored procedure does not return a row count, the method executeUpdate throws an SQLException.

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 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();
Chapter 13 Statements 108
while (true);

CODE EXAMPLE 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 ResultSet object 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);
...