import java.sql.*;   // for JDBC
import java.util.*;  // for Scanner and its related exceptions
import java.io.*;    // for more file-related stuff

/**
 * grabs employee names and their salaries from HSU's
 *    Oracle student database
 * (2nd JDBC example)
 *
 * @author Ann Burroughs
 * @author (modified by) Sharon Tuttle
 * @version 3-11-13
 */

public class EmpSal
{
    /**
     * tries to query for employee names and salaries from the
     *     HSU Oracle student database and print
     *     them to the screen
     * 
     * @param args not used
     */

    public static void main(String args[])
    {
        // turn off looking that the IPv6 stack to avoid
        //    a 2 minute wait for V6 timeout

        System.setProperty("java.net.preferIPv4Stack", "true");

        try
	{
            // load jdbc driver for Oracle

            Class.forName("oracle.jdbc.driver.OracleDriver");

            BufferedReader fromStream;
            String username = "java";
            String password = "";

            // read this username's password from a local
	    //    file pwd-file.txt (which we'll give
	    //    permission 600!!!)

            try
	    {
                fromStream = new BufferedReader(
	            new FileReader("pwd-file.txt"));
                password = fromStream.readLine();
                fromStream.close();
            }
            catch (FileNotFoundException exc)
            {
                System.out.println("EmpSal: could not " +
                    "open pwd-file.txt");
            }
            catch (IOException exc)
            {
                System.out.println("EmpSal: IOError: " +
                    exc.getMessage());
            }

            // make a Connection object 
            // (make the connection to Oracle on
            //    on cedar, for this username and password)

            Connection con = DriverManager.getConnection(
                "jdbc:oracle:thin:@cedar:1521:student",
                username, password);

            // Connection class has a method createStatement,
            //    it returns a Statement object.

            Statement stmt = con.createStatement();

            // it is convenient, but not required,
	    //    to store the Oracle statement desired
	    //    in a String variable

            String emplQuery = 
                "select empl_last_name, salary " +
                "from empl";   // NO ; IN QUERY STR!!!!

            ResultSet rS = stmt.executeQuery(emplQuery);

            // for each row in ResultSet, print employee
	    //     and salary

            String emplName;
            int empSal;

            while (rS.next())
	    {
                // get 1st projected result from current row
		//     and treat it like a Strig
 
                emplName = rS.getString(1);

                // get the result from the column salary
                //     and treat like an int

                empSal = rS.getInt("salary");

                System.out.println(emplName + " " + empSal);
            }

            // COURSE STYLE STANDARD -- CLOSE your statement
	    //    and connection when you are done!!!

            stmt.close();
            con.close();
        }

        // any other exceptions? catch and output here:

        catch (Exception exc)
        {
            System.out.println(exc);
        }
    } // end main
} // end EmpSal class