//  Source File Name: Applt.sqlj  1.2
//  
//   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. 

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

//  Run this sample with the following steps: 
//  (1) create and populate the "sample" database with the following  
//      command: db2sampl 
//  (2) customize Applt.html with your server, TCP/IP port number,  
//      user ID, and password 
//  (3) start the DB2 JDBC server on a TCP/IP port with the following 
//      command: db2jstrt portno 
//  (4) run this sample: install the applet and the HTML file according 
//      to the documentation, and view it in a Java-enabled browser, or 
//      view it locally with the following command:  
//         appletviewer Applt.html 

//  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 java.awt.*;
import java.applet.Applet;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

#sql iterator Applt_Cursor1 (String empno, String firstnme) ;
#sql iterator Applt_Cursor2 (String) ;

public class Applt extends Applet
{

   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.net.DB2Driver").newInstance();
      }
      catch (Exception e)
      {
         e.printStackTrace();
      }
   }

   Connection con;

   public void init()
   {


      try
      {
         DefaultContext ctx = DefaultContext.getDefaultContext();
         if (ctx == null)
         {
            //  get parameter values from the html page 
            String server = getParameter("server");
            String port = getParameter("port");

            //  construct the URL ( sample is the database name ) 
            String url = "jdbc:db2://"+server+":"+port+"/sample";

            String userid = getParameter("userid");
            String password = getParameter("password");

            //  connect to database with userid and password 
            con = DriverManager.getConnection(url, userid, password );
            con.setAutoCommit(false);

            ctx = new DefaultContext(con);
            DefaultContext.setDefaultContext(ctx);
         }
      }
      catch( Exception e )
      {
         e.printStackTrace();
      }
   }

   public void paint(Graphics g)
   {
      try
      {
         Applt_Cursor1 cursor1;
         Applt_Cursor2 cursor2;

         String str1 = null;
         String str2 = null;
         long   count1;

         //  retrieve data from database 
         g.drawString("First, let's retrieve some data from the database...", 10, 10);

         #sql cursor1 = { SELECT empno, firstnme from employee };
         g.drawString("Received results:", 10, 25);

         //  display the result set 
         //  cursor1.next() returns false when there are no more rows 
         int y = 50;
         int i = 0;
         while (cursor1.next() && (i<2))
         {
            i++;
            str1 = cursor1.empno();
            str2 = cursor1.firstnme();
            String oneLine = " empno= " + str1 + " firstname= " + str2;
            g.drawString(oneLine, 20, y );
            y = y + 15;
         }
         cursor1.close();

         //  retrieve number of employee from the database 
         y = y + 40;
         g.drawString("Retrieve the number of rows in employee table...", 10, y);
         #sql { SELECT count(*) into :count1 from employee };
         y = y + 15;
         if (1 == count1)
            g.drawString("There is " + count1 + " row in employee table.", 10, y);
         else
            g.drawString("There are " + count1 + " rows in employee table.", 10, y);

         //  update the database 
         y = y + 40;
         g.drawString("Now, update the database...", 10, y);
         #sql { UPDATE employee set firstnme = 'SHILI' where empno = '000010' };

         //  retrieve the updated data from the database 
         y = y + 40;
         g.drawString("Retrieve the updated data from the database...", 10, y);
         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 
         y = y + 15;
         g.drawString("Received results:", 10, y);
         y = y + 25;
         while (true)
         {
            #sql { FETCH :cursor2 INTO :str2 };
            if (cursor2.endFetch()) break;

            String oneLine = " empno= " + str1 + " firstname= " + str2;
            g.drawString(oneLine, 20, y );
            y = y + 15;
         }
         cursor2.close();

         //  rollback the update 
         y = y + 40;
         g.drawString("Now, rollback the update...", 10, y);
         #sql { ROLLBACK work };
         y = y + 15;
         g.drawString("Rollback done.", 10, y);
      }
      catch( Exception e )
      {
         e.printStackTrace();
      }
   }
}