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

/** 
 * calls a PL/SQL stored procedure swap -- to give an example
 *    involving IN OUT parameters...?
 *
 * 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 Sharon Tuttle
 * @version 3-13-13
 */

public class InOutParam
{
    /**
     * calls a PL/SQL stored procedure swap, trying to handle its two
     *    IN OUT parameters
     * 
     * @param args the two values to be, ah, swapped... 
     */

    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");  

        // exit if aren't exactly two numeric command-line arguments

        if (args.length != 2)
        {
            System.out.println("InOutParam: needs exactly 2 numeric " +
                               "command-line arguments");
	    System.exit(0);
	}

        // these need to be declared outside of the try-block, because
        //     want to use them later in this method -- and need to
	//     initialize them so Java won't complain that "might not"
        //     be initialized...!

        double cmdLine1 = 0;
        double cmdLine2 = 0;

        try
        {
            cmdLine1 = Double.parseDouble(args[0]);
            cmdLine2 = Double.parseDouble(args[1]);
        }
        catch (NumberFormatException e)
        {
            System.out.println("InOutParam: both command-line arguments " +
                               "must be numeric");
            System.exit(0);
        }

        // IF get here -- should have two numeric command-line arguments

        System.out.println("first command line argument: " + cmdLine1);
        System.out.println("second command line argument: " + cmdLine2);

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

            // connect to the database

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

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

            // prepare the swap stored procedure call (but do not 
            //     actually call it yet)

            CallableStatement cstmt =
                con.prepareCall(
                    "{call swap(?, ?)}");

            // trying to register the two IN OUT parameters

            cstmt.registerOutParameter(1, java.sql.Types.DOUBLE);
            cstmt.registerOutParameter(2, java.sql.Types.DOUBLE);
            cstmt.setDouble(1, cmdLine1);
            cstmt.setDouble(2, cmdLine2);

            // execute the swap stored procedure with those arguments

            cstmt.execute();

            // see what happened with the out parameters!

            System.out.println("Out parameters afterwards: ");
            System.out.println("   (are they \"reversed?\")");
            System.out.println("--------------------------");
            System.out.println("Out Parameter 1: " + 
                               cstmt.getDouble(1));
            System.out.println("Out Parameter 2: " + 
                               cstmt.getDouble(2));

            cstmt.close();
            con.close();
        }

        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 class InOutParam