Basic Samples

This section presents examples that demonstrate some of the basic essentials of SQLJ, including iterators and host expressions. The following samples are included here:

• Named Iterator--NamedIterDemo.sqlj

• Positional Iterator--PosIterDemo.sqlj

• Host Expressions--ExprDemo.sqlj

These samples are located in the following directory:

[Oracle Home]/sqlj/demo

Before beginning, connect to the database following the procedures described in "Set Up the Runtime Connection". Note that this includes creating the following SALES table:

CREATE TABLE SALES (
      ITEM_NUMBER NUMBER,
      ITEM_NAME CHAR(30),
      SALES_DATE DATE, 
      COST NUMBER, 
      SALES_REP_NUMBER NUMBER,
      SALES_REP_NAME CHAR(20));

Named Iterator--NamedIterDemo.sqlj

This example demonstrates the use of a named iterator.

For information about named iterators (and positional iterators as well), see "Multi-Row Query Results--SQLJ Iterators".

// ------------------ 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 );
    }

    try { Oracle.close(); } catch (SQLException e) { }
  }


  /* 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() ;
  }

}

Positional Iterator--PosIterDemo.sqlj

This example demonstrates the use of a positional iterator.

For information about positional iterators (and named iterators as well), see "Multi-Row Query Results--SQLJ Iterators".

// ---------------------- 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 ) ;
    }

    try { Oracle.close(); } catch (SQLException e) { }
  } //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

Host Expressions--ExprDemo.sqlj

This example demonstrates the use of host expressions.

For information about host expressions, see "Java Host Expressions, Context Expressions, and Result Expressions".

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


class ExprDemo
{

  public static void main( String[] arg )
  {
    try
    {
      new ExprDemo().runExample();
    }
    catch( SQLException e )
    {
      System.err.println( "Error running the example: " + e );
    }

    try
    {
      Oracle.close();
    } 
    catch( SQLException e ) { }
  }


  ExprDemo() 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");
  }


  int[] array;
  int indx;

  Integer integer;


  class Demo
  {
    int field = 0;
  }


  Demo obj = new Demo();

  int total;


  void printArray()
  {
    System.out.print( "array[0.." + (array.length-1) + "] = { " );

    int i;

    for( i=0;i<array.length;++i )
    {
      System.out.print( array[i] + "," );
    }

    System.out.println( " }" );
  }


  void printIndex()
  {
    System.out.println( "indx = " + indx );
  }


  void printTotal()
  {
    System.out.println( "total = " + total );
  }


  void printField()
  {
    System.out.println( "obj.field = " + obj.field );
  }


  void printInteger()
  {
    System.out.println( "integer = " + integer );
  }


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

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      Expressions 'indx++' and 'array[indx]' are evaluated in that order.
      Because 'indx++' increments the value of 'indx' from 1 to 2, the
      result will be stored in 'array[2]':

      Suggested Experiments:

       - Try preincrement operator instead of post-increment
       - See what happens if the array index goes out of bounds as a result
         of being manipulated in a host expression

    */

    array = new int[] { 1000,1001,1002,1003,1004,1005 };
    indx = 1;

    #sql { SELECT :(indx++) INTO :(array[indx]) FROM DUAL };

    printArray();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      Expressions 'array[indx]' and 'indx++' are evaluated in that order.
      The array reference is evaluated before the index is incremented,
      and hence the result will be stored in 'array[1]' (compare with the
      previous example):

    */

    array = new int[] { 1000,1001,1002,1003,1004,1005 };
    indx = 1;

    #sql { SET :(array[indx]) = :(indx++) };

    printArray();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      Expressions 'x.field' and 'y.field' both refer to the same variable,
      'obj.field'.  If an attempt is made to assign more than one results
      in what is only one storage location, then only the last assignment
      will remain in effect (so in this example 'obj.field' will contain 2
      after the execution of the SQL statement):

    */

    Demo x = obj;
    Demo y = obj;

    #sql { SELECT :(1), :(2) INTO :(x.field), :(y.field) FROM DUAL };

    printField();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      All expressions are evaluated before any are assigned.  In this
      example the 'indx' that appears in the second assignment will be
      evaluated before any of the assignments take place.  In particular,
      when 'indx' is being used to assign to 'total', its value has not
      yet been assigned to be 100.

      The following warning may be generated, depending on the settings
      of the SQLJ translator:

        Warning: Repeated host item indx in positions 1 and 3 in SQL
        block. Behavior is vendor-defined and non portable.

    */

    indx = 1;
    total = 0;

    #sql
    {
      BEGIN
        :OUT indx := 100;
        :OUT total := :IN (indx);
      END;
    };

    printIndex();
    printTotal();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      Expression 'indx++' in the following example is evaluated exactly
      once, despite appearing inside a SQL loop construct.  Its old value
      before increment is used repeatedly inside the loop, and its value
      is incremented only once, to 2.

    */

    indx = 1;
    total = 0;

    #sql
    {
      DECLARE
        n NUMBER;
        s NUMBER;
      BEGIN
        n := 0;
        s := 0;
        WHILE n < 100 LOOP
          n := n + 1;
          s := s + :IN (indx++);
        END LOOP;
        :OUT total := s;
      END;
    };

    printIndex();
    printTotal();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      In the next example there are two assignments to the same variable,
      each inside a different branch of an SQL 'if..then..else..end if'
      construct, so that only one of those will be actually executed at
      run-time.  However, assignments to OUT variable are always carried
      out, regardless of whether the SQL code that manipulates the return
      value has been executed or not.

      In the following example, only the first assignment is executed by
      the SQL; the second assignment is not executed.  When the control
      returns to Java from the SQL statement, the Java variable 'integer'
      is assigned twice: first with the value '1' it receives from the
      first SQL assignment, then with a 'null' value it receives from the
      second assignment that is never executed.  Because the assignments
      occur in this order, the final value of 'integer' after executing
      this SQL statement is undefined.

      The following warning may be generated, depending on the settings
      of the SQLJ translator:

        Warning: Repeated host item indx in positions 1 and 3 in SQL
        block. Behavior is vendor-defined and non portable.
      
      Suggested experiments:

       - Use a different OUT-variable in the 'else'-branch
       - Vary the condition so that the 'else'-branch gets executed

    */

    integer = new Integer(0);

    #sql
    {
      BEGIN
        IF 1 > 0 THEN
          :OUT integer := 1;
        ELSE
          :OUT integer := 2;
        END IF;
      END;
    };

    printInteger();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    */

  }
}

---