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 Hw6Pr1
{

   /**
    * Method: main<br>
    * Responsibilities: runs sql queries and lists them in the way needed.
    *
 * @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  mySqlStmt;   // holds sql statement to be run
       String  prevGiven;	//holds the previous given name from the loop
       String  prevFamily;	//holds the previous family name from the loop
       int count = 0;// holds the count of rows retrieved

       /*
       *  handling exceptions
       */
      try
      {
         //Link 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 = "studers";
         myPasswd = "rachelle";
         Connection myCon = null;     //Build the connection object
         myCon = DriverManager.getConnection(myConnStr, myUser, myPasswd); //establish a connection via the jdbc driver


         Statement myStmt = null;          //holds the value of the sql statement
         myStmt = myCon.createStatement(); // the connection association
         Statement statement = null;
         statement = myCon.createStatement();

         ResultSet myReslts = null;    // the result set
         mySqlStmt = "SELECT INSTRUCTOR.GIVEN_NAME, INSTRUCTOR.FAMILY_NAME, SECTION.COURSE, SECTION.TERM FROM INSTRUCTOR	JOIN SECTION ON INSTRUCTOR.ID = SECTION.INSTRUCTOR;"; // the sql statement to run



         myReslts = myStmt.executeQuery(mySqlStmt);
                                    // tell the jdbc connection to run the query
                                    // and put the results in my_results


         //looping through the results to extract the data needed

         String givenName =  myReslts.getString("GIVEN_NAME");  //holds the given name
         String familyName = myReslts.getString("FAMILY_NAME"); //holds the family name
         String course = myReslts.getString("COURSE");			//holds the course number
         String term = myReslts.getString("TERM");
         prevFamily  = familyName;
         prevGiven = givenName;
         while (myReslts.next())  //ResultSet.next gets the next record,
                                  //it returns false if no more records exist.
         {

             givenName =  myReslts.getString("GIVEN_NAME");
             familyName = myReslts.getString("FAMILY_NAME");
             course = myReslts.getString("COURSE");
             term = myReslts.getString("TERM");

            //counter to see the position of the loop
            count = count + 1;
            System.out.print("Instructor" + count + ":");
            while(givenName.equals(prevGiven)&& familyName.equals(prevFamily))
            {
            	System.out.println(course + " " + term);
            }
            prevFamily  = familyName;
            prevGiven = givenName;
            //Now use all these pieces to build up a nice output.


         }  //end while

         myReslts.close();   // close the result set
         myStmt.close();     // close the statement
         myCon.close();      // close the connection

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