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

/**
 * calls a PL/SQL stored function how_many, to find
 *    out how many copies are on hand of a title
 *    given as a command-line argument, and then prints the
 *    result 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 Sharon Tuttle (modified)
 * @version 3-13-13
 */

public class CallHowMany
{
    /**
     * calls a PL/SQL stored function how_many for the
     *    provided title and prints how many copies of
     *    that title are on-hand to the screen
     *
     * @param args the name of the title of interest
     */

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

        if (args.length != 1)
	{
            System.out.println("CallHowMany: needs exactly 1 "
                + "command-line argument, a title \n(use quotes "
                + "around if necessary)");
            System.exit(0);
        }

        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 stored functon call (but do not
	    //     actually call it yet)

            CallableStatement cStmt =
                con.prepareCall("begin ? := how_many(?); end;");

            // register returned value as if it were
            //    an OUT parameter?!

            cStmt.registerOutParameter(1, java.sql.Types.INTEGER);
            cStmt.setString(2, args[0]);

            // execute the stored function with those arguments

            cStmt.execute();

            // see what you get

            System.out.println("Have " + cStmt.getInt(1) 
                + " copies of " + args[0]); 

            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 CallHowMany