Positional iterators

The order of declaration of the Java™ variables in a positional iterator must match the order in which the SQL columns are returned.

For example, the following statement generates a positional iterator class called CustIter with six columns:

#sql iterator CustIter( int , String, String, String, String, String );

This iterator can hold the result set from the following SELECT statement:

SELECT customer_num, fname, lname,  address1, 
address2, phone
FROM   customer

You run the SELECT statement and populate the iterator object with the result set by using an Embedded SQLJ statement of the form:

#sql iterator-object = { SELECT ...};

For example:

CustIter cust_rec;
#sql  [ctx] cust_rec = { SELECT customer_num, fname, lname,  address1, 
address2, phone
FROM   customer 
};

You retrieve data from a positional iterator into host variables using the FETCH...INTO statement:

#sql { FETCH :cust_rec 
INTO :customer_num, :fname, :lname, 
:address1, :address2, :phone
};

The SQLJ translator checks that the types of the host variables in the INTO clause of the FETCH statement match the types of the iterator columns in corresponding positions.

The types of the SQL columns in the SELECT statement must be compatible with the types of the iterator. These type conversions are checked at translation time if you perform online checking. For information about setting up online checking, see Online checking. For a listing of SQL and Java type mappings, see SQL and Java type mappings.