import java.sql.DriverManager;   // DriverManager for establishing linkage to DB
import java.sql.SQLException;    // SQLException handles exception for DB classes
import java.sql.Connection;      // Connection establishes a logon to the DB
import java.sql.Statement;       // Holds and executes the sql statements
import java.sql.ResultSet;       // Holds the results of a query
import java.util.Date;

public class Hw6Pr2
{

   /**
    * Method: main<br>
    * Responsibilities: .
    *
 * @param args
 */
public static void main(String[] args)
   {
       System.out.println("Starting Connection Process");
       String  myConnStr;   // holds the connection string
       String  myUser;      // holds the user name
       String  myPasswd;    // holds the user password
       String  tableCreateStatement;   // holds sql statement to be run
       String  populateTableStatement;
       String  populateTableStatementTwo;
       String  populateTableStatementSelect;



      try
      {
         //Link the class to the jdbc driver
          System.out.println("Link this class to the jdbc driver");
         DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

         //connection to the database
         myConnStr = "jdbc:oracle:thin:@pythia.etsu.edu:1521:csdb";
         myUser = "";
         myPasswd = "";
         Connection myCon = null;
         myCon = DriverManager.getConnection(myConnStr, myUser, myPasswd);


         Statement myStmt = null;
         myStmt = myCon.createStatement();
         Statement myTableStatement = null;
         myTableStatement = myCon.createStatement();
         Statement myTableStatementTwo = null;
         myTableStatementTwo = myCon.createStatement();
         Statement myTableStatementSelect = null;
         myTableStatementSelect = myCon.createStatement();

         ResultSet tablePopulatedResults = null;
         ResultSet tableCreateResults = null;
         tableCreateStatement = "create table ATTEND2( " +
						"STUDENT, " +
						"COURSE, " +
						"SECTION, " +
						"TERM, " +
						"ABSENCES number (2,0), " +
						"STATUS varchar2(12) not null, " +
						"primary key (STUDENT, COURSE, SECTION, TERM, ABSENCES), " +
						"foreign key (STUDENT, COURSE, SECTION, TERM) references TAKES on delete cascade)";
        	 		 // the sql statement to run
         populateTableStatement = "INSERT INTO ATTEND2 VALUES "+
         							"(12345678, 'CSCI2200', '001', '083', 5, 'STUDENT')";
         populateTableStatementTwo = "INSERT INTO ATTEND2 VALUES "+
									"(87654321, 'MATH2100', '201', '073', 11, 'STUDENT')";

         populateTableStatementSelect = "SELECT * FROM ATTEND5";

         tableCreateResults = myStmt.executeQuery(tableCreateStatement);

         myTableStatement.executeQuery(populateTableStatement);
         myTableStatementTwo.executeQuery(populateTableStatementTwo);

         tablePopulatedResults= myTableStatementSelect.executeQuery(populateTableStatementSelect);


         while (tablePopulatedResults.next())
         {


        	 int student = tablePopulatedResults.getInt("STUDENT");
        	 String course = tablePopulatedResults.getString("COURSE");
        	 String section = tablePopulatedResults.getString("COURSE");
        	 String term = tablePopulatedResults.getString("COURSE");
        	 int absences = tablePopulatedResults.getInt("ABSENCES");
        	 String status = tablePopulatedResults.getString("COURSE");

        	 System.out.println(student + " " + course + " " + section + " " + term + " " + absences + " " + status);


         }  //end while

         tableCreateResults.close();
         tablePopulatedResults.close();
         myTableStatement.close();
         myTableStatementTwo.close();
         myTableStatementSelect.close();
         myStmt.close();
         myCon.close();

      }  // end try
      catch (SQLException mySqlError)
      {
         //error handling
         System.out.println("An Error has occured, dumping error stack");
         mySqlError.printStackTrace();
      }
   }
}