//*************************************************************************
// (c) Copyright IBM Corp. 2007 All rights reserved.
// 
// The following sample of source code ("Sample") is owned by International 
// Business Machines Corporation or one of its subsidiaries ("IBM") and is 
// copyrighted and licensed, not sold. You may use, copy, modify, and 
// distribute the Sample in any form without payment to IBM, for the purpose of 
// assisting you in the development of your applications.
// 
// The Sample code is provided to you on an "AS IS" basis, without warranty of 
// any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
// IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
// not allow for the exclusion or limitation of implied warranties, so the above 
// limitations or exclusions may not apply to you. IBM shall not be liable for 
// any damages you suffer as a result of using, copying, modifying or 
// distributing the Sample, even if IBM has been advised of the possibility of 
// such damages.
//*************************************************************************
//
// SOURCE FILE NAME: SpClient.java
//
// SAMPLE: Call the set of stored procedures implemented in SpServer.java
//
// Steps to run the sample with command line window:
//         I) If you have a compatible make/nmake program on your system, 
//            do the following:
//            1. Compile the server source file SpServer.java (this will also 
//               compile the utility file, Util.java, erase the existing 
//               library/class files and copy the newly compiled class files, 
//               SpServer.class, from the current directory to the 
//               $(DB2PATH)\function directory):
//                 nmake/make SpServer
//            2. Compile the client source file SpClient.java (this will also 
//               call the script 'spcat' to create and catalog the stored 
//               procedures):
//                 nmake/make SpClient
//            3. Run the client SpClient:
//                 java SpClient
//
//         II) If you don't have a compatible make/nmake program on your 
//             system do the following:
//             1. Compile the utility file and the server source file with:
//                  javac Util.java
//                  javac SpServer.java
//             2. Erase the existing library/class files (if exists), 
//                SpServer.class from the following path,
//                $(DB2PATH)\function. 
//             3. Copy the class files, SpServer.class from the current
//                directory to the $(DB2PATH)\function.
//             4. Catalog the stored procedures in the database with:
//                  spcat
//             5. Compile SpClient with:
//                  javac SpClient.java
//             6. Run SpClient with:
//                  java SpClient
//
// SpClient calls nine methods that call stored procedures:
//  (1) callOutLanguage: Calls a stored procedure that returns the 
//      implementation language of the stored procedure library
//        Parameter types used: OUT CHAR(8)
//  (2) callOutParameter: Calls a stored procedure that returns median 
//      salary of employee salaries
//        Parameter types used: OUT DOUBLE                    
//  (3) callInParameters: Calls a stored procedure that accepts 3 salary 
//      values and updates employee salaries in the EMPLOYEE table based 
//      on these values for a given department.
//        Parameter types used: IN DOUBLE
//                              IN DOUBLE
//                              IN DOUBLE
//                              IN CHAR(3)
//  (4) callInoutParameter: Calls a stored procedure that accepts an input
//      value and returns the median salary of those employees in the
//      EMPLOYEE table who earn more than the input value. Demonstrates how 
//      to use null indicators in a client application. The stored procedure
//      has to be implemented in the following parameter styles for it to be
//      compatible with this client application.
//        Parameter style for a C stored procedure:  SQL
//        Parameter style for a Java(JDBC/SQLJ) stored procedure:  JAVA
//        Parameter style for an SQL stored procedure:  SQL
//        Parameter types used: INOUT DOUBLE
//  (5) callClobExtract: Calls a stored procedure that extracts and returns a 
//      portion of a CLOB data type
//        Parameter types used: IN CHAR(6)
//                              OUT VARCHAR(1000)
//  (6) callDecimalType: Calls a stored procedure that passes and receives a 
//      DECIMAL data type from a stored procedure
//        Parameter types used: INOUT DECIMAL
//  (7) callAllDataTypes: Calls a stored procedure that uses a variety of 
//      common data types (not DECIMAL, GRAPHIC, VARGRAPHIC, BLOB, CLOB, 
//      DBCLOB). This sample shows only a subset of DB2 supported data types.
//      For a full listing of DB2 data types, please see the SQL Reference.
//        Parameter types used: INOUT SMALLINT
//                              INOUT INTEGER
//                              INOUT BIGINT
//                              INOUT REAL
//                              INOUT DOUBLE
//                              OUT CHAR(1)
//                              OUT CHAR(15)
//                              OUT VARCHAR(12)
//                              OUT DATE
//                              OUT TIME
//  (8) callOneResultSet: Calls a stored procedure that returns a result set to
//      the client application
//        Parameter types used: IN DOUBLE
//  (9) callTwoResultSets: Calls a stored procedure that returns two result sets 
//      to the client application
//        Parameter types used: IN DOUBLE
//
// SQL Statements USED:
//         CALL
//         SELECT
//
//                           
// Output will vary depending on the JDBC driver connectivity used.
//*************************************************************************
//
// For more information on the sample programs, see the README file.
//
// For information on developing Java applications see the Developing Java Applications book.
//
// For information on using SQL statements, see the SQL Reference.
//
// For the latest information on programming, compiling, and running DB2
// applications, visit the DB2 Information Center at
//     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
//**************************************************************************/


