The CallableStatement Interface

The CallableStatement Interface

PreparedStatement, which inherits from the Statement interface, provides a faster and more powerful means of executing SQL statements. The CallableStatement interface is a subinterface of the PreparedStatement interface. The CallableStatement provides a different kind of support, namely a means to execute applications and programs that reside in the database itself. The applications on the database side are normally functions and stored procedures.

Server-side application development is supported by all the leading database vendors because this is carried out using SQL and has a tremendous advantage in terms of performance. Java applications using JDBC can now leverage these functions and stored procedures by directly calling them using the CallableStatement interface. The JDBC driver implements the CallableStatement interface. An added feature of PreparedStatement is the ability to receive a return value from the called stored procedure.

Methods

The prepareCall() method of the Connection object is used to create a CallableStatement object. The prepareCall() method takes the command to execute the stored procedure as a parameter. Stored procedures are executed by using the keyword CALL. The parameters for the stored procedure are replaced by the ? placeholders. The CallableStatement interface provides methods that set the parameters for the stored procedure. A code snippet follows:

 CallableStatement myCStmt =                            myConn.prepareCall("? = {CALL MY_DB_ST_PROC(?, ?)}"); 

where ? is a placeholder for the parameter to be passed to the stored procedure. Because the stored procedure returns a value, there is an additional placeholder to retrieve the returned value, and it is placed before the CALL keyword.

Because the CallableStatement interface is a subinterface of the PreparedStatement interface, it inherits the methods of the PreparedStatement interface. Some of the methods that you looked at earlier for the Statement object are in the following list:

• execute()

• executeQuery()

• executeUpdate()

• getResultSet()

• setXXX(index, data) for each data type

• close()

• registerOutParameter(parameterIndex, sqlType)

The registerOutParameter() method is used to identify the datatype of the returned value from the stored procedure. The registerOutParameter() method must be called before the CallableStatement is executed. It takes two parameters: the parameterIndex of the parameter whose type is to be registered and the constant for the SQL datatype defined in the java.sql.Types class. See the following code snippet for an example.

 // prepare the CallableStatement  CallableStatement myCStmt =                        myConn.prepareCall("? = {CALL MY_DB_ST_PROC(?, ?)}"); // register the datatype for the out parameter myCStmt.registerOutParameter(1, java.sql.Types.VARCHAR); 

The getXXX() methods are a set of retrieval methods for different data types that are similar to the setXXX() methods. The getXXX() methods are used to retrieve the result data from the stored procedure being called. For example, an Oracle-stored procedure accepts parameters IN, OUT, and INOUT. The getXXX() methods are used to retrieve the OUT parameter data. The getXXX() methods accept the parameter index (of the placeholder) as a parameter. Here is a code snippet to illustrate the concept.

 // prepare the CallableStatement  CallableStatement myCStmt =                         myConn.prepareCall("? = {CALL MY_DB_ST_PROC(?, ?)}"); // register the datatype for the out parameter myCStmt.registerOutParameter(1, java.sql.Types.VARCHAR); // execute the Query myCStmt.executeQuery(); // retrieve the returned value from the stored procedure String retVal = myCStmt.getString(1); 

This sums up the different ways that you can interact with the database to insert, update, and delete data. In the next section you will explore different ways to retrieve data from the database using the ResultSet interface.