//   Source File Name: V5SpCli.java  1.3 
//  
//   Licensed Materials -- Property of IBM 
//  
//   (c) Copyright International Business Machines Corporation, 1996, 1997. 
//       All Rights Reserved. 
//  
//   US Government Users Restricted Rights - 
//   Use, duplication or disclosure restricted by 
//   GSA ADP Schedule Contract with IBM Corp. 

//   Sample Program - Client side of Java Stored Procedure Sample 

//   Steps to run the sample: 
//   (1) you must have the "sample" database cataloged on the client 
//   (2) compile the stored procedure (javac V5Stp.java) and copy it 
//       to the sqllib/function directory  
//   (3) compile this java file (javac V5SpCli.java) 
//   (4) run the sample (java V5SpCli [username password]) 

//   NOTES: (1) The CLASSPATH and shared library path environment variables 
//              must be set, as for any JDBC application. 
//          (2) Visit http://www.software.ibm.com/data/db2/java 
//              for current DB2 Java information 

//  For more information about this sample, refer to the README file. 

//  For more information on Programming in Java, refer to the  
//  "Programming in Java" section of the Application Development Guide. 

//  For more information on creating stored procedures, refer to the  
//  "Writing Stored Procedures" section of the Application Development Guide. 

//  For more information on building and running Java programs for DB2, 
//  refer to the "Building Java Applets and Applications" section of the  
//  Application Building Guide. 

//  For more information on the SQL language, refer to the SQL Reference. 

//   Class V5SpCli contains four methods: 
//   (1) registerStoredProc: register the stored procedure 
//   (2) callStoredProc: call the stored procedure 
//   (3) main: application body (register and call the stored procedure) 

import java.sql.*;              //  JDBC classes 
import java.math.*;             //  BigDecimal 
import COM.ibm.db2.jdbc.app.*;  //  DB2 UDB JDBC classes 

class V5SpCli
{
  static
  {
    try
      {
        System.out.println ();
        System.out.println ("  Java Stored Procedure Sample");
        Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
      }
    catch (Exception e)
      {
        System.out.println ("\n  Error loading DB2 Driver...\n");
        e.printStackTrace ();
      }
  }


  //  (1) register the stored procedure 
  public static void registerStoredProc (Connection con,
                                         String name,
                                         String classMethod,
                                         String mode) throws Exception
  {
    try
      {
        //  drop the stored procedure if it exists 
        Statement dropStmt = con.createStatement ();
        dropStmt.executeUpdate ("DROP PROCEDURE " + name);
        dropStmt.close ();
      }
    catch (SQLException e)
      {
        //  ignore this error 
      }

    try
      {
        Statement stmt = con.createStatement ();

        //  construct a parameter list for the stored procedure and 
        //  register it in the system catalogs 
        String parameterList =
          "(in    table         varchar(20)," +
          "  in    modifier      int," +
          "  inout department    varchar(10)," +
          "  out   payrollBefore double," +
          "  out   payrollAfter  double," +
          "  out   avgSalBefore  double," +
          "  out   avgSalAfter   double," +
          "  out   numOfUpdates  int," +
          "  out   medianEmpName varchar(40))";

        System.out.println ("\n  Registering Java stored procedure " + name +
                            "\n     as " + classMethod +
                            "\n     in " + mode + " mode");

        stmt.executeUpdate ("CREATE PROCEDURE " + name + parameterList +
                            " LANGUAGE JAVA " +
                            " PARAMETER STYLE DB2GENERAL " + mode +
                            " EXTERNAL NAME '" + classMethod + "'");
        stmt.close ();
      }
    catch (SQLException e)
      {
        System.out.println ("\n  Error received registering stored procedure");
        throw e;
      }
  }


