Overview of CallableStatement Objects

This section provides overview information on CallableStatement Objects for stored procedures.

JDBC CallableStatement objects are designed to represent calls to stored procedures in the database server. Several things you need to remember about CallableStatement objects:

1. A CallableStatement object is created by calling the prepareCall() method on a Connection object like:

// Calling a procedure with no procedure parameters
  CallableStatement cs1 = con.prepareCall("CALL Abc()");

// Calling a procedure with some literal parameters
  CallableStatement cs2 = con.prepareCall("CALL Abc(v1, v2, ...)");

2. Place holders (question marks "?") can be used in call statements in the same way as in prepared statements. OUT (output) parameters, INOUT (input and output) parameters, and return parameter must be represented by place holders:

// Calling a procedure with place-holder parameters
  CallableStatement cs3 = con.prepareCall(
    "CALL Abc(v1, v2, ?, ?, ...)");

// Calling a procedure with a return value
  CallableStatement cs4 = con.prepareCall(
    "? = CALL Abc(v1, v2, ?, ?, ...)");

The last format "? = CALL Abc(v1, v2, ?, ?, ...)" does not apply to MySQL, since MySQL procedures are not allowed to return values.

3. If an IN parameter is represented by a place holder, its value should be provided by calling the setXXX() method before the execution of the call statement:

// providing a string value to the first place holder
  cs3.setString(1, "North Pole");

// providing a double value to the second place holder
  cs3.setDouble(2, 3.14);

4. Place holders for OUT, INOUT and return parameters must registered as output parameters with JDBC data types before the execution of the call statement:

// Registering the first place holder as a INTEGER output
  cs4.registerOutParameter(1, java.sql.Types.INTEGER);

// Registering the fourth place holder as a DATE output
  cs4.registerOutParameter(4, java.sql.Types.DECIMAL, 3);

5. If no ResultSet objects is returned from the stored procedure, the call statement should be executed by calling the cs.executeUpdate() method.

// Executing the call statement

6. If one or more ResultSet objects are returned from the store procedure, the call statement should be executed by calling the cs.executeQuery() method. It will return the first ResultSet object, representing the output of the first query statement executed in the stored procedure:

// Executing the call statement
  ResultSet res = cs1.executeQuery();

7. A CallableStatement can also be executed in batch mode similar to a PreparedStatement:

// Close one copy of the call statement

// Close another copy of the call statement

// Execute all copies of the call statement in batch mode

8. After the execution of the call statement, all rows of all returning ResultSet objects should be processed before retrieving any registered output parameters.

// Executing the call statement
  ResultSet res = cs1.executeQuery();
  while (res.next()) {

9. After finishing all ResultSet objects, registered output parameters can be retrieved by using the getXXX() methods:

// Retrieving registered output parameters
  int first = cs4.getInt(1);
  Date four = cs4.getDate(4);

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 MySQL Installation on Windows

 MySQL JDBC Driver (MySQL Connector/J)

 MySQL - PreparedStatement

 MySQL - Reference Implementation of JdbcRowSet

MySQL - JBDC CallableStatement

Overview of CallableStatement Objects

 "CREATE PROCEDURE" - Creating a Simple Procedure

 Creating Procedures with IN and OUT Parameters

 Creating Procedures with INOUT Parameters

 Creating Procedures with Multiple Queries

 Creating CallableStatement Objects with prepareCall()

 Capturing ResultSet with executeQuery()

 Creating CallableStatement Objects with Parameters

 Common Errors with CallableStatement Parameters

 Creating CallableStatement Objects with INOUT Parameters

 Retrieving Multiple ResultSet Objects

 Executing Stored Procedures without Permission

 getProcedures() - Listing Stored Procedures

 MySQL CLOB (Character Large Object) - TEXT

 MySQL BLOB (Binary Large Object) - BLOB

 Using Connection Pool with JDBC

 Archived Tutorials


 Full Version in PDF/EPUB