7 - CallableStatement 7.1 Overview The CallableStatement object provides a method for 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: a form of formal ginseng, another form without results parameters (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 [(?,?, ...)]} has stored procedures without parameters Syntax Similar: {CALL process name} Usually, creating a person who creates a CallableStatement object should know what the DBMS used is to support the stored procedure 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 the general SQL statement), and inherited the preparedStatement method (they are used to process IN). 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 Process GettestData. 7.1.2 IN and OUT parameters pass the IN parameter to the CallableStatement object to be done by 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); CallableStatement is different from ResultSet, which does not provide special mechanisms for large OUT values with incremental ways. 7.1.3 The inout parameter supports both the input and receiving parameters (INOUT parameters) In addition to calling the RegisterOutparameter method, the appropriate setxxx method is required (this method is inherited from preparedStatement). 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.