  //  (2) call the requested stored procedure and display results 
  public static void callStoredProc (Connection con,
                                     String tableName,
                                     String name,
                                     int percentModification,
                                     String department) throws Exception
  {
    //  prepare the CALL statement 
    CallableStatement stmt;
    String sql = "Call " + name + "(?,?,?,?,?,?,?,?,?) ";
    stmt = con.prepareCall (sql);

    //  register the output parameters 
    stmt.registerOutParameter (4, Types.DOUBLE);
    stmt.registerOutParameter (5, Types.DOUBLE);
    stmt.registerOutParameter (6, Types.DOUBLE);
    stmt.registerOutParameter (7, Types.DOUBLE);
    stmt.registerOutParameter (8, Types.INTEGER);
    stmt.registerOutParameter (9, Types.CHAR);

    //  set all parameters (input and output) 
    double totalPayrollBefore = 0.00;
    double totalPayrollAfter = 0.00;
    double averageSalaryBefore = 0.00;
    double averageSalaryAfter = 0.00;
    String medianEmployeeName = "This field is not defined yet";
    int numberOfUpdates = 0;
    stmt.setString (1, tableName);
    stmt.setInt (2, percentModification);
    stmt.setString (3, department);
    stmt.setDouble (4, totalPayrollBefore);
    stmt.setDouble (5, totalPayrollAfter);
    stmt.setDouble (6, averageSalaryBefore);
    stmt.setDouble (7, averageSalaryAfter);
    stmt.setInt (8, numberOfUpdates);
    stmt.setString (9, medianEmployeeName);

    //  call the stored procedure 
    System.out.println ("\n  Calling stored procedure: " + name);
    stmt.execute ();
    System.out.println ("\n  Returned from stored procedure: " + name);

    //  retrieve output parameters 
    BigDecimal totPayBefore = new BigDecimal (stmt.getDouble (4));
    BigDecimal totPayAfter = new BigDecimal (stmt.getDouble (5));
    BigDecimal avgSalBefore = new BigDecimal (stmt.getDouble (6));
    BigDecimal avgSalAfter = new BigDecimal (stmt.getDouble (7));
    numberOfUpdates = stmt.getInt (8);
    medianEmployeeName = stmt.getString (9);

    //  display the information returned from the stored procedure 
    System.out.println ();
    System.out.println ("              Percent modification:   " +
                        percentModification + "%");
    System.out.println ("         Department being modified:   " +
                        department);
    System.out.println ();
    System.out.println ("              Total payroll before: $ " +
                        totPayBefore.setScale (2, totPayBefore.ROUND_HALF_UP));
    System.out.println ("             Average salary before: $ " +
                        avgSalBefore.setScale (2, avgSalBefore.ROUND_HALF_UP));
    System.out.println ();
    System.out.println ("    Number of salary modifications:   " +
                        numberOfUpdates);
    System.out.println ();
    System.out.println ("               Total payroll after: $ " +
                        totPayAfter.setScale (2, totPayAfter.ROUND_HALF_UP));
    System.out.println ("              Average salary after: $ " +
                        avgSalAfter.setScale (2, avgSalAfter.ROUND_HALF_UP));
    System.out.println ();
    System.out.println ("              Median Employee Name:   " +
                        medianEmployeeName);

    stmt.close ();
  }


  //  (3) main application: .connect to the database 
  //                        .register the stored procedure 
  //                        .call the stored procedure 
  public static void main (String argv[])
  {
    Connection con = null;
    try
      {
        String url = "jdbc:db2:sample";
        String callName = "SAMPLESTOREDPROC";
        //  you may specify the schema name 
        //  String callName = "MYSCHEMA.SAMPLESTOREDPROC"; 
        String storedProcName = "V5Stp!salaryModification";
        String mode = "not fenced";
        String table = "EMPLOYEE";
        String department = "C01";
        int percentModification = 5;

        if (argv.length == 0) {
           //  connect with default id/password 
           con = DriverManager.getConnection(url);
           }
        else if (argv.length == 2) {
           String userid = argv[0];
           String passwd = argv[1];

           //  connect with user-provided username and password 
           con = DriverManager.getConnection(url, userid, passwd);
           }
        else {
           System.out.println("\nUsage: java V5SpCli [username password]\n");
           System.exit(0);
        }

        //  register the stored procedure 
        registerStoredProc (con, callName, storedProcName, mode);

        //  call the stored procedure 
        callStoredProc (con, table, callName, percentModification, department);

        con.close ();
      }
    catch (Exception e)
      {
        try { con.close(); } catch (Exception x) { }
        e.printStackTrace ();
      }
  }
}