import java.sql.*;
import java.util.*;

public class PrepareAlt
{
  static String[] asDOW =
  {
    "Mon",
    "Tue",
    "Wed",
    "Thu",
    "Fri",
    "Sat",
    "Sun"
  };

  static String[] asLDOW =
  {
    "Monday",
    "Tuesday",
    "Wednesday",
    "Thursday",
    "Friday",
    "Saturday",
    "Sunday"
  };

  Connection con;
  int ndx,
      iRowCount;
  PreparedStatement pstmt1 = null,
                    pstmt2 = null;
  ResourceBundle rbConnect;
  ResultSet rs;
  Statement stmt;
  String sDriver,
         sDriverKey = "CSDriver",
         sPassword,
         sPasswordKey ="CSPassword",
         sQuery = 
    "SELECT * FROM JJJJData",
         srbName = "ConnectU",
         sUpdate1 = 
    "UPDATE JJJJData SET LDOW = ? WHERE DOW = ?",
         sUpdate2 = 
    "UPDATE JJJJData SET Type = ? WHERE Type = ?",
         sURL,
         sURLKey="CSURL",
         sUserID,
         sUserIDKey = "CSUserID";

  // column value holders
  int iCups, 
      iEntry;

  String sCustomer = null,
         sDOW      = null,
         sLDOW     = null,
         sType     = null;


  public PrepareAlt()
  {
    try  // get the PropertyResourceBundle
    {
      rbConnect = ResourceBundle.getBundle( srbName );

      sDriver   = rbConnect.getString( sDriverKey );
      sPassword = rbConnect.getString( sPasswordKey );
      sURL      = rbConnect.getString( sURLKey );
      sUserID = rbConnect.getString( sUserIDKey );
    }
    catch( MissingResourceException mre )
    {
      System.err.println(
                  "ResourceBundle problem for " + 
                   srbName + ", program ends." );
      System.err.println("Specific error: " + 
                          mre.getMessage() );
      return; // exit on error
    }

    try   // Attempt to load the JDBC driver 
    {     // with newInstance
        Class.forName( sDriver ).newInstance();
    }
    catch( Exception e )  // error
    {
      System.err.println(
                  "Failed to load current driver.");
      return;
    } // end catch

    try
    {
      con = DriverManager.getConnection ( sURL,
                                          sUserID,
                                          sPassword);
      stmt = con.createStatement();
    }
    catch ( SQLException SQLe)
    {
      ReportSQLError( SQLe );

      if( con != null)
      {
        try { con.close(); }
        catch( Exception e ) {}
      }

      return;
    } // end catch

    try
    {
      // Add new LDOW column
      stmt.executeUpdate( "ALTER TABLE JJJJData "  +
             "ADD COLUMN LDOW VARCHAR (9)" );
      System.out.println(
        "Column LDOW added to Table JJJJData.");

      // prepare the statements
      pstmt1 = con.prepareStatement( sUpdate1 );
      pstmt2 = con.prepareStatement( sUpdate2 );

      // Load LDOW columns
      for ( ; ndx < asDOW.length; ndx++ )
      {
        pstmt1.setString( 1, asLDOW[ndx] );
        pstmt1.setString( 2, asDOW[ndx] );

        iRowCount += pstmt1.executeUpdate();
      }

      System.out.println( iRowCount + 
         " Rows updated for LDOW in JJJJData.");

      // Update "JustJoe" values to "Jus'Joe"
      pstmt2.setString( 1, "Jus'Joe" );
      pstmt2.setString( 2, "JustJoe" );

      iRowCount = pstmt2.executeUpdate();
      System.out.println( iRowCount + 
         " Rows updated to Jus'Joe in JJJJData.");

      // Report table Data
      rs = stmt.executeQuery( sQuery );

      while(rs.next())   // for each row of data
      {
        iEntry = rs.getInt("Entry");
        sCustomer = rs.getString("Customer");
        sDOW = rs.getString("DOW");
        iCups = rs.getInt("Cups");
        sType = rs.getString("Type");
        sLDOW = rs.getString("LDOW");

        // Report each Customer
        System.out.println( iEntry    + ",\t" +
                            sCustomer + ",\t" +
                            sDOW      + ",\t" +
                            iCups     + ",\t" +
                            sType     + ",\t" +
                            sLDOW );
      } // end while


    } // end try
    catch ( SQLException SQLe)
    {
      ReportSQLError( SQLe );
    }
    catch (Exception e) { e.printStackTrace(); }
    finally
    {
      try { stmt.close(); }
      catch( Exception e ) {}

      if( pstmt1 != null)
      {
        try { pstmt1.close(); }
        catch( Exception e ) {}
      }

      if( pstmt2 != null)
      {
        try { pstmt2.close(); }
        catch( Exception e ) {}
      }

      try { con.close(); }
      catch( Exception e ) {}
    } // end finally clause

  } // end constructor


  public void ReportSQLError( SQLException SQLe )
  {
      System.err.println( "Problem:" );
      System.err.println( SQLe.getMessage() );
      System.err.println( "SQL State: " + 
                           SQLe.getSQLState() );

  }  // end ReportSQLError


  public static void main (String args[])
  {
    new PrepareAlt();
  } // end main

} // end class PrepareAlt