JDBCTM guide: getting started with 7-CallableStatement

7-CallableStatement
This overview is taken from JDBCTM Database Access from JavaTM: A Tutorial and Annotated Reference. JavaSoft is currently preparing this book. This book is a tutorial and an important reference manual for JDBC. It will be published by Addison-Wesley in the spring of 1997 as part of the Java series.


7.1 Overview
The CallableStatement object provides a standard method for calling stored procedures for all DBMS systems. Stored procedures are stored in the database. The call to a stored procedure is the content contained in the CallableStatement object. This call is written using a code-changing syntax. There are two forms: one with result parameters, the other form does not contain result parameters (for information about the code-changing syntax, see section 4th "statements "). The result parameter is an output (OUT) parameter, which is the return value of the stored process. The two types can contain variable input (IN parameter), output (OUT parameter), or input and output (INOUT parameter) parameters. The question mark is used as a placeholder for the parameter.

The syntax for calling stored procedures in JDBC is as follows. Note that square brackets indicate that the content is optional; square brackets themselves are not part of the syntax.

{Call process name [(?, ?, ...)]}

The syntax of the returned result parameter is as follows:

{? = Call process name [(?, ?, ...)]}

The syntax for stored procedures without parameters is similar:

{Call process name}

Generally, the person who creates the CallableStatement object should know that the DBMS used supports stored procedures and what these processes are. However, multiple DatabaseMetaData methods can provide such information if you need to check. For example, if the DBMS supports calling stored procedures, the supportsStoredProcedures method returns true, and the getProcedures method returns a description of the stored procedure.

CallableStatement inherits the Statement method (they are used to process General SQL statements) and the PreparedStatement method (they are used to process IN parameters ). All methods defined in CallableStatement are used to process the output part of the OUT or INOUT parameters: register the JDBC Type of the OUT parameter (General SQL type), and retrieve results from these parameters, or check whether the returned value is jdbc null.


7.1.1 create a CallableStatement object
The CallableStatement object is created using the Connection method prepareCall. The following example creates a CallableStatement instance, which contains a call to the stored procedure getTestData. This process has two variables but does not include the result parameters:

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

Where? The placeholder is the IN, OUT, or INOUT parameter, depending on the stored process getTestData.


7.1.2 IN and OUT parameters
The IN parameter is passed to the CallableStatement object through the setXXX method. This method is inherited from PreparedStatement. The type of the input parameter determines the setXXX method used (for example, using setFloat to pass in the float value ).

If the OUT parameter is returned for a stored procedure, the JDBC Type of each OUT parameter must be registered before the CallableStatement object is executed (this is required because some DBMS require the JDBC Type ). You can use the registerOutParameter method to register the JDBC Type. After the statement is executed, the getXXX method of CallableStatement retrieves the parameter value. The correct getXXX method is the Java type corresponding to the JDBC Type registered by each parameter (for the standard ing from the JDBC Type to the Java type, see the table in section 8.6.1 ). In other words, registerOutParameter uses the JDBC Type (so it matches the JDBC Type returned by the database), and getXXX converts it to the Java type.

As an example, the following code first registers the OUT parameter, executes the stored procedure called by cstmt, and then retrieves the value returned in the OUT parameter. The getByte method extracts a Java byte from the first OUT parameter, while getBigDecimal extracts a BigDecimal object from the second OUT parameter (three digits after the decimal point ):

CallableStatement cstmt = con. prepareCall (
"{Call getTestData (?, ?)} ");
Cstmt. registerOutParameter (1, java. SQL. Types. TINYINT );
Cstmt. registerOutParameter (2, java. SQL. Types. DECIMAL, 3 );
Cstmt.exe cuteQuery ();
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 in incremental mode.


7.1.3 INOUT Parameters
Besides calling the registerOutParameter method, INOUT parameters that support both input and output are also required to call the appropriate setXXX method (this method is inherited from PreparedStatement ). The setXXX method sets the parameter value as the input parameter, while the registerOutParameter method registers its JDBC Type as the output parameter. The setXXX method provides a Java value, which the driver first converts to a JDBC value and then sends it to the database.

The JDBC Type of this IN value should be the same as the JDBC Type provided to the registerOutParameter method. Then, to retrieve the output value, use the corresponding getXXX method. For example, if the Java type is byte, you should use the setByte method to assign the input value. The JDBC Type of TINYINT type should be provided to registerOutParameter, getByte should also be used to retrieve output values (section 8th "ing between JDBC and Java types" will provide detailed information and type ing tables ).

In the next example, there is a stored procedure reviseTotal. Its unique parameter is the INOUT parameter. The method setByte sets this parameter to 25, and the driver sends it to the database as the jdbc tinyint type. Then, registerOutParameter registers the parameter as jdbc tinyint. After the stored procedure is executed, a new jdbc tinyint value is returned. The getByte method retrieves the new value as a Java byte type.

CallableStatement cstmt = con. prepareCall (
"{Call reviseTotal (?)} ");
Cstmt. setByte (1, 25 );
Cstmt. registerOutParameter (1, java. SQL. Types. TINYINT );
Cstmt.exe cuteUpdate ();
Byte x = cstmt. getByte (1 );

7.1.4 first retrieve the result and then retrieve the OUT Parameter
Due to the limitations of some DBMS, in order to achieve maximum portability, we recommend that you first retrieve the results produced by executing the CallableStatement object, and then use the CallableStatement. getXXX method to retrieve OUT parameters.

If the CallableStatement object returns multiple ResultSet objects (by calling the execute method), all results should be retrieved before the OUT parameter is retrieved. In this case, to ensure that all results are accessed, the Statement method getResultSet, getUpdateCount, and getMoreResults must be called until no results are available.

After retrieving all the results, you can use the CallableStatement. getXXX method to retrieve the value in the OUT parameter.


7.1.5 retrieve the NULL value as the OUT Parameter
The value returned to the OUT parameter may be jdbc null. In this case, the jdbc null value is converted so that the value returned by the getXXX method is null, 0, or false, depending on the type of the getXXX method. For a ResultSet object, you must know whether 0 or false is the only method that originates from jdbc null. The method wasNull is used for detection. If the last value read by the getXXX method is jdbc null, this method returns true; otherwise, flase is returned. Section 5th "ResultSet" provides detailed information.