//  Source File Name: Cursor.sqlj  1.5
//  
//   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 a basic static 
//   embedded SQL SELECT application (SQLJ). 

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

#sql iterator CursorByName(String name, short dept) ;
#sql iterator CursorByPos(String, short ) ;

class Cursor 
{   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 Cursor Sample");

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

            //  Set the connection 
            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 Cursor [username password]\n");
            } 

            //  Set the default context 
            DefaultContext ctx = new DefaultContext(con);            
            DefaultContext.setDefaultContext(ctx);

            //  Enable transactions 
            con.setAutoCommit(false);

            //  Using cursors 
            try
            {   CursorByName cursorByName;
                CursorByPos  cursorByPos;

                String name = null;
                short  dept=0;
 
                //  Using the JDBC ResultSet cursor method 
                System.out.println("\nUsing the JDBC ResultSet cursor method");
                System.out.println(" with a 'bind by name' cursor ...\n");
 
                #sql cursorByName = { 
                      SELECT name, dept FROM staff WHERE job='Mgr' }; /* :rk.1:erk. */
                while (cursorByName.next()) /* :rk.2:erk. */
                {   name = cursorByName.name(); /* :rk.3:erk. */
                    dept = cursorByName.dept();

                    System.out.print (" name= " + name);
                    System.out.print (" dept= " + dept);
                    System.out.print ("\n");
                }
                cursorByName.close(); /* :rk.4:erk. */


                //  Using the SQLJ iterator cursor method 
                System.out.println("\nUsing the SQLJ iterator cursor method");
                System.out.println(" with a 'bind by position' cursor ...\n");

                #sql cursorByPos = { 
                       SELECT name, dept FROM staff WHERE job='Mgr' }; /* :rk.1:erk. */ /* :rk.2:erk. */
                while (true) 
                {   #sql { FETCH :cursorByPos INTO :name, :dept }; /* :rk.3:erk. */
                    if (cursorByPos.endFetch()) break;

                    System.out.print (" name= " + name); 
                    System.out.print (" dept= " + dept);
                    System.out.print ("\n");
                }
                cursorByPos.close(); /* :rk.4:erk. */
            }
            catch( Exception e )
            {   throw e; 
            } 
            finally
            {   //  Rollback the transaction 
                System.out.println("\nRollback the transaction...");
                #sql { ROLLBACK };
                System.out.println("Rollback done.");
            }
        }
        catch( Exception e )
        {   System.out.println (e);
        }
    }
}