Java Reference
In-Depth Information
import java.sql.*;
public class CallableStmt{
public static void main(String args[]){
int orderNo;
String name;
String storedProc = "create procedure SHOW_ORDERS_BY_STATE "+
"@State CHAR (2) "+
"as "+
"select c.Last_Name+', '+c.First_Name AS Name,"+
"o.Order_Number "+
"from CUSTOMERS c, ORDERS o "+
"where c.Customer_Number = o.Customer_Number "+
"AND c.State = @State "+
"order by c.Last_Name;";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection ("jdbc:odbc:Customers");
Statement stmt = con.createStatement();
stmt.executeUpdate(storedProc);
CallableStatement cs = con.prepareCall("{call SHOW_ORDERS_BY_STATE(?)}");
cs.setString(1,"NJ");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
name = rs.getString("Name");
orderNo = rs.getInt("Order_Number");
System.out.println(name+": "+orderNo);
}
}
catch(ClassNotFoundException e){
e.printStackTrace();
}
catch(SQLException e){
e.printStackTrace();
}
}
}
Notice the JDBC escape sequence used to call the stored procedure. This allows
stored procedures to be called in a standard way for all database management
systems.
