/**
 * ---------------------------------------------------------------------------
 * File name: Hw6Pr2.java
 * Project name: HW6
 * ---------------------------------------------------------------------------
 * Creator's name and email: Sean Letterman, seanletterman@gmail.com
 * Course-Section:  CSCI2020-201
 * Creation Date: Apr 22, 2010
 * Date of Last Modification: Apr 23, 2010
 * ---------------------------------------------------------------------------
 */
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;

public class Hw6Pr2
{
   public static void main(String[] args)
   {
       String   myConnStr,
                myUser,
                myPasswd,
                myTableQuery,
                myUpdateQuery,
                mySelectQuery;

       try
       {
             DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

             myConnStr = "jdbc:oracle:thin:@pythia.etsu.edu:1521:csdb";
             myUser = "letterms";
             myPasswd = "8==============D";
             Connection myCon = null;
             myCon = DriverManager.getConnection(myConnStr, myUser, myPasswd);



             Statement myQueryStmt = null;
             myQueryStmt = myCon.createStatement();

             Statement myQueryStmt2 = null;
             myQueryStmt2 = myCon.createStatement();

             Statement myQueryStmt3 = null;
             myQueryStmt3 = myCon.createStatement();

             ResultSet myTableResult = null;
             ResultSet myUpdateResult = null;
             ResultSet mySelectResult = null;


          myTableQuery = "create table ASSISTANT2 (" +
                        "ASSIGNMENT_NUMBER number(3,0) primary key," +
                        "STUDENT references STUDENT on delete set null," +
                        "COURSE references COURSE," +
                        "DATE_STARTED date not null," +
                        "DESCRIPTION varchar2(60))";

          myUpdateQuery = "insert into ASSISTANT2 values (" +
                    "001, 10190257, 'CSCI4127', '21-MAR-2009', 'Grading'); " +

                    "insert into ASSISTANT2 values (" +
                        "002, 10198947, 'CSCI1100', '25-FEB-2009', 'Test'); " +

                    "insert into ASSISTANT2 values (" +
                        "003, 13128601, 'CSCI2160', '12-JAN-2009', 'Homework');";

          mySelectQuery = "select * from ASSISTANT2";

             myTableResult = myQueryStmt.executeQuery(myTableQuery);
             myUpdateResult = myQueryStmt2.executeQuery(myUpdateQuery);
             mySelectResult = myQueryStmt3.executeQuery(mySelectQuery);

             while(mySelectResult.next())
             {
                 String assignmentNum = myUpdateResult.getString("ASSIGNMENT_NUMBER");
                 String student = myUpdateResult.getString("STUDENT");
                 String course = myUpdateResult.getString("COURSE");
                 String dateStart = myUpdateResult.getString("DATE_STARTED");
                 String description = myUpdateResult.getString("DESCRIPTION");

                 System.out.print(assignmentNum + " " + student + " " + course + " " +
                         dateStart + " " + description + "\n");
             }
          System.out.print(mySelectResult);
          myCon.setAutoCommit(false);

          mySelectResult.close();
          myUpdateResult.close();
          myTableResult.close();
             myQueryStmt3.close();
             myQueryStmt2.close();
             myQueryStmt.close();
             myCon.close();

          }
          catch (SQLException mySqlError)
          {

             System.out.println("An Error has occured, dumping error stack");
             mySqlError.printStackTrace();
          }
       }
    }