import java.sql.*;

/**
 *
 * <p>Title: connect class</p>
 * <p>Description: Connect class takes care of the connection between our interface
 *         and Oracle database. </p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: </p>
 * <p>@author : CHiemi Umezawa</p>
 * <p>@version sdk1.4.2</p>
 */
public class connect{
  private  Connection conn;
  private  Statement stmt;
  private  ResultSet rset;
  private boolean connected;

  /**
   * Purpose:  This is a constructor of this class that initializes all fields. </br>
   * Pre: </br>
   * Post: All fields are initilized. </br>
   */
  public connect(){
    conn = null;
    stmt = null;
    rset = null;
    connected = false;
  }

  /**
   * Purpose: This method will try to make a connection to the Oracle, using the
   *     given username and password.
   * @param user
   * @param pswd
   * @throws SQLException
   * @throws java.lang.ClassNotFoundException
   */
  public void start(String user, String pswd) throws SQLException,
      ClassNotFoundException {

    try {

      String userName = user;
      String passwd = pswd;

      Class.forName("oracle.jdbc.driver.OracleDriver");
      conn =
          DriverManager.getConnection("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ect-sun.ecst.csuchico.edu)(PORT=1521)))(CONNECT_DATA=(SID=ecstDB)(SERVER=DEDICATED)))",
                                      userName, passwd);

      stmt = conn.createStatement();
      //create a table
      //CreateGroceryStoreData cgsd = new CreateGroceryStoreData(stmt);
      create();
      System.out.println("DONE");

    }
    catch (SQLException e) {
      System.out.println("SQLException occured. Please do this again. ");
      System.exit(0);
    }
    connected = true;

  }

  /**
   * Purpose: This method returns ResultSet obtained from a specific query. </br>
   * Pre:  query is correct.  </br>
   * Post: The ResultSet object is obtained from the given query, and is returned. </br>
   * @param query
   * @return ResultSet
   */
  protected ResultSet getResult(String query){
    try{
      rset = stmt.executeQuery(query);
    }catch(SQLException e){ rset = null; }
    return rset;
  }
  /**
   * Purpose: This create method creates a new table. </br>
   * Pre:  stmt<Statement> is not null, and there is not a
   *       table that has the same name(table title)as this on the database.  </br>
   * Post: A new table is created on the database. </br>
   */
  public void create(){
        try {
            // execute SQL commands to create table, insert data
            for (int i=0; i<SQL.length; i++) {
                stmt.execute(SQL[i]);
            }
            System.out.println("done");
        } catch (Exception e) {System.out.println("exception");}
    }

    static String[] SQL = {
        "create table GroceryStore ("+
            "product_name varchar (32),"+
            "product_id integer ,"+
            "price float ,"+
            "category varchar (13),"+
            "stocked_num integer )",
        "insert into GroceryStore values ('Tomato', 00001, '1.00', 'vegetable', 00010)",
        "insert into GroceryStore values ('Mushroom', 00002, '1.99', 'vegetable', 00050)",
        "insert into GroceryStore values ('Bell Pepper', 00003, '1.99', 'vegetable', 00050)",
        "insert into GroceryStore values ('white onion', 00004, '1.25', 'vegetable', 00110)",
        "insert into GroceryStore values ('Yellow onion', 00005, '.99', 'vegetable', 00190)",
        "insert into GroceryStore values ('Potapo Yam', 00006, '1.50', 'vegetable', 00100)",
        "insert into GroceryStore values ('Potato sweet', 00007, '1.25', 'vegetable', 00300)",

        "insert into GroceryStore values ('Milk A', 00010, '3.39', 'dairy product', 00100)",
        "insert into GroceryStore values ('Milk B', 00011, '4.39', 'dairy product', 00100)",
        "insert into GroceryStore values ('Milk C', 00012, '2.39', 'dairy product', 00100)",
        "insert into GroceryStore values ('pizza cheese', 00014, '2.50', 'dairy product', 00090)",

        "insert into GroceryStore values ('pork sholder', 00020, '3.00', 'meat', 00050)",
        "insert into GroceryStore values ('pork BBQ', 00021, '4.50', 'meat', 00050)",
        "insert into GroceryStore values ('beef sholder', 00022, '2.50', 'meat', 00100)",
        "insert into GroceryStore values ('beef for stew', 00023, '2.00', 'meat', 00250)",

        "insert into GroceryStore values ('Orange Juice A', 00030, '3.99', 'juice', 00500)",
        "insert into GroceryStore values ('Orange Juice B', 00031, '2.99', 'juice', 00600)",
        "insert into GroceryStore values ('Apple Juice A', 00032, '3.99', 'juice', 00500)",
        "insert into GroceryStore values ('Apple Juice B', 00033, '1.99', 'juice', 00750)",
        "insert into GroceryStore values ('Grape Juice A', 00034, '3.50', 'juice', 00200)",
        "insert into GroceryStore values ('Grape Juice B', 00035, '2.99', 'juice', 00300)",


        "insert into GroceryStore values ('Salt A', 00040, '.5', 'seasoning', 00300)",
        "insert into GroceryStore values ('Salt B', 00041, '1.00', 'seasoning', 00300)",
        "insert into GroceryStore values ('Oregano', 00042, '2.50', 'seasoning', 00100)",
        "insert into GroceryStore values ('Sugar A', 00043, '1.99', 'seasoning', 00300)",
        "insert into GroceryStore values ('Sugar B', 00044, '2.49', 'seasoning', 00250)",

    };


}