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
  cs1.executeUpdate();

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
  cs3.setXXX(...);
  cs3.addBatch();

// Close onather copy of the call statement
  cs3.setXXX(...);
  cs3.addBatch();

// Execute all copies of the call statement in batch mode
  cs3.executeBatch();

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);

Last update: 2015.

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 Installing and Running Java DB - Derby

 Derby (Java DB) JDBC Driver

 Derby (Java DB) JDBC DataSource Objects

 Java DB (Derby) - DML Statements

 Java DB (Derby) - ResultSet Objects of Queries

 Java DB (Derby) - PreparedStatement

 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

 Oracle Express Edition Installation on Windows

 Oracle JDBC Drivers

 Oracle - Reference Implementation of JdbcRowSet

 Oracle - PreparedStatement

 Oracle - JBDC CallableStatement

 Oracle CLOB (Character Large Object) - TEXT

 Oracle BLOB (Binary Large Object) - BLOB

 Microsoft SQL Server 2005 Express Edition

 Microsoft JDBC Driver for SQL Server - sqljdbc42.jar

 Microsoft JDBC Driver - Query Statements and Result Sets

 Microsoft JDBC Driver - DatabaseMetaData Object

 Microsoft JDBC Driver - DDL Statements

 Microsoft JDBC Driver - DML Statements

 SQL Server - PreparedStatement

 SQL Server CLOB (Character Large Object) - TEXT

 SQL Server BLOB (Binary Large Object) - BLOB

 JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver

 JDBC-ODBC Bridge Driver - Flat Text Files

 JDBC-ODBC Bridge Driver - MS Access

 JDBC-ODBC Bridge Driver - MS SQL Server

 Summary of JDBC Drivers and Database Servers

 Additional Tutorial Notes to Be Added

 Outdated Tutorials

 References

 PDF Printing Version