//
//  Source File Name: PartRep.java  1.3 
//  
//   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 : 
//         Use recursive SQL to generate an exploding parts list. 

//  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 PartRep {

   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) {
         e.printStackTrace();
      }
   }

   public static void main (String[] argv) {
      Connection con = null;
      int ROW_ARRAY_SIZE = 2;
      try {
         //  connect to the server of user's choice 
         //  see Tools.class on Tools.DBConnect method 
         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);

         //  create array with products to be used when generating list 
         int[] prod_Num = {
            990110, 990120 };
         generatePartsList (con, prod_Num, ROW_ARRAY_SIZE);

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

   //  
   //  generatePartsList 
   //     - creates the SQL statement and then executes the query 
   //     - prints the data from the results sets listing the column name if 
   //       they have never been listed before 
   //  

   public static void generatePartsList (Connection con, int[] prodNum, int numEntry) {
      try {
         //  prepare statement to use in for loop 
         String statement = "WITH mainp (level, prod_num, part_num) AS " +
                              "( SELECT 1, root.prod_num, root.part_num " +
                                 "FROM prod_parts root " +
                                 "WHERE root.prod_num = pnum(cast(? as integer)) " +
                                "UNION ALL " +
                                "SELECT parent.level + 1, sub.prod_num, sub.part_num " +
                                 "FROM mainp parent, prod_parts sub " +
                                 "WHERE parent.part_num = sub.prod_num " +
                                 "AND parent.level < 2 " +
                              ") " +
                            "SELECT distinct " +
                              "m.level, m.prod_num, m.part_num " +
                              "FROM mainp m ";
         PreparedStatement pstmt = con.prepareStatement (statement);
         boolean headersWritten = false;
         int level = 0;
         String prod_Num = new String("");
         String part_Num = new String("");

         //  execute prepared for the number of different products in the array 
         for (int cnt = 0; cnt < numEntry; cnt = cnt + 1) {
            pstmt.setInt (1, prodNum[cnt]);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
               //  the first time going through any result set get the column 
               //  names to print as headers 
               if (!headersWritten) {
                  ResultSetMetaData rsmd = rs.getMetaData();
                  System.out.println ("\n" + Tools.padLength(rsmd.getColumnName (1), 12) +
                     Tools.padLength(rsmd.getColumnName (2), 12) + rsmd.getColumnName (3));
                  headersWritten = true;
               }
               level = rs.getInt (1);
               prod_Num = rs.getString (2);
               part_Num = rs.getString (3);

               //  formatted print of the information from the result set 
               System.out.println (Tools.padLength(String.valueOf (level), 12) +
                  Tools.padLength(prod_Num, 12) + part_Num);
            }
            rs.close();
         }
         pstmt.close();
      } catch (Exception e) { e.printStackTrace(); }
   }
}