//
//  Source File Name: Embedded.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 : 
//      Example of executing various SQL statements in JDBC 

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

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

         String tableName = "NAMEID";
         createAndPopulateTable (con, tableName);
         listTableData (con, tableName);
         destroyTable (con, tableName);

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

   //  createAndPopulateTable 
   //     - creates table NAMEID and populates with a single row of values 

   public static void createAndPopulateTable (Connection con, String tableName) {
      try {
         Statement stmt = con.createStatement();
         //  statememt to create table NAMEID 
         stmt.execute ("CREATE TABLE " + tableName + " (ID integer, NAME varchar(50))");
         stmt.close();
         //  commit changes made to database 
         con.commit();

         //  prepared statement to insert a row of values into table 
         PreparedStatement pstmt = con.prepareStatement ("INSERT INTO NAMEID VALUES (?, ?)");
         int id = 500;
         String name = "Babbage";
         pstmt.setInt (1, id);
         pstmt.setString (2, name);
         pstmt.executeUpdate();
         pstmt.close();
         //  commit changes made to database 
         con.commit();
      } catch (Exception e) { e.printStackTrace(); }
   }

   //  
   //  listTableData 
   //     - queries NAMEID for IDs and NAMEs and displays results to screen 
   //  

   public static void listTableData (Connection con, String tableName) {
      try {
        Statement stmt = con.createStatement();
        //  statement to select data from table id 
        //  data is stored as a result set 
        ResultSet rs = stmt.executeQuery ("SELECT ID, NAME FROM " + tableName);
        int id = 0;
        String name = new String("");

        while (rs.next()) {
           id = rs.getInt (1);
           name = rs.getString (2);
           //  display information to screen 
           System.out.println ("Result of Select: id = " + id + " name = " + name);
        }
        rs.close();
        stmt.close();
      } catch (Exception e) { e.printStackTrace(); }
   }

   //  destroyTable 
   //     - drops NAMEID and commits changes 

   public static void destroyTable (Connection con, String tableName) {
      try {
         Statement stmt = con.createStatement();
         //  statement to destory table (term used is drop table) 
         stmt.execute ("DROP TABLE " + tableName);
         stmt.close();
         con.commit();
      } catch (Exception e) { e.printStackTrace(); }
   }
}