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.
Procedure
The steps in using a positioned iterator are:
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.
Create an instance of the iterator class.
You declare an object of the positioned iterator class to
retrieve rows from a result table.
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:
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.
Close the iterator.
Use the PositionedIterator.close method
to do this.
Example
The following code demonstrates how to declare and use a
positioned iterator. The numbers to the right of selected statements
correspond to the previously-described steps.Figure 1. Example of using a positioned iterator
#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
}