CALLABLESTAEMENT | Java J2EE Tutorial pdf

CALLABLESTAEMENT

A CallableStatement object provides a way to call stored procedures in a standard way for all RDBMS. A stored procedure is stored in a database; the call to the stored procedure is what a CallableStatement object contains. This call is written in an escape syntax that may take one of two forms: one form with a result parameter and the other without one. A result parameter, a kind of OUT parameter, is the return value for the stored procedure. Both forms may have a variable number of parameters used for input (IN parameters), output (OUT parameters), or both (INOUT parameters). A question mark (?) serves as a placeholder for a parameter.
The syntax for invoking a stored procedure using the JDBC API is shown here.
=>  Calling a stored procedure with no parameters:
=>  {call procedure_name}
=>  Calling a stored procedure with one or more IN, OUT, or INOUT parameters:
=>  {call procedure_name(?, ?, ...)}
=>  Calling a procedure that returns a result parameter and may or may not take any IN, OUT, or INOUT parameters: (Note that the square brackets indicate that what is between them is optional; they are not themselves part of the syntax.)
=>  {? = call procedure_name[(?, ?, ...)]}
IN Parameters
Passing in any IN parameter values to a CallableStatement object is done using setter methods.
These methods include both the setter methods inherited from the PreparedStatement interface and those defined in the CallableStatement interface. The type of the value being passed in determines which setter method to use (setFloat to pass in a float value, setBoolean to pass in a boolean, and so on).
The following code fragment uses the setter methods that take the parameter number to indicate which parameter is to be set.
String sql = "{call updateStats(?, ?)}";
CallableStatement cstmt = con.prepareCall(sql);
cstmt.setInt(1, 398);
cstmt.setDoublel(2, 0.04395);
OUT Parameters
If the stored procedure returns OUT parameters, the data type of each OUT parameter must be registered before the CallableStatement object can be executed. This is necessary because some DBMSs require the SQL type (which the JDBC type represents); the JDBC API itself does not require that the SQL type be registered. JDBC types, a set of generic SQL type identifiers that represent the most commonly used SQL types.
Registering the JDBC type is done with the method registerOutParameter. Then after the statement has been executed, the CallableStatement interface's getter methods can be used to retrieve OUT parameter values. The correct getter method to use is the type in the Java programming language that corresponds to the JDBC type registered for that parameter.
CallableStatement cstmt = con.prepareCall(
"{call getTestData(?, ?)}");
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.DECIMAL);
ResultSet rs = cstmt.executeQuery();
INOUT Parameters
A parameter that supplies input as well as accepts output (an INOUT parameter) requires a call to the appropriate setter method in addition to a call to the method registerOutParameter. The setter method sets a parameter's value as an input parameter, and the method registerOutParameter registers its JDBC type as an output parameter. The setter method provides a Java value that the driver converts to a JDBC value before sending it to the database.
The JDBC type of this IN value and the JDBC type supplied to the method registerOutParameter should be the same. If they are not the same, they should at least be types that are compatible, that is, types that can be mapped to each other. Then, to retrieve the output value, a corresponding getter method is used.
CallableStatement cstmt = con.prepareCall(
"{call reviseTotal(?)}");
cstmt.setByte(1, (byte)25);
cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.executeUpdate();
byte x = cstmt.getByte(1);
for example
import java.sql.*;
public class calldemo {
public static void main(String a[]) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:TEST");
CallableStatement ps=con.prepareCall("{ call pdemo(?,?)}");
ps.setInt(1,2);
ps.registerOutParameter(2,java.sql.Types.VARCHAR);
ps.executeUpdate();
String s=ps.getString(2);
System.out.println(s);
}
}
Stored procedure
CREATE PROCEDURE pdemo (@empid int,@empname varchar(10) out)
AS
set @empname=(select ename from employee where eid=@empid)
Table
ename          city             eid
---------- ---------- -----------
emp1          city1          1
emp2          city2          2
emp3          city3          3
output