Contents | Prev | Next | JDBCTM Guide: Getting Started

# 7 - CallableStatement

This overview is excerpted from JDBCTM Database Access from JavaTM: A Tutorial and Annotated Reference, currently in progress at JavaSoft. This book, both a tutorial and the definitive reference manual for JDBC, will be published in the spring of 1997 by Addison-Wesley Publishing Company as part of the Java series.

## 7.1 Overview

A `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`.

### 7.1.1 Creating a CallableStatement Object

`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`.

### 7.1.2 IN and OUT Parameters

Passing in any IN parameter values to a `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.

### 7.1.3 INOUT Parameters

A parameter that supplies input as well as accepts output (an INOUT parameter) requires a call to the appropriate `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);

### 7.1.4 Retrieve OUT Parameters after Results

Because of limitations imposed by some DBMSs, it is recommended that for maximum portability, all of the results generated by the execution of a `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.

### 7.1.5 Retrieving NULL Values as OUT Parameters

The value returned to an OUT parameter may be `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.

Contents | Prev | Next

Copyright © 1996, 1997 Sun Microsystems, Inc. All rights reserved.