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

//   This sample program shows how to write an SQLJ application using 
//   the JDBC application driver to access a DB2 database. 

//  For more information about these samples, 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.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

#sql iterator App_Cursor1 (String empno, String firstnme) ;
#sql iterator App_Cursor2 (String) ;

class App 
{

   static
   {
      try
      {
         //  register the driver with DriverManager 
         //  The newInstance() call is needed for the sample to work with 
         //  JDK 1.1.1 on OS/2, where the Class.forName() method does not 
         //  run the static initializer. For other JDKs, the newInstance 
         //  call can be omitted. 
         Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
      }
      catch (Exception e)
      {
         e.printStackTrace();
      }
   }

   public static void main(String argv[])
   {
      try
      {
         App_Cursor1 cursor1;
         App_Cursor2 cursor2;

         String str1 = null;
         String str2 = null;
         int   count1;

         Connection con = null;

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

         DefaultContext ctx = DefaultContext.getDefaultContext();
         if (ctx == null) {
            try {
              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 {
                System.out.println("\nUsage: java App [username password]\n");
                System.exit(0);
              }
              con.setAutoCommit(false);
              ctx = new DefaultContext(con);
            }
          catch (SQLException e) {
            System.out.println("Error: could not get a default context");
            System.err.println(e) ;
            System.exit(1);
          }
 
          DefaultContext.setDefaultContext(ctx);
         }

         //  retrieve data from the database 
         System.out.println("Retrieve some data from the database...");
         #sql cursor1 = { SELECT empno, firstnme from employee };

         //  display the result set 
         //  cursor1.next() returns false when there are no more rows 
         System.out.println("Received results:");
         while (cursor1.next()) {
            str1 = cursor1.empno();
            str2 = cursor1.firstnme();

            System.out.print (" empno= " + str1);
            System.out.print (" firstname= " + str2);
            System.out.print ("\n");
         }
         cursor1.close();

         //  retrieve number of employee from the database 
         System.out.println("\nRetrieve the number of rows in employee table...");
         #sql { SELECT count(*) into :count1 from employee };
         if (1 == count1)
            System.out.println ("There is " + count1 + " row in employee table.");
         else
            System.out.println ("There are " + count1 + " rows in employee table.");

         //  update the database 
         System.out.println("\n\nUpdate the database... ");
         #sql { UPDATE employee set firstnme = 'SHILI' where empno = '000010' };

         //  retrieve the updated data from the database 
         System.out.println("\nRetrieve the updated data from the database...");
         str1 = "000010";
         #sql cursor2 = { SELECT firstnme from employee where empno = :str1 };

         //  display the result set 
         //  cursor2.next() returns false when there are no more rows 
         System.out.println("Received results:");
         while (true) {
            #sql { FETCH :cursor2 INTO :str2 };
            if (cursor2.endFetch()) break;

            System.out.print (" empno= " + str1);
            System.out.print (" firstname= " + str2);
            System.out.print ("\n");
         }
         cursor2.close();

         //  rollback the update 
         System.out.println("\n\nRollback the update...");
         #sql { ROLLBACK work };
         System.out.println("Rollback done.");
      }
      catch( Exception e )
      {
         e.printStackTrace();
      }
   }
}