// ---------------------- Begin of file PosIterDemo.sqlj ---------------------
//
// Invoke the SQLJ translator as follows:
//      sqlj PosIterDemo.sqlj
// Then run the program using
//      java PosIterDemo


import java.sql.* ;                   // JDBC classes
import oracle.sqlj.runtime.Oracle;    // Oracle class for connecting

/* Declare a ConnectionContext class named PosIterDemoCtx. Instances of this 
   class can be used to specify where SQL operations should execute. */
#sql context PosIterDemoCtx;

/* Declare a positional iterator class named FetchSalesIter.*/
#sql iterator FetchSalesIter (int, String, Date, double);

class PosIterDemo {

  private PosIterDemoCtx ctx = null;  // holds the database connection info

  /* The constructor sets up a database connection. */
  public PosIterDemo() {
    try 
    {  
      /* if you're using a non-Oracle JDBC Driver, add a call here to
	DriverManager.registerDriver() to register your Driver
      */

      // get a context object based on the URL, user, and password
      // specified in your connect.properties file
      ctx = 
	new PosIterDemoCtx(Oracle.getConnection(getClass(),
                           "connect.properties"));
    } 
    catch (Exception exception) 
    {	System.err.println (
	"Error setting up database connection: " + exception);
    }
   }

  //Main method
  public static void main (String args[])
  {
    PosIterDemo posIter = new PosIterDemo();

    try 
    { 
      //Run the example
      posIter.runExample() ; 

      //Close the connection
      posIter.ctx.close() ;
    } 
    catch (SQLException exception)
    { System.err.println (
      "Error running the example: " + exception ) ;
    }
  } //End of method main


  //Method that runs the example
  void runExample() throws SQLException
  {

    /* Reset the database for the demo application.    */
    #sql [ctx] { DELETE FROM SALES 
                   -- Deleting sales rows

               };

    insertSalesRecord
    ( 250, "widget1", new Date(97, 9, 9), 12.00, 
      new Integer(218), "John Doe"
    ) ;
    
    insertSalesRecord
    ( 267, "thing1", new Date(97, 9, 10), 700.00, 
      new Integer(218), "John Doe"
    ) ;
   
    insertSalesRecord
    ( 270, "widget2", new Date(97, 9, 10), 13.00, 
      null, "Jane Doe" // Note: Java null is same as SQL null
    ) ;
    
    System.out.println("Sales records before delete") ;
    printRecords(fetchSales()) ;

    // Now delete some sales records
    Date delete_date;
    #sql [ctx] { SELECT MAX(sales_date) INTO :delete_date
			 FROM SALES };

    #sql [ctx] { DELETE FROM SALES WHERE sales_date = :delete_date };

    System.out.println("Sales records after delete") ;
    printRecords(fetchSales()) ;
	   
  } //End of method runExample


  //Method to select all records from SALES through a positional iterator
  FetchSalesIter fetchSales() throws SQLException {
    FetchSalesIter f;

    #sql [ctx]  f = { SELECT item_number, item_name, sales_date, cost
		      FROM sales };
    return f;
  }

  //Method to print rows using a FetchSalesIter
  void printRecords(FetchSalesIter salesIter) throws SQLException
  {
    int item_number = 0; 
    String item_name = null;
    Date sales_date = null;
    double cost = 0.0;

    while (true) 
    {
      #sql { FETCH :salesIter 
             INTO :item_number, :item_name, :sales_date, :cost 
           };
      if (salesIter.endFetch()) break;

      System.out.println("ITEM NUMBER: " + item_number) ;
      System.out.println("ITEM NAME: " + item_name) ;
      System.out.println("SALES DATE: " + sales_date) ;
      System.out.println("COST: " + cost) ;
      System.out.println() ;
    }
    
    //Close the iterator since we are done with it.
    salesIter.close() ;

  } //End of method runExample


  //Method to insert one row into the database
  void insertSalesRecord(
    int item_number,
    String item_name,
    Date sales_date,
    double cost,
    Integer sales_rep_number,
    String sales_rep_name)

  throws SQLException
  {
    #sql [ctx] {INSERT INTO SALES VALUES 
                (:item_number, :item_name, :sales_date, :cost,
                 :sales_rep_number, :sales_rep_name
                )
    } ;
  } //End of method insertSalesRecord

} //End of class PosIterDemo

//End of file PosIterDemo.sqlj