//
//   Source File Name: Columns.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 : 
//  Uses getColumns() to return attributes of columns 
//  - prompts for pattern search values for schema name, table name 
//  - lists all columns for the indicated tables 

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

   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;

      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);

         System.out.println ("Enter Table Schema Name Search Pattern:");
         String schemaSearchPat = Tools.readString().toUpperCase();
         System.out.println ("Enter Table Name Search Pattern:");
         String tableSearchPat = Tools.readString().toUpperCase();
         listColumns (con, schemaSearchPat, tableSearchPat);

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

   //  
   //  listColumns 
   //      - lists all columns that match input search patterns 
   //      - lists attributes of those columns 

   public static void listColumns (Connection con, String schemaSearch, String tableSearch) {
      try {
         DatabaseMetaData dmd = con.getMetaData();
         //  use getColumns to retrieve data on columns that match search pattern 
         ResultSet rs = dmd.getColumns(null, schemaSearch, tableSearch, "%");

         //  variables to be used in loop through the result set 
         int n = 0;
         int decLength = 0;
         int colLength = 0;
         String schemaName = new String("");
         String tableName = new String("");
         String nlble = new String("");
         String colName = new String("");
         String colType = new String("");
         String tableOld = new String("");
         String schemaOld = new String("");
         String remarks = new String("");
         String numOutput = new String("");

         while (rs.next()) {
            schemaName = rs.getString (2);
            tableName = rs.getString (3);
            //  format output routine 
            if (!tableName.equals(tableOld) || !schemaName.equals(schemaOld)) {
               System.out.print ("\n" + schemaName + "." + tableName + "\n");
               tableOld = tableName;
               schemaOld = schemaName;
            }
            colName = rs.getString (4);
            colType = rs.getString (6);
            colLength = rs.getInt (7);
            decLength = rs.getInt (9);
            //  formatted output 
            numOutput = "(" + colLength;
            if (!rs.wasNull()) { numOutput = numOutput + ", " + decLength; }
            numOutput = numOutput + ")";
            //  if n = 1 then column values are nullable, else not 
            n = rs.getInt (11);
            if (n == DatabaseMetaData.typeNullable) { nlble = "NULLABLE"; } else { nlble = "NOT NULLABLE"; }
            remarks = rs.getString (12);

            //  output column characteristics to screen 
            System.out.print ("   " + colName + ", " + nlble + ", " + colType + ", " + numOutput);
            if (!rs.wasNull()) { System.out.print (", " + remarks); }
            System.out.print ("\n");
         }
         rs.close();
      } catch (Exception e) { e.printStackTrace(); }
   }
}