// Client.java

/* This is the sample SQLJ client of the EmployeeByValue stored procedure */

import java.sql.*;
import java.io.IOException;

public class Client
{
  static Connection conn = null;


  public static void main (String args[])
       throws Exception
  {
    if (args.length > 2) {
      System.out.println ("usage: Client [username [password]]");
      System.exit (1);
    }
    String username = args.length > 0 ? args [0] : "scott";
    String password = args.length > 1 ? args [1] : "tiger";

    init("jdbc:oracle:oci8:@", username, password, true);

    System.out.println ("Getting employee number 7788");
    EmployeeInfo emp = getEmployee (7788);
    printEmp (emp);

    System.out.println ("Raising employee number 7788 and his manager by 10%");
    emp.salary += emp.salary * 0.10;
    emp.manager.salary += emp.manager.salary * 0.10;
    updateEmployee (emp);

    System.out.println ("Getting employee number 7788 again");
    emp = getEmployee (7788);
    printEmp (emp);
  }

  public static void printEmp (EmployeeInfo emp) {
    if (emp != null) {
      System.out.println (emp.empno + ": " + emp.ename +
                          " salary " + emp.salary);
      if (emp.manager != null) {
        System.out.print ("  managed by ");
        printEmp (emp.manager);
      }
    }
  }

  public static EmployeeInfo getEmployee (int number)
       throws SQLException, IOException, ClassNotFoundException
  {
    byte[] serial_emp_info = null;
    CallableStatement cs = null;
    
    try{
      cs = conn.prepareCall("begin ? := empbyval.getempinfo(?); end;");
      cs.registerOutParameter(1,Types.VARBINARY);
      cs.setInt(2,number);
      cs.executeUpdate();
      serial_emp_info = cs.getBytes(1);
    }
    finally
    {
      if(cs != null)
        cs.close();
    }
    return (EmployeeInfo)Util.deserializeObject (serial_emp_info);
  }

  public static void updateEmployee (EmployeeInfo emp)
       throws SQLException, IOException
  {
    byte[] serial_emp_info = Util.serializeObject (emp);
    PreparedStatement ps = null;
    
    try{
      ps = conn.prepareStatement("begin empbyval.updateempinfo(?); end;");
      ps.setBytes(1,serial_emp_info);
      ps.executeUpdate();
    }
    finally
    {
      if(ps != null)
        ps.close();
    }
  }

  static void init(String conn_url,String usr, String pwd, boolean ac)
    throws SQLException
  {
    if(conn != null)
      return;
    else
    {
      DriverManager.registerDriver (new oracle.jdbc.OracleDriver ());
      conn = DriverManager.getConnection(conn_url, usr,pwd);
      conn.setAutoCommit(ac);
    }      
  }
}