Data retrieval in SQLJ applications

SQLJ applications use a result set iterator to retrieve result sets. Like a cursor, a result set iterator can be non-scrollable or scrollable.

Just as in database applications in other languages, if you want to retrieve a single row from a table in an SQLJ application, you can write a SELECT INTO statement with a WHERE clause that defines a result table that contains only that row: 
#sql [myConnCtx] {SELECT DEPTNO INTO :hvdeptno
  FROM DEPARTMENT WHERE DEPTNAME="OPERATIONS"};

However, most SELECT statements that you use create result tables that contain many rows. In database applications in other languages, you use a cursor to select the individual rows from the result table. That cursor can be non-scrollable, which means that when you use it to fetch rows, you move the cursor serially, from the beginning of the result table to the end. Alternatively, the cursor can be scrollable, which means that when you use it to fetch rows, you can move the cursor forward, backward, or to any row in the result table.

This topic discusses how to use non-scrollable iterators. For information on using scrollable iterators, see "Use scrollable iterators in an SQLJ application".

A result set iterator is a Java™ object that you use to retrieve rows from a result table. Unlike a cursor, a result set iterator can be passed as a parameter to a method.

The basic steps in using a result set iterator are:
  1. Declare the iterator, which results in an iterator class
  2. Define an instance of the iterator class.
  3. Assign the result table of a SELECT to an instance of the iterator.
  4. Retrieve rows.
  5. Close the iterator.

There are two types of iterators: positioned iterators and named iterators. Positioned iterators extend the interface sqlj.runtime.PositionedIterator. Positioned iterators identify the columns of a result table by their position in the result table. Named iterators extend the interface sqlj.runtime.NamedIterator. Named iterators identify the columns of the result table by result table column names.