- CallableStatement
This summary 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 is also an important reference manual for JDBC. It will be part of the Java series in the 1997 Spring by Addison-Wesley Publishing Company.
7.1 Overview
The CallableStatement object provides a method of calling the stored procedure in a standard form for all DBMs. The stored procedure is stored in the database. The call to the stored procedure is the content contained in the CallableStatement object. This call is written in a modified syntax, there are two forms: a form band result parameter, another form without a result parameter (for information on exchange syntax, see Section 4 "Statements") . The result parameters are an output (OUT) parameter, which is the return value of the stored procedure. Both forms can have a number of variable input (IN parameters), output (OUT parameters) or parameters of input and output (INOUT parameters). Question marks will be used as a placeholder of parameters.
The syntax that calls the stored procedure in the JDBC is as follows. Note that square brackets indicate that the content is optional; square brackets are not a part of the grammar.
{CALL process name [(?,?, ...)]}
The syntax of the process of returning the result parameters is:
{? = CALL process name [(?,?, ...)]}
The syntax of the stored procedure without parameters is similar:
{CALL process name}
Typically, people who create a CallableStatement object should know that the DBMS used is to support stored procedures and know what these processes are. However, if you need to check, the variety of DatabaseMetadata methods can provide such information. For example, if DBMS supports a memory that has been stored, the SupportsSstoredProcedures method returns True, and the getProcedures method returns a description of the stored procedure.
CallableStatement inherits the Statement method (they are used to handle a general SQL statement), and inherited the method of PreparedStatement (they are used to process the IN parameters). All methods defined in CallaBleStatement are used to process the output section of the OUT parameter or inout parameter: the JDBC type (general SQL type) of the OUT parameter, retrieve the result from these parameters, or check if the returned value is JDBC NULL.
7.1.1 Creating a CallableStatement object
The CallableStatement object is created with the connection method prepareCall. The following example creates an instance of CallableStatement, which contains the stored process GettestData call. This process has two variables, but does not contain results parameters:
Callablestatement cstmt = con.preparecall
"{CALL GETTESTDATA (?,?)}");
Where? The placeholder is IN, OUT is also inout parameters, depending on the stored procedure.
7.1.2 in and OUT parameters
Pass the in parameter to the CallableStatement object is done through the setxxx method. This method inherits from preparedStatement. The type of incoming parameters determines the setXXX method used (for example, with setfloat to pass the float value, etc.). If the stored procedure returns an OUT parameter, you must register the JDBC type of each OUT parameter before executing the CallableStatement object (this is required because some DBMS requires the JDBC type). The registration of the JDBC type is done with the RegisterOutParameter method. After the statement is executed, the CallableStatement's getxxx method will retrieve the parameter. The correct Getxxx method is a Java type corresponding to the JDBC type registered with each parameter (see a table in Section 8.6.1) from the JDBC type to the Java type. In other words, REGISTEROUTPARAMETER uses a JDBC type (so it matches the JDBC type returned by the database), and Getxxx is converted to the Java type.
As an example, the following code first registers the OUT parameter, performs the stored procedure called by the CSTMT, and then retrieves the value returned in the OUT parameter. Method GetByte removes a Java byte from the first OUT parameter, and getBigDecimal removes a BigDecimal object from the second OUT parameter (three digits behind 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.executeQuery ();
BYTE X = CSTMT.GetByte (1);
Java.math.bigDecimal n = cstmt.getbigDecimal (2, 3);
The CallableStatement is different from the resultset, which does not provide a special mechanism for the large OUT value with an incremental manner.
7.1.3 inout parameters
The parameters that support both input and output (INOUT parameters) are required to call the appropriate setxxxx method (this method is inherited from preparedStatement) in addition to calling the RegisterOutparameter method. SetXXX method Set the parameter value to the input parameters, and the RegisterOutParameter method registers its JDBC type as the output parameter. The SetXXX method provides a java value, and the driver first converts this value to the JDBC value and then sent it to the database.
The JDBC type of this IN value and the JDBC type provided to the RegisterOutparameter method should be the same. Then, to retrieve the output value, use the corresponding getxxx method. For example, the parameter of the Java type is BYTE should use method setByte to assign input values. The JDBC type type Tinyint should be provided to RegisterOutParameter, and GetByte should be used to retrieve the output value (Mapping between Section 8 "JDBC and Java Types" will give details and type mapping tables).
The following example is assumed to have a stored procedure RevisetAl, and the unique parameter is the INOUT parameter. Method SetByte Set this parameter to 25, the driver will send it as a JDBC Tinyint type to the database. Then, RegisterOutparameter registers this parameter as JDBC Tinyint. After performing the stored procedure, a new JDBC Tinyint value will be returned. Method GetByte will retrieve this new value as a Java Byte type. Callablestatement cstmt = con.preparecall
"{CALL RevisetAl (?)}");
CSTMT.SetByte (1, 25);
Cstmt.registerOutparameter (1, java.sql.types.tinyint);
Cstmt.executeUpdate ();
BYTE X = CSTMT.GetByte (1);
7.1.4 Retrieve the result, then retrieve the OUT parameters
Due to certain DBMS limits, in order to achieve maximum portability, it is recommended to retrieve the results generated by the execution of the CallableStatement object, and then retrieve the OUT parameter with the CallableStatement.getxxx method.
If the CallableStatement object returns multiple ResultSet objects (by calling the execute method), all results should be retrieved before retrieving the OUT parameters. In this case, in order to ensure access to all the results, the Statement method getResultSet, GetUpdateCount and GetMoreResults must be called until there is no longer result.
After searching all the results, you can retrieve the values in the OUT parameter with the CallableStatement.getxxx method.
7.1.5 Retrieving NULL values as OUT parameters
The value returned to the OUT parameter may be JDBC NULL. When this situation occurs, the JDBC NULL value will be converted to enable the value returned by the Getxxx method to NULL, 0 or FALSE, depending on the Getxxx method type. For the ResultSet object, you know if 0 or false is the only way to source from JDBC NULL, is to detect with method WASNULL. If the last value read by the getxxx method is a JDBC NULL, then the method returns true, otherwise it returns Flase. Section 5 "ResultSet" will give details.