import java.sql.*;            // JDBC classes           
import java.math.BigDecimal;  // BigDecimal support for packed decimal type

class SpClient
{
  static double outMedian = 0;
  static Db db;
  public static void main(String argv[])
  {
    String language = "";
    
      Connection con = null;
      
      try
      { 
        int prt=Integer.parseInt(argv[1]);
        javax.sql.DataSource ds=null;
        ds=new com.ibm.db2.jcc.DB2SimpleDataSource();
        ((com.ibm.db2.jcc.DB2BaseDataSource) ds).
	setServerName(argv[0]);
        ((com.ibm.db2.jcc.DB2BaseDataSource) ds).
	setPortNumber(prt);
        ((com.ibm.db2.jcc.DB2BaseDataSource) ds).
	setDatabaseName("sample");
        ((com.ibm.db2.jcc.DB2BaseDataSource) ds).
	setDriverType(4);
        ((com.ibm.db2.jcc.DB2BaseDataSource) ds).
	setTraceFile("jcctrace.txt");
        ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setEnableNamedParameterMarkers(1);
        con = ds.getConnection(argv[2],argv[3]);
	System.out.println("  Connect to 'sample' database using JDBC Universal type 4 driver.");
        con.setAutoCommit(false);
      }
      catch (Exception e)
      {
        System.out.println("  Error loading DB2 Driver...\n");
        System.out.println(e);
        System.exit(1);
      }



    try
    {
     

      System.out.println("HOW TO CALL VARIOUS STORED PROCEDURES.\n");
            
      language = callOutLanguage(con);
      callOutParameter(con);
      callInParameters(con);
      
      // call INOUT_PARAM stored procedure using the median returned
      // by the call to OUT_PARAM
      System.out.println("\nCall stored procedure named INOUT_PARAM");
      System.out.println("using the median returned by the call to " + 
                         "OUT_PARAM");
      callInoutParameter(con, outMedian);
      
      // call INOUT_PARAM stored procedure again in order to depict a        
      // NOT FOUND error condition
      System.out.println("\nCALL stored procedure INOUT_PARAM again");
      System.out.println("with an input value that causes a NOT FOUND error");
      callInoutParameter(con, 99999.99);
            
      callClobExtract("000140", con);
      callDecimalType(con);
      callAllDataTypes(con);
      callOneResultSet(con);
      callTwoResultSets(con);

      // roll back any changes to the database made by this sample
      con.rollback();                                   
      con.close();
      System.out.println();
      System.out.println("  Disconnect from 'sample' database.");
    }
    catch (Exception e)
    {
      try
      {
        con.rollback();
        con.close();
      }
      catch (Exception x)
      { }

      e.printStackTrace();
    }
  } // end main

  public static String callOutLanguage(Connection con)
  {
    String outLang = "";
    try
    {
      // prepare the CALL statement for OUT_LANGUAGE
      String procName = "OUT_LANGUAGE";
      String sql = "CALL " + procName + "(:Typchar)";
      CallableStatement callStmt = con.prepareCall(sql);

      // register the output parameter
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("Typchar", Types.CHAR);

      // call the stored procedure
      System.out.println();
      System.out.println("Call stored procedure named " + procName);
      callStmt.execute();

      // retrieve output parameters
      outLang = callStmt.getString(1);
      System.out.println("Stored procedures are implemented in language "
                         + outLang);
                         
      // clean up resources
      callStmt.close();                                                  
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
    }
    return(outLang);
  } // callOutLanguage

