Simple Program Of Callable Statement In Java - 2

public interface CallableStatement

extends PreparedStatement

The interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.

   {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

   {call <procedure-name>[(<arg1>,<arg2>, ...)]}

IN parameter values are set using the set methods inherited from PreparedStatement. The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the getmethods provided here.

A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement.

For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters.

Example - 2

import java.sql.*;
/**  
 * @author Sagar
 */
public class CallableStatementEx2 {
public static void main(String s[]) throws Exception {
  Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
  Connection con=DriverManager.getConnection(
  "jdbc:oracle:thin:@192.168.1.123:1521:XE","scott","tiger");
  CallableStatement cs= con.prepareCall("{call getBalance(?,?)}");
  cs.setInt(1, Integer.parseInt(s[0]));
  cs.registerOutParameter(2, Types.DOUBLE);
  cs.execute();
  System.out.println("Balance : "+ cs.getDouble(2));
  con.close();
 }//main
}//class

Example - 3

import java.sql.*;
import java.util.*;
/**
 * @author Sagar
 */
public class CallableStatementEx3 {
public static void main(String s[]) throws Exception {

  Properties p=new Properties();
  p.put("user","scott");
  p.put("password","tiger");

  oracle.jdbc.driver.OracleDriver 
  od=new oracle.jdbc.driver.OracleDriver();
   Connection con=od.connect ("jdbc:oracle:thin:@192.168.1.123:1521:XE",p);

  CallableStatement cs=con.prepareCall ("{call ?:=getBalanceF(?)}");
  cs.registerOutParameter (1, Types.DOUBLE);
  cs.setInt(2,Integer.parseInt(s[0]));
  cs.execute();
  System.out.println(cs.getDouble(1));
  con.close();
 }//main
}//class 

Example - 4

import java.sql.*;
import java.util.*;
public class CallableStatementEx4 {
 public static void main(String s[]) throws Exception {
  Properties p=new Properties();
  p.put("user","scott");
  p.put("password","tiger");
  oracle.jdbc.driver.OracleDriver 
  od=new oracle.jdbc.driver.OracleDriver();
   Connection con=od.connect("jdbc:oracle:thin:@192.168.1.123:1521:XE",p);
  CallableStatement cs= 
  con.prepareCall("{call ?:=getAccountDetails(?)}");
  cs.registerOutParameter(1, oracle.jdbc. OracleTypes.CURSOR);
  cs.setInt(2,Integer.parseInt(s[0]));
  cs.execute();
  ResultSet rs=(ResultSet) cs.getObject(1);
  while (rs.next()){
   System.out.print(rs.getInt(1)+"\t");
   System.out.print(rs.getString(2)+"\t");
   System.out.println(rs.getDouble(3));
  }//while
  con.close();
 }//main
}//class