Original URL : http://www.coling.uni-freiburg.de/java/doku/jdbc-etc/jdbc/jdbc7.html

JDBC: Calling a Stored Procedure

Calls to Stored Procedures and Functions are a bit more involved than SQL queries and updates. These calls are performed using the CallableStatement interface, which is an extension of the PreparedStatement interface. Parameters passed to and from the stored procedure are identified using ? placeholder symbols. These placeholders must be registered as IN our OUT data elements, using data types from the Types class.

Create the Command with Placeholders:

// Stored procedure call
String command = "{call PROCEDURENAME(?,?,?,?)}";     // 4 placeholders

// Stored function call
String command = "{? = call FUNCTIONNAME(?,?,?,?)}";  // 4 placeholders + 1 return value

Create a Callable Statement Object:

CallableStatement cstmt = con.prepareCall (command);

(Throws a SQLException error.)

Register the IN and OUT Placeholders:

// Assign IN parameters (use positional placement)
cstmt.setInt(1, 50);       // first placeholder has an integer value of 50
cstmt.setString(2, "Smith");  // second placeholder has a string value of "Smith"
cstmt.registerOutParameter(3, Types.NUMERIC);    // NUMERIC is preferred over INTEGER with some databases
cstmt.registerOutParameter(4, Types.VARCHAR);    // fourth placeholder is an OUT field of type VARCHAR.

(Throws a SQLException error.)

• Note: INOUT fields may be registered using the setXXXX() and registerOutParameter() methods for the same placeholder position. Array parameters (i.e. PL/SQL Tables) used in Oracle stored procedure and function calls are not supported by the JDBC.

Execute the Procedure or Function Call:

cstmt.execute();

(Throws a SQLException error.)

Process the OUT Placeholders:

/** Use proper getXXXX() method based on parameter data type.
  * Placeholder 3 will receive a NUMERIC value as a BigDecimal object with 0 decimal places.
  * Placeholder 4 will receive a VARCHAR value as a String object.
  */
BigDecimal num = cstmt.getBigDecimal(3,0);
String str = cstmt.getString(4);

(Throws a SQLException error.)

Close the CallableStatement Object:

// Close the call object after update is complete
cstmt.close();

(Throws a SQLException error.)

Next Topic: Accessing Metadata

Home

Last modified 19/09/2000