  public static void callOutParameter(Connection con)
  {
    try
    {
      // prepare the CALL statement for OUT_PARAM
      String procName = "OUT_PARAM";
      String sql = "CALL " + procName + "(:Typdouble)";
      CallableStatement callStmt = con.prepareCall(sql);

      // register the output parameter                   
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("Typdouble", Types.DOUBLE);
      
      // call the stored procedure                       
      System.out.println();
      System.out.println("Call stored procedure named " + procName);
      
      callStmt.execute();

      // retrieve output parameters                      
      outMedian = callStmt.getDouble(1);
      
      System.out.println(procName + " completed successfully");
      System.out.println("Median salary returned from " + procName + " = "
                         + outMedian);
                         
      // clean up resources
      callStmt.close();                         
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
    }
  } // callOutParameter


  public static void callInParameters(Connection con) throws SQLException
  {
    try
    {
      // prepare the CALL statement for IN_PARAMS
      String procName = "IN_PARAMS";
      String sql = "CALL " + procName + "(:lowsal, :medsal, :hisal, :dept)";
      CallableStatement callStmt = con.prepareCall(sql);
    
      // display total salary before calling IN_PARAMS
      String query = "SELECT SUM(salary) FROM employee WHERE workdept = :dept";
      PreparedStatement queryStmt = con.prepareStatement(query);
      ((com.ibm.db2.jcc.DB2PreparedStatement)queryStmt).setJccStringAtName("dept", "E11");
      ResultSet queryRS = queryStmt.executeQuery();
      queryRS.next();
      double sumSalary = queryRS.getDouble(1);
      queryRS.close();
      System.out.println();
      System.out.println("Sum of salaries for dept. E11 = " +
                         sumSalary + " before " + procName);
     
      // set input parameters
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("lowsal", 15000);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("medsal", 20000);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("hisal", 25000);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccStringAtName("dept", "E11");

      // call the stored procedure
      System.out.println("Call stored procedure named " + procName);
      callStmt.execute();

      System.out.println(procName + " completed successfully");

      // display total salary after calling IN_PARAMS
      queryRS = queryStmt.executeQuery();
      queryRS.next();
      sumSalary = queryRS.getDouble(1);
      queryRS.close();
      System.out.println("Sum of salaries for dept. E11 = "
                         + sumSalary + " after " + procName);
      
      // clean up resources
      queryStmt.close();
      callStmt.close();
    }
    catch (SQLException e)
    {
      // roll back any UPDATE statements issued before the SQLException 
      con.rollback();
      System.out.println(e.getMessage());
    }
  } // callInParameters

  public static void callInoutParameter(Connection con, double median)
  {
    try
    {
      // prepare the CALL statement for INOUT_PARAM
      String procName = "INOUT_PARAM";
      String sql = "CALL " + procName + "(:median)";
      CallableStatement callStmt = con.prepareCall(sql);

      // set input parameter to median value passed back by OUT_PARAM
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("median", median);

      // register the output parameters
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("median", Types.DOUBLE);

      if (median == 99999.99)
      {        
        System.out.println("\n-- The following error report is " +
                           "expected! --");
      }
      callStmt.execute();

      // retrieve output parameters
      double inoutMedian = callStmt.getDouble(1);
      
      System.out.println(procName + " completed successfully");
      System.out.println("Median salary returned from " + procName + " = "
                         + inoutMedian);
                         
     // clean up resources
     callStmt.close();                         
      
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
    }
  } // callInoutParameter

