[Top] [Prev] [Next]

## CallableStatement

NOTE: The material in this chapter is based on JDBCtm API Tutorial and Reference, Second Edition: Universal Data Access for the Javatm 2 Platform, published by Addison Wesley as part of the Java series, ISBN 0-201-43328-1.

### 7.1 CallableStatement Overview

A`CallableStatement` object provides a way to call stored procedures in a standard way for all RDBMSs. 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. 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 using the JDBC API is shown here. 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 of the OUT parameters, retrieving values from them, or checking whether a returned value was JDBC `NULL`. Whereas the `getXXX` methods defined in `ResultSet` retrieve values from a result set, the `getXXX` methods in `CallableStatement` retrieve values from the OUT parameters and/or return value of a stored procedure.

#### 7.1.1 Creating a CallableStatement Object

`CallableStatement` objects are created with the `Connection` method `prepareCall`. The following example, in which con is an active JDBC `Connection` object, creates an instance of `CallableStatement`.

>     CallableStatement cstmt = con.prepareCall(
>     	"{call getTestData(?, ?)}");

The variable cstmt contains a call to the stored procedure `getTestData`, which has two input parameters and no result parameter. Whether the `?` placeholders are IN, OUT, or INOUT parameters depends on the stored procedure `getTestData`. This instance of a `CallableStatement` object was created using JDBC 1.0 API; consequently, any query in the stored procedure called by cstmt will produce a default `ResultSet` object (one that is non-scrollable and non-updatable).

The JDBC 2.0 API provides the means to create `CallableStatement` objects that can produce `ResultSet` objects that are scrollable and updatable, as the following code fragment demonstrates.

>     String sql = "{call getTestData(?, ?)}";
>     CallableStatement cstmt2 = con.prepareCall(sql,
>     	ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

The variable cstmt2 contains the same call to the stored procedure `TestData that` cstmt does, but with cstmt2, any `resultSet` objects that `TestData` produces can be updated and are scrollable (though they will not be sensitive to updates made while they are open). Explanations for the constants used to indicate scrollability and updatability are given in the chapter "ResultSet."

#### 7.1.2 IN 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, `setBoolean` to pass in a `boolean`, and so on). Of the programs that use parameters, the vast majority use only IN parameters.

#### 7.1.3 Making Batch Updates

The ability to make batch updates is the same for `CallableStatement` objects as it is for `PreparedStatement` objects. In fact, a `CallableStatement` object is restricted to the same functionality that a `PreparedStatement` object has. More precisely, when using the batch update facility, a `CallableStatement` object can call only stored procedures that take input parameters or no parameters at all. Further, the stored procedure must return an update count. The `CallableStatement.executeBatch` method (inherited from `PreparedStatement`) will throw a `BatchUpdateException` if the stored procedure returns anything other than an update count or takes OUT or INOUT parameters.

The following code fragment illustrates using the batch update facility to associate two sets of parameters with a `CallableStatement` object.

>     CallableStatement cstmt = con.prepareCall(
>     			"{call updatePrices(?, ?)}");
>     cstmt.setString(1, "Colombian");
>     cstmt.setFloat(2, 8.49f);
>     cstmt.addBatch();

>     cstmt.setString(1, "Colombian_Decaf");
>     cstmt.setFloat(2, 9.49f);
>     cstmt.addBatch();

>     int [] updateCounts = cstmt.executeBatch();

The variable cstmt contains a call to the stored procedure `updatePrices` with two sets of parameters associated with it. When cstmt is executed, two update statements will be executed together as a batch: one with the parameters `Colombian` and `8.49f`, and a second one with the parameters `Colombian_Decaf` and `9.49f`. An `f` after a number, as in `8.49f`, tells the Java compiler that the value is a `float`; otherwise, the compiler assumes that a number with decimal digits is a `double` and will not allow it to be used as a `float`.

#### 7.1.4 OUT Parameters

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 SQL type (which the JDBC type represents), not because JDBC requires it. JDBC types, a set of generic SQL type identifiers that represent the most commonly used SQL types, are explained fully in the chapter "Mapping SQL and Java Types" on page 85.

Registering the JDBC type is done with the method `registerOutParameter`. Then after the statement has been executed, `CallableStatement`'s `getXXX` methods can be used to retrieve OUT parameter values. The correct `CallableStatement`.`getXXX` method to use is the type in the Java programming language that corresponds to the JDBC type registered for that parameter. (The standard mapping from JDBC types to Java types is shown in Table 8.1 on page 105.) In other words, `registerOutParameter` uses a JDBC type (so that it matches the data 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 `java.math.BigDecimal` object (with three digits after the decimal point) from the second OUT parameter. The method `executeQuery` is used to execute cstmt because the stored procedure that it calls returns a result set.

>     CallableStatement cstmt = con.prepareCall(
>     	"{call getTestData(?, ?)}");
>     cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
>     cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 3);
>     ResultSet rs = cstmt.executeQuery();
>     // . . . retrieve result set values with rs.getXXX methods
>     byte x = cstmt.getByte(1);
>     java.math.BigDecimal n = cstmt.getBigDecimal(2);

Unlike `ResultSet,` `CallableStatement` does not provide a special mechanism for retrieving large OUT values incrementally. More specifically, it does not have `getXXX` methods for streams of data, such as `getAsciiStream` or `getBinary-Stream.` However, the JDBC 2.0 API provides `CallableStatement` methods for retrieving SQL3 datatypes as OUT or INOUT parameters, which includes the methods `getBlob` and `getClob` for retrieving binary large objects and character large objects.

#### 7.1.5 Numbering of Parameters

When a method takes an `int` specifying which parameter to act upon (`setXXX`, `getXXX`, and `registerOutParameter`), that `int` refers to `?` placeholder parameters only, with numbering starting at one. The parameter number does not refer to literal parameters that might be supplied to a stored procedure call. For example, the following code fragment illustrates a stored procedure call with one literal parameter and one `?` parameter:

>     CallableStatement cstmt = con.prepareCall(
>     		"{call getTestData(25, ?)}");
>     cstmt.registerOutParameter(1, java.sql.Types.TINYINT);

In this code, the first argument to `registerOutParameter`, the `int` `1`, refers to the first `?` parameter (and in this case, the only `?` parameter). It does not refer to the literal `25`, which is the first parameter to the stored procedure.

#### 7.1.6 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 that 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. ("Mapping SQL and Java Types" on page 85 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`. Since the stored procedure called in this example returns an update count, the method `executeUpdate` is used.

>     CallableStatement cstmt = con.prepareCall(
>     		"{call reviseTotal(?)}");
>     cstmt.setByte(1, (byte)25);
>     cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
>     cstmt.executeUpdate();
>     byte x = cstmt.getByte(1);

#### 7.1.7 Retrieve OUT Parameters after Results

Because of limitations imposed by some DBMSs, it is recommended that for maximum portability, all of the results in a `ResultSet` object generated by the execution of a `CallableStatement` object should be retrieved before OUT parameters are retrieved. When all values have been retrieved from a result set, the method `ResultSet.next` will return `false`.

If a `CallableStatement` object returns multiple `ResultSet` objects (which is possible only if it is executed with 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. When all results have been exhausted, the method `getMoreResults` returns `false`, and the method `getUpdateCount` returns `-1`.

After all values have been retrieved from `ResultSet` objects (using `ResultSet.getXXX` methods), and after it has been determined that there are no more update counts, values from OUT parameters can be retrieved (using `CallableStatement`.`getXXX` methods).

#### 7.1.8 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.

[Top] [Prev] [Next]

Copyright © 1999, Sun Microsystems, Inc. All rights reserved.