//
//  Source File Name: LookRes.java  1.4 
//  
//   Licensed Materials -- Property of IBM 
//  
//   (c) Copyright International Business Machines Corporation, 1999. 
//       All Rights Reserved. 
//  
//   US Government Users Restricted Rights - 
//   Use, duplication or disclosure restricted by 
//   GSA ADP Schedule Contract with IBM Corp. 
//  
//   Purpose : 
//     Display all employees with a resume CLOB. Display interests found 
//     in resume by downloading the CLOB and searching for the phrase 
//     "Interests" 
//     NOTE: This program does not run like the CLI program with the same 
//       name, which uses LOB Locators. This program is not as efficient 
//       since it downloads the entire CLOB rather than using a LOB  
//       locator to search for the interests. 
//  
//   This program may not work if the CLOB contains non-ASCII characters, 
//   since the copy routine relies on the getAsciiStream() method. 

//  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 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. 

import java.io.*;
import java.lang.*;
import java.sql.*;

class LookRes {

   static {
      try {
         //  register the driver with DriverManager 
         //  The newInstance() call is needed for the sample to work with 
         //  JDK 1.1.1 on OS/2, where the Class.forName() method does not 
         //  run the static initializer. For other JDKs, the newInstance 
         //  call can be omitted. 
         Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
      } catch (Exception e) {
         System.out.println(e);
      }
   }

   public static void main (String[] argv) {
      Connection con = null;

      try {
         //  connect to server 
         if (argv.length == 3)
         {  //  connect using command line arguments 
            con = Tools.DBConnect(argv[0], argv[1], argv[2]); 
         }
         else
         {  //  prompt user for database name, user ID, password 
            con = Tools.DBConnect(); 
         }
         con.setAutoCommit (false);
         listEmployeesWithResumes (con);
         listInterests (con);

         //  disconnect from connection made 
         System.out.println ("\n>Disconnecting...");
         con.commit();
         con.close();
      } catch (Exception e) { System.out.println(e); }
   }

   //  listEmployeesWithResumes 
   //     - queries the database to list all employess with  
   //       ASCII formatted resumes 

   public static void listEmployeesWithResumes (Connection con) {
      try {
         Statement stmt = con.createStatement();
         //  query database for employee number and name 
         ResultSet rs = stmt.executeQuery (
            "SELECT employee.empno, firstnme || lastname as name " +
            "FROM employee, emp_resume " +
            "WHERE employee.empno = emp_resume.empno " +
            "AND resume_format = 'ascii'");
         System.out.println ("\nEmpno   Name");
         System.out.println ("------- ---------------------");

         String empNo = new String("");
         String name = new String("");

         while (rs.next()) 
         {
            empNo = rs.getString (1);
            name = rs.getString (2);
            System.out.println (Tools.padLength(empNo, 8) + name);
         }
         rs.close();
         stmt.close();
      } catch (Exception e) { System.out.println(e); }
   }

   //  
   //  listInterests 
   //     - copy the CLOB into a string and search for "Interests" 
   //     - print the interests 
   //  

   public static void listInterests (Connection con) {
      try {
         //  choose the employee resume that matches the user employee number choice 
         System.out.println ("\n>Enter an employee number:");
         String empNo = Tools.readString ();
         Statement stmt = con.createStatement ();
         ResultSet rs = stmt.executeQuery("SELECT resume FROM emp_resume WHERE " +
        "empno = '" + empNo + "' AND resume_format = 'ascii'");

         if (rs.next()) {
            System.out.println ("\nEmployee #: " + empNo);

            //  copy the CLOB into an array of characters by converting all  
            //  bytes into characters as they are read in 
            InputStream is = rs.getAsciiStream (1);
            //  InputStream.available() may not work on larger files 
            int max = is.available();
            byte[] clobBytes = new byte[max];
            char[] clobData = new char[max];
            is.read(clobBytes);
            for (int cnt = 0; cnt < max; cnt = cnt + 1) {
               clobData[cnt] = (char)clobBytes[cnt];
            }

            String clob = String.valueOf (clobData);
            //  print from interests to the end of the CLOB 
            int index = clob.indexOf("Interests");
            if (index == -1) {
               throw new Exception("Resume does not contain an Interests section!");
            }
            else {
               String interests = clob.substring (clob.indexOf ("Interests"));
               System.out.println (" " + interests);
            }
         }
         else { System.out.println ("\nThat employee does not have a resume!"); }
         rs.close();
         stmt.close();
      } catch (Exception e) { System.out.println(e); }
   }
}