//  Source File Name: Inpcli.java  1.1
//  
//   Licensed Materials -- Property of IBM 
//  
//   (c) Copyright International Business Machines Corporation, 1998. 
//       All Rights Reserved. 
//  
//   US Government Users Restricted Rights - 
//   Use, duplication or disclosure restricted by 
//   GSA ADP Schedule Contract with IBM Corp. 

//   Sample Program Inpcli - Calling the JDBC Input Stored Procedure 

//   Steps to run the sample: 
//   (1) create and populate the SAMPLE database (db2sampl) 
//   (2) (n)make Inpsrv 
//   (3) (n)make Inpcli 
//   (4) run Inpcli 

//   NOTES: (1) The jdk11_path database manager configuration parameter must 
//              be set 
//          (2) The CLASSPATH and shared library path environment variables 
//              must be set, as for any JDBC application. 
//          (3) Visit http://www.software.ibm.com/data/db2/java 
//              for current DB2 Java information 

//  For more information about this sample, refer to the README file. 

//  For more information on Programming in Java, refer to the  
//  "Programming in Java" section of the Application Development Guide. 

//  For more information on creating stored procedures, refer to the  
//  "Writing Stored Procedures" section of the Application Development Guide. 

//  For more information on building and running Java programs for DB2, 
//  refer to the "Building Java Applets and Applications" section of the  
//  Application Building Guide. 

//  For more information on the SQL language, refer to the SQL Reference. 

//   Class Inpcli does the following: 
//   (1) registers the stored procedure 
//   (2) calls the stored procedure 

import java.sql.*;              //  JDBC classes 

class Inpcli
{   static
    {   try
        {   Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
        } 
        catch (Exception e)
        {   System.out.println ("\n  Error loading DB2 Driver...\n");
            System.out.println (e);
            System.exit(1);
        }
    }

    public static void main (String argv[])
    {   try 
        {   System.out.println ("  Java Input Stored Procedure Sample");
            //  Connect to Sample database 

            Connection con = null;
            //  URL is jdbc:db2:dbname 
            String url = "jdbc:db2:sample";

            if (argv.length == 0) 
            {   //  connect with default id/password 
                con = DriverManager.getConnection(url);
            }
            else if (argv.length == 2)
            {   String userid = argv[0];
                String passwd = argv[1];

                //  connect with user-provided username and password 
                con = DriverManager.getConnection(url, userid, passwd);
            }
            else 
            {   throw new Exception("\nUsage: java Inpcli [username password]\n");
            } 
    
            //  Set AutoCommit 
            con.setAutoCommit(true);

            //  Variables used to call the stored procedure 
            String callName = "inputSTP";
            String storedProcName = "Inpsrv!inputStoredProcedure";
            String mode = "fenced";
            String tableName = "PRESIDENTS";
            String dataItem1 = "Washington";
            String dataItem2 = "Jefferson";
            String dataItem3 = "Lincoln";

            Statement stmt = con.createStatement (); //  for DROP/CREATE 

            //  drop the stored procedure if it exists 
            try 
            {   stmt.executeUpdate ("DROP PROCEDURE " + callName);
            }
            catch (Exception e)
            {   //  ignore this error 
            }

            //  construct a parameter list for the stored procedure and 
            //  register it in the system catalogs 
            String parameterList =
                 "(in    tableName      varchar(15)," +
                 " in    president1     varchar(15)," +
                 " in    president2     varchar(15)," +
               	 " in    president3     varchar(15))";

            System.out.println (
                 "\n  Registering Java stored procedure " + callName +
                 "\n     as " + storedProcName +
                 "\n     in " + mode + " mode");

            stmt.executeUpdate (
                 "CREATE PROCEDURE " + callName + parameterList +
                 " LANGUAGE JAVA " +
                 " PARAMETER STYLE DB2GENERAL " + mode +
                 " EXTERNAL NAME '" + storedProcName + "'");
     
            //  prepare the CALL statement 
            CallableStatement callableStmt;
            String sql = "Call " + callName + "(?,?,?,?) ";
            callableStmt = con.prepareCall (sql);

            //  set all parameters  
            callableStmt.setString (1, tableName);
            callableStmt.setString (2, dataItem1);
            callableStmt.setString (3, dataItem2);
            callableStmt.setString (4, dataItem3);

            //  call the stored procedure 
            boolean initialAutoCommit = con.getAutoCommit();
            con.setAutoCommit(false);  //  Enable transactions 
            try
            {   System.out.println (
                         "\n  Calling stored procedure : " + callName );
                callableStmt.execute ();

                //  Commit the transaction 
                con.commit();
            } 
            catch (Exception e)
            {   //  Rollback the transaction 
                con.rollback();
                throw e; 
            }
            finally
            {   //  Restore initial AutoCommit 
                con.setAutoCommit(initialAutoCommit);
            }

            callableStmt.close ();

            //  Drop the PRESIDENTS table created by the stored procedure 
            stmt.executeUpdate ("DROP TABLE " + tableName);
            System.out.println ("\n  Table PRESIDENTS dropped.");
            stmt.close ();
        }
        catch (Exception e)
        {   
		System.out.println (e);
        }
    }
}