  public static void callClobExtract(String empNo, Connection con)
  {
    String outResume;
    try
    {
      // prepare the CALL statement for CLOB_EXTRACT
      String procName = "CLOB_EXTRACT";
      String sql = "CALL " + procName + "(:empNo, :Typvarchar)";
      CallableStatement callStmt = con.prepareCall(sql);

      // set input parameter to median value passed back by OUT_PARAM
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccStringAtName("empNo", empNo);
      
      // register the output parameters
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("Typvarchar", Types.VARCHAR);

      // call the stored procedure
      System.out.println();
      System.out.println("Call stored procedure named " + procName);
      callStmt.execute();

      // retrieve output parameters
      outResume = callStmt.getString(2);
      
      System.out.println(procName + " completed successfully");
      System.out.println("Resume section returned for employee "
                         + empNo + "=\n" + outResume);
          
    }
    catch (Exception e)
    {
      System.out.println(e.getMessage());
    }
  } // callClobExtract

  //*************************************************************************
  // PARAMETER STYLE JAVA procedures do not support the DBINFO clause.
  // The following PARAMETER STYLES can be used with DBINFO or PROGRAM TYPE
  // clauses: 
  //          - DB2SQL
  //          - GENERAL
  //          - GENERAL WITH NULLS
  //          - SQL
  // Please see the SpClient implementation for C/C++/CLI language to
  // see this functionality.
  //*************************************************************************

  //*************************************************************************
  // PROGRAM TYPE MAIN is only valid for LANGUAGE C, COBOL or CLR, and 
  // following PARAMETER STYLE:
  //          - DB2SQL
  //          - GENERAL
  //          - GENERAL WITH NULLS
  //          - SQL
  // Please see the SpClient implementation for C/C++/CLI language to
  // see this functionality.
  //*************************************************************************

  public static void callDecimalType(Connection con)
  {
    try
    {
      // prepare the CALL statement for DECIMAL_TYPE
      String procName = "DECIMAL_TYPE";
      String sql = "CALL " + procName + "(:inoutDec)";
      CallableStatement callStmt = con.prepareCall(sql);

      // declare and initialize input variable
      BigDecimal inoutDecimal = new BigDecimal("400000.00");

      // set input parameter
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccBigDecimalAtName("inoutDec", inoutDecimal);

      // register the output parameters
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("inoutDec", Types.DECIMAL, 2);

      // call the stored procedure
      System.out.println();
      System.out.println("Call stored procedure named " + procName);
      callStmt.execute();
   
      System.out.println(procName + " completed successfully");

      // retrieve output parameters
      inoutDecimal = callStmt.getBigDecimal(1).setScale( 2 );
      System.out.println("Value of DECIMAL = " + inoutDecimal);

      callStmt.close();
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
    }
  } // callDecimalType

