import java.sql.*;      // for JDBC
import java.io.*;       // for file i/o

/**
 * calls a PL/SQL stored procedure sp_from_java and prints
 *     results based on that call to the screen
 *
 * assumes that the Oracle student database is on 
 *     cedar.humboldt.edu, and that there is an account
 *     on that database with username java and password  
 *     that you know and have stored in a file pwd-file.txt 
 *     in the same directory as this class' .class file 
 *
 * @author Ann Burroughs
 * @author (modified by) Sharon Tuttle
 * @version 3-13-13
 */

public class StoredProcCall
{
    /**
     * calls a PL/SQL stored procedure sp_from_java and prints
     *     results based on that call to the screen
     *
     * @param args not used here
     */

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

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

        try
	{
	    // load driver

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

	    String username = "java";
	    String password = getPassword();

	    // create Connection object con

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

            // prepare the stored procedure call

            CallableStatement cStmt =
               con.prepareCall(
                   "{call SP_FROM_JAVA(?, ?, ?, ?, ?)}"); 

            // now you set those ? - register those parameters -
            //    NOTE that OUT parameters are handled
            //    differently from IN parameters

            cStmt.registerOutParameter(1, java.sql.Types.INTEGER);
            cStmt.registerOutParameter(2, java.sql.Types.CHAR);
            cStmt.setInt(3, 20);
            cStmt.setString(4, "red");
            cStmt.registerOutParameter(5, java.sql.Types.INTEGER);

            // execute the stored procedure with those
	    //    parameters

            cStmt.execute();

            // look at/use the out parameters

            System.out.println("Stored Proc OUT params:");
            System.out.println("Out param 1: " + cStmt.getInt(1));

            // (note, trimming bizarre excess trailing blanks)

            String outParam2 = cStmt.getString(2).trim();

            System.out.println("Out param 2: " + outParam2);

            System.out.println("Out param 5: " + cStmt.getInt(5));

            cStmt.close();
            con.close();

	} // end of try block

        // any exception thrown within the try-block above will be
        //    caught and reported

        catch (Exception e)
	{
	    System.out.println(e);
	}

    }  // end main

    /**
     * reads the java account password from the file pwd-file.txt,
     *   assumed to be in the same directory as this class
     *
     *   @return     the password read
     */

    private static String getPassword()
    {
	BufferedReader fromStream;  
	String password = "";
        try
	{
	    fromStream = new BufferedReader(
	        new FileReader("pwd-file.txt"));
	    password = fromStream.readLine();
	    fromStream.close();
	}
        catch (FileNotFoundException exc)
	{
	    System.out.println(
                "Could not open: "
                + "pwd-file.txt");
	}
        catch (IOException exc)
	{
	    System.out.println(    
               "IOError: " +
               exc.getMessage());
	}
        return password;
    }
     
}  // end StoredProcCall class