//Alex Roque
//Database Systems
//Dec. 6,2000

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

public class  transcriptDB{

      static Connection cn;
      static PrintWriter out;
      
      public transcriptDB(){}

private static ResultSet getQuery( String query ) {

        String driverURL = "jdbc:odbc:student_record";
        Connection con = null;
        cn = null;
        ResultSet rs = null;
        Statement st = null;

        try {
	       Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
        }
        catch( Exception e ) {
	       System.err.println( "Driver could not be loaded...\n" );
        }

        try {
	       cn = DriverManager.getConnection( driverURL );
	       System.err.println( "Connected to the university database...\n" );
         System.err.println("Making Transcript Query.....\n");
	       st = cn.createStatement();
	       rs = st.executeQuery( query );

        }
	    catch( SQLException e ) {
	       e.printStackTrace();
	       System.err.println( "Could not make a connection to the database...\n" );
        }

	    return rs;  //return the ResultSet object

    } //end  getQuery
    
  public static void main( String args[] ) throws IOException{

    	BufferedReader in = new BufferedReader( new InputStreamReader( System.in ) );
      System.out.println("Please enter Student SSN whose transcript is to be received:");
	    String input = in.readLine();

      String query = "SELECT Student.SSN, FirstName, M_Initial, LastName, CourseName, CourseNumber, Grade FROM Student, EnrolledIn WHERE Student.SSN='"+input+"' And EnrolledIn.SSN='"+input+"'";

      FileWriter fout = OpenFiles.getNameAndOpenOutput();
      out = new PrintWriter( fout, true );

	    String firstName = new String();
      String lastName = new String();
      String mInitial = new String();
	    String SSN = new String();
      String courseName = new String();
      String courseNumber = new String();
      String grade = new String();
      boolean hasNext = false;
      ResultSet rc = getQuery(query);

	    try {

	        rc.next();
          SSN = rc.getString("SSN");
          firstName = rc.getString("FirstName");
          lastName = rc.getString("LastName");
          mInitial = rc.getString("m_Initial");
          System.err.println("Query has been completed. No errors occured.\n");

	    }
	    catch (SQLException e)
	    {
          System.out.println("Problem with query. No records seem to have been returned.\n");
          System.err.println("Printing stacktrace information:\n");
          e.printStackTrace();
	    }
      out.println("Printed Transcript of the university database");
      out.println();
      out.println("SSN of the Student: "+SSN);
      if (mInitial == null)
          out.println("Name of the Student: " + firstName + " " + lastName);
      else
	        out.println("Name of the Student: " + firstName + " "+ mInitial+ " " + lastName);
      out.println();
      out.println("COURSES                            Grade");
      out.println();
      do

      {
          try{
          courseName = rc.getString("courseName");
          courseNumber = rc.getString("courseNumber");
          grade = rc.getString("Grade");
          hasNext = rc.next();
          }
          catch(SQLException e)
          {
              e.printStackTrace();
          }
          out.println(courseName + "      " + courseNumber + "      " + "     " + grade);
      }
      while(hasNext)  ;

    } //end main

} //end of connect