| Contents | Prev | Next | JDBCTM Guide: Getting Started |
CallableStatement object provides a way to call stored procedures in a standard
way for all DBMSs. A stored procedure is stored in a database; the call to the stored
procedure is what a CallableStatement object contains. This call is written in an
escape syntax that may take one of two forms: one form with a result parameter,
and the other without one. (See Section 4, "Statement," for information on escape
syntax.) A result parameter, a kind of OUT parameter, is the return value for the
stored procedure. Both forms may have a variable number of parameters used for
input (IN parameters), output (OUT parameters), or both (INOUT parameters). A
question mark serves as a placeholder for a parameter.
The syntax for invoking a stored procedure in JDBC is shown below. Note that the square brackets indicate that what is between them is optional; they are not themselves part of the syntax.
{call procedure_name[(?, ?, ...)]}
The syntax for a procedure that returns a result parameter is:
{? = call procedure_name[(?, ?, ...)]}
The syntax for a stored procedure with no parameters would look like this:
{call procedure_name}
Normally, anyone creating a CallableStatement object would already know
that the DBMS being used supports stored procedures and what those procedures
are. If one needed to check, however, various DatabaseMetaData methods will
supply such information. For instance, the method supportsStoredProcedures
will return true if the DBMS supports stored procedure calls, and the method
getProcedures will return a description of the stored procedures available.
CallableStatement inherits Statement methods, which deal with SQL statements in general, and it also inherits PreparedStatement methods, which deal
with IN parameters. All of the methods defined in CallableStatement deal with
OUT parameters or the output aspect of INOUT parameters: registering the
JDBC types (generic SQL types) of the OUT parameters, retrieving values from
them, or checking whether a returned value was JDBC NULL.
CallableStatement objects are created with the Connection method prepareCall.
The example below creates an instance of CallableStatement that contains a call to
the stored procedure getTestData, which has two arguments and no result parameter:
CallableStatement cstmt = con.prepareCall(
"{call getTestData(?, ?)}");
Whether the ? placeholders are IN, OUT, or INOUT parameters depends on
the stored procedure getTestData.
CallableStatement object is done using the
setXXX methods inherited from PreparedStatement. The type of the value being
passed in determines which setXXX method to use (setFloat to pass in a float
value, and so on).
If the stored procedure returns OUT parameters, the JDBC type of each OUT
parameter must be registered before the CallableStatement object can be executed. (This is necessary because some DBMSs require the JDBC type.) Registering the JDBC type is done with the method registerOutParameter. Then after
the statement has been executed, CallableStatement's getXXX methods retrieve
the parameter value. The correct getXXX method to use is the Java type that corresponds to the JDBC type registered for that parameter. (The standard mapping
from JDBC types to Java types is shown in the table in Section 8.6.1.) In other
words, registerOutParameter uses a JDBC type (so that it matches the JDBC
type that the database will return), and getXXX casts this to a Java type.
To illustrate, the following code registers the OUT parameters, executes the
stored procedure called by cstmt, and then retrieves the values returned in the
OUT parameters. The method getByte retrieves a Java byte from the first OUT
parameter, and getBigDecimal retrieves a BigDecimal object (with three digits
after the decimal point) from the second OUT parameter:
CallableStatement cstmt = con.prepareCall(
"{call getTestData(?, ?)}");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 3);
cstmt.executeQuery();
byte x = cstmt.getByte(1);
java.math.BigDecimal n = cstmt.getBigDecimal(2, 3);
Unlike ResultSet, CallableStatement does not provide a special mechanism
for retrieving large OUT values incrementally.
setXXX method (inherited from PreparedStatement) in addition to a call to the method registerOutParameter. The
setXXX method sets a parameter's value as an input parameter, and the method
registerOutParameter registers its JDBC type as an output parameter. The setXXX method provides a Java value which the driver converts to a JDBC value
before sending it to the database.
The JDBC type of this IN value and the JDBC type supplied to the method
registerOutParameter should be the same. Then to retrieve the output value, a
corresponding getXXX method is used. For example, a parameter whose Java type
is byte should use the method setByte to assign the input value, should supply a
TINYINT as the JDBC type to registerOutParameter, and should use getByte to
retrieve the output value. (Section 8, "Mapping JDBC and Java Types," gives
more information and contains tables of type mappings.)
The following example assumes that there is a stored procedure reviseTotal
whose only parameter is an INOUT parameter. The method setByte sets the
parameter to 25, which the driver will send to the database as a JDBC TINYINT.
Next registerOutParameter registers the parameter as a JDBC TINYINT. After the
stored procedure is executed, a new JDBC TINYINT value is returned, and the
method getByte will retrieve this new value as a Java byte.
CallableStatement cstmt = con.prepareCall(
"{call reviseTotal(?)}");
cstmt.setByte(1, 25);
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.executeUpdate();
byte x = cstmt.getByte(1);
CallableStatement object should be retrieved before OUT parameters are retrieved using
CallableStatement.getXXX methods.
If a CallableStatement object returns multiple ResultSet objects (using a
call to the method execute), all of the results should be retrieved before OUT
parameters are retrieved. In this case, to be sure that all results have been
accessed, the Statement methods getResultSet, getUpdateCount, and getMoreResults need to be called until there are no more results.
After this is done, values from OUT parameters can be retrieved using the
CallableStatement.getXXX methods.
JDBC NULL. When this happens, the
JDBC NULL value will be converted so that the value returned by a getXXX method
will be null, 0, or false, depending on the getXXX method type. As with ResultSet objects, the only way to know if a value of 0 or false was originally JDBC NULL
is to test it with the method wasNull, which returns true if the last value read by a
getXXX method was JDBC NULL and false otherwise. Section 5, "ResultSet," contains more information.