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

//   This is a basic dynamic SQL application that uses 
//   the JDBC application driver to access a DB2 database. 

//  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 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. 

import java.sql.*;

class Varinp 
{   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 Varinp 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 Varinp [username password]\n");
            } 

            //  Enable transactions 
            con.setAutoCommit(false);

            //  Perform dynamic SQL using JDBC 
            try
            {   PreparedStatement pstmt1 = con.prepareStatement(
                 "SELECT name, dept FROM staff WHERE job = ? FOR UPDATE OF job"); /* :rk.1:erk. */
                //  set cursor name for the positioned update statement 
                pstmt1.setCursorName("c1");                                         /* :rk.2:erk. */
                pstmt1.setString(1, "Mgr"); 
                ResultSet rs = pstmt1.executeQuery();                               /* :rk.3:erk. */

                PreparedStatement pstmt2 = con.prepareStatement(
                    "UPDATE staff SET job = ? WHERE CURRENT OF c1");            /* :rk.4:erk. */
                pstmt2.setString(1, "Clerk"); 

                System.out.print("\n");
                while( rs.next() )                                                  /* :rk.5:erk. */ 
                {   String name = rs.getString("name");
                    short  dept = rs.getShort("dept"); 
                    System.out.println(name + " in dept. " + dept + " will be demoted to Clerk");

                    pstmt2.executeUpdate();                                          /* :rk.6:erk. */
                };
 
                rs.close();
                pstmt1.close();                                                     /* :rk.7:erk. */
                pstmt2.close();
            }
            catch( Exception e )
            {   throw e; 
            } 
            finally
            {   //  Rollback the transaction 
                System.out.println("\nRollback the transaction...");
                con.rollback();
                System.out.println("Rollback done.");
            }
        } 
        catch( Exception e )
        {   System.out.println(e);
        } 
    }
}