  public static void callAllDataTypes(Connection con)
  {
    try
    {
      // prepare the CALL statement for ALL_DATA_TYPES
      String procName = "ALL_DATA_TYPES";
      String sql = "CALL " +
                   procName + "(:iosmall, :ioint, :iobigint, :ioreal, :iodouble, :char1, :char2, :varchar, :date, :time)";
      CallableStatement callStmt = con.prepareCall(sql);

      // declare and initialize input variables
      short inoutSmallint = 32000;
      int inoutInteger = 2147483000;
      long inoutBigint = 2147483000;
      float inoutReal = 100000;
      double inoutDouble = 2500000;

      // declare output variables
      String outChar, outChars, outVarchar;
      Date outDate;
      Time outTime;

      // set input parameters
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccShortAtName("iosmall", inoutSmallint);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccIntAtName("ioint", inoutInteger);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccLongAtName("iobigint", inoutBigint);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccFloatAtName("ioreal", inoutReal);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("iodouble", inoutDouble);

      // register the output parameters
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("iosmall", Types.SMALLINT);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("ioint", Types.INTEGER);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("iobigint", Types.BIGINT);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("ioreal", Types.REAL);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("iodouble", Types.DOUBLE);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("char1", Types.CHAR);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("char2", Types.CHAR);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("varchar", Types.VARCHAR);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("date", Types.DATE);
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).registerJccOutParameterAtName("time", Types.TIME);

      // call the stored procedure
      System.out.println();
      System.out.println("Call stored procedure named " + procName);
      callStmt.execute();
      
      System.out.println(procName + " completed successfully");

      // retrieve output parameters
      inoutSmallint = callStmt.getShort(1);
      inoutInteger = callStmt.getInt(2);
      inoutBigint = callStmt.getLong(3);
      inoutReal = callStmt.getFloat(4);
      inoutDouble = callStmt.getDouble(5);
      outChar = callStmt.getString(6);
      outChars = callStmt.getString(7);
      outVarchar = callStmt.getString(8);
      outDate = callStmt.getDate(9);
      outTime = callStmt.getTime(10);

      System.out.println("Value of SMALLINT = " + inoutSmallint);
      System.out.println("Value of INTEGER = " + inoutInteger);
      System.out.println("Value of BIGINT = " + inoutBigint);
      System.out.println("Value of REAL = " + inoutReal);
      System.out.println("Value of DOUBLE = " + inoutDouble);
      System.out.println("Value of CHAR(1) = " + outChar);
      System.out.println("Value of CHAR(15) = " + outChars.trim());
      System.out.println("Value of VARCHAR(12) = " + outVarchar.trim());
      System.out.println("Value of DATE = " + outDate);
      System.out.println("Value of TIME = " + outTime);
      
      callStmt.close();
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
    }
  } // callAllDataTypes

  public static void callOneResultSet(Connection con)
  {
    try
    {
      // prepare the CALL statement for ONE_RESULT_SET
      String procName = "ONE_RESULT_SET";
      String sql = "CALL " + procName + "(:outMedian)";
      CallableStatement callStmt = con.prepareCall(sql);

      // set input parameter to median value passed back by OUT_PARAM
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("outMedian", outMedian);
      
      // call the stored procedure
      System.out.println();
      System.out.println("Call stored procedure named " + procName);
      callStmt.execute();

      System.out.println(procName + " completed successfully");
      ResultSet rs = callStmt.getResultSet();
      fetchAll(rs);

      // close ResultSet and callStmt
      rs.close();
      callStmt.close();
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
    }
  } // callOneResultSet

  public static void callTwoResultSets(Connection con)
  {
    try
    {
      // prepare the CALL statement for TWO_RESULT_SETS
      String procName = "TWO_RESULT_SETS";
      String sql = "CALL " + procName + "(:outMedian)";
      CallableStatement callStmt = con.prepareCall(sql);

      // set input parameter to median value passed back by OUT_PARAM
      ((com.ibm.db2.jcc.DB2CallableStatement)callStmt).setJccDoubleAtName("outMedian", outMedian);

      // call the stored procedure
      System.out.println();
      System.out.println("Call stored procedure named " + procName);
      callStmt.execute();

      System.out.println(procName + " completed successfully");

      System.out.println(
        "Result set 1: Employees with salaries greater than " + outMedian);
      // get first result set
      ResultSet rs = callStmt.getResultSet();
      fetchAll(rs);

      System.out.println();
      System.out.println("Result set 2: Employees with salaries less than " +
                         outMedian);
      // get second result set
      callStmt.getMoreResults();
      rs = callStmt.getResultSet();
      fetchAll(rs);

      // close ResultSet and callStmt
      rs.close();
      callStmt.close();
      
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
    }
  } // callTwoResultSets

  //*************************************************************************
  // PARAMETER STYLE GENERAL and GENERAL WITH NULLS can be specified when
  // LANGUAGE C, COBOL, or CLR is used.
  // Please see the SpClient implementation for CLI language to see this 
  // functionality.
  //*************************************************************************

  // ======================================================
  // Method: fetchAll -- returns all rows from a result set
  // ======================================================
  public static void fetchAll(ResultSet rs)
  {
    try
    {
      System.out.println(
        "=============================================================");
    
      // retrieve the  number, types and properties of the 
      // resultset's columns
      ResultSetMetaData stmtInfo = rs.getMetaData();
      
      int numOfColumns = stmtInfo.getColumnCount();
      int r = 0;

      while (rs.next())
      {
        r++;
        System.out.print("Row: " + r + ": ");
        for (int i = 1; i <= numOfColumns; i++)
        {
          if (i == 3)
          {
            System.out.print(Data.format(rs.getDouble(i), 7, 2));
          }
          else
          {
            System.out.print(rs.getString(i));
          }

          if (i != numOfColumns)
          {
            System.out.print(", ");
          }
        }
        System.out.println();
      }
    }
    catch (Exception e)
    {
      System.out.println("Error: fetchALL: exception");
      System.out.println(e.getMessage());
    }
  } // fetchAll
} // SpServer