Using a positioned iterator in an SQLJ application

Use a positioned iterator to refer to columns in a result table by their position in the result set.

The steps in using a positioned iterator are:

1. Declare the iterator.
   You declare any result set iterator using an iterator declaration clause. This causes an iterator class to be created that has the same name and attributes as the iterator. For a positioned iterator, the iterator declaration clause specifies the following information:

   • The name of the iterator
   • A list of Java™ data types
   • Information for a Java class declaration, such as whether the iterator is public or static
   • A set of attributes, such as whether the iterator is holdable, or whether its columns can be updated

   The data type declarations represent columns in the result table and are referred to as columns of the result set iterator. The columns of the result set iterator correspond to the columns of the result table, in left-to-right order. For example, if an iterator declaration clause has two data type declarations, the first data type declaration corresponds to the first column in the result table, and the second data type declaration corresponds to the second column in the result table.

   You need to specify Java data types in the iterators that closely match the corresponding column data types. See "Java, JDBC, and SQL data types" for a list of the best mappings between Java data types and column data types.

   You can declare an iterator in a number of ways. However, because a Java class underlies each iterator, you need to ensure that when you declare an iterator, the underlying class obeys Java rules. For example, iterators that contain a with-clause must be declared as public. Therefore, if an iterator needs to be public, it can be declared only where a public class is allowed. The following list describes some alternative methods of declaring an iterator:

   • As public, in a source file by itself

     This is the most versatile method of declaring an iterator. This method lets you use the iterator declaration in other code modules, and provides an iterator that works for all SQLJ applications. In addition, there are no concerns about having other top-level classes or public classes in the same source file.

   • As a top-level class in a source file that contains other top-level class definitions

     Java allows only one public, top-level class in a code module. Therefore, if you need to declare the iterator as public, such as when the iterator includes a with-clause, no other classes in the code module can be declared as public.

   • As a nested static class within another class

     Using this alternative lets you combine the iterator declaration with other class declarations in the same source file, declare the iterator and other classes as public, and make the iterator class visible from other code modules or packages. However, when you reference the iterator from outside the nesting class, you must fully-qualify the iterator name with the name of the nesting class.

   • As an inner class within another class

     When you declare an iterator in this way, you can instantiate it only within an instance of the nesting class. However, you can declare the iterator and other classes in the file as public.

     You cannot cast a JDBC ResultSet to an iterator if the iterator is declared as an inner class. This restriction does not apply to an iterator that is declared as a static nested class. See "Use SQLJ and JDBC in the same application" for more information on casting a ResultSet to a iterator.

2. Create an instance of the iterator class.

   You declare an object of the positioned iterator class to retrieve rows from a result table.

3. Assign the result table of a SELECT to an instance of the iterator.
   To assign the result table of a SELECT to an iterator, you use an SQLJ assignment clause. The format of the assignment clause for a positioned iterator is:

   #sql context-clause iterator-object={select-statement};

4. Retrieve rows.

   Do this by executing FETCH statements in executable clauses in a loop. The FETCH statements looks the same as a FETCH statements in other languages.

   To test whether you have retrieved all rows, invoke the PositionedIterator.endFetch method after each FETCH. endFetch returns a boolean with the value true if the FETCH failed because there are no rows to retrieve.

5. Close the iterator.

   Use the PositionedIterator.close method to do this.

#sql iterator ByPos(String,Date); // Declare positioned iterator ByPos  1 
{
  …
  ByPos positer;                    // Declare object of ByPos class    2 
  String name = null;               // Declare host variables
  Date hrdate;
  #sql [ctxt] positer = 
    {SELECT LASTNAME, HIREDATE FROM EMPLOYEE};                          3 
                                    // Assign the result table of the SELECT
                                    // to iterator object positer
  #sql {FETCH :positer INTO :name, :hrdate };                           4 
                                    // Retrieve the first row
  while (!positer.endFetch())       // Check whether the FETCH returned a row
  { System.out.println(name + " was hired in " +
      hrdate);
    #sql {FETCH :positer INTO :name, :hrdate };
                                    // Fetch the next row
  }
  positer.close();                  // Close the iterator               5 
}