// ------------------ Begin of file NamedIterDemo.sqlj -----------------------
//
// Invoke the SQLJ translator with the following command:
//    sqlj NamedIterDemo.sqlj
// Then run as 
//    java NamedIterDemo

/* Import useful classes.
**
** Note that java.sql.Date (and not java.util.Date) is being used.
*/

import java.sql.Date;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;

/* Declare an iterator.
**
** The comma-separated terms appearing in parentheses after the class name
** serve two purposes: they correspond to column names in the query results 
** that later occupy instances of this iterator class, and they provide
** names for the accessor methods of the corresponding column data.
**
** The correspondence between the terms and column names is case-insensitive,
** while the correspondence between the terms and the generated accessor names
** is always case-sensitive.
*/

#sql iterator SalesRecs(
      int item_number,
      String item_name,
      Date sales_date,
      double cost,
      Integer sales_rep_number,
      String sales_rep_name );


class NamedIterDemo
{

  public static void main( String args[] )
  {
    try
    {
      NamedIterDemo app = new NamedIterDemo();
      app.runExample();
    } 
    catch( SQLException exception )
    {
      System.err.println( "Error running the example: " + exception );
    }
  }


  /* Initialize database connection.
  **
  ** Before any #sql blocks can be executed, a connection to a database
  ** must be established.  The constructor of the application class is a
  ** convenient place to do this, since it is executed once, and only
  ** once, per application instance.
  */

  NamedIterDemo() throws SQLException
  {
    /* if you're using a non-Oracle JDBC Driver, add a call here to
	DriverManager.registerDriver() to register your Driver
    */

    // set the default connection to the URL, user, and password
    // specified in your connect.properties file
    Oracle.connect(getClass(), "connect.properties");
  }


  void runExample() throws SQLException
  {
    System.out.println();
    System.out.println( "Running the example." );
    System.out.println();


    /* Reset the database for the demo application.
    */

    #sql { DELETE FROM SALES };


    /* Insert a row into the cleared table.
    */

    #sql
    {
      INSERT INTO SALES VALUES(
            101,'Relativistic redshift recorder',
            TO_DATE('22-OCT-1997','dd-mon-yyyy'),
            10999.95,
            1,'John Smith')
    };


    /* Insert another row in the table using bind variables.
    */

    int     itemID = 1001;
    String  itemName = "Left-handed hammer";
    double  totalCost = 79.99;

    Integer salesRepID = new Integer(358);
    String  salesRepName = "Jouni Seppanen";
    Date    dateSold = new Date(97,11,6);

    #sql { INSERT INTO SALES VALUES( :itemID,:itemName,:dateSold,:totalCost,
          :salesRepID,:salesRepName) };


    /* Instantiate and initialize the iterator.
    **
    ** The iterator object is initialized using the result of a query.
    ** The query creates a new instance of the iterator and stores it in
    ** the variable 'sales' of type 'SalesRecs'.  SQLJ translator has
    ** automatically declared the iterator so that it has methods for
    ** accessing the rows and columns of the result set.
    */

    SalesRecs sales;

    #sql sales = { SELECT item_number,item_name,sales_date,cost,
          sales_rep_number,sales_rep_name FROM sales };


    /* Print the result using the iterator.
    **
    ** Note how the next row is accessed using method 'next()', and how
    ** the columns can be accessed with methods that are named after the
    ** actual database column names.
    */

    while( sales.next() )
    {
      System.out.println( "ITEM ID: " + sales.item_number() );
      System.out.println( "ITEM NAME: " + sales.item_name() );
      System.out.println( "COST: " + sales.cost() );
      System.out.println( "SALES DATE: " + sales.sales_date() );
      System.out.println( "SALES REP ID: " + sales.sales_rep_number() );
      System.out.println( "SALES REP NAME: " + sales.sales_rep_name() );
      System.out.println();
    }


    /* Close the iterator.
    **
    ** Iterators should be closed when you no longer need them.
    */

    sales.close() ;
  }

}