INFORMIX
IBM Informix Embedded SQLJ User's Guide
Chapter 4: The Embedded SQLJ Language
Contents Index

Handling Result Sets

Embedded SQLJ uses iterator objects to manage result sets returned by SELECT statements. A result set iterator is a Java object from which you can retrieve the data returned from the database. Iterator objects can be passed as parameters to methods and manipulated like other Java objects.

Important: Names of iterator classes must be unique within an application.
When you declare an iterator object, you specify a set of Java variables to match the SQL columns that your SELECT statement returns. There are two types of iterators: positional and named.

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:

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

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

For example:

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

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 on-line checking. See "On-Line Checking" for information about setting up on-line checking. See "SQL and Java Type Mappings" for a listing of SQL and Java type mappings.

Named Iterators

The name of each Java variable of a named iterator must match the name of a column returned by your SELECT statement; order is irrelevant. The matching of SQL column names and iterator column names is case insensitive.

For example, the following statement generates a named iterator class called CustRec:

This iterator can hold the result set of any query that returns the columns defined in the iterator class. You use accessor methods of the same name as each iterator column to obtain the returned data, as shown in the example in "A Simple Embedded SQLJ Program". The SQLJ translator uses the iterator column names to create accessor methods. Iterator column names are case sensitive; therefore, you must use the correct case when you specify an accessor method.

You cannot use the FETCH...INTO statement with named iterators.

The next() method of the iterator object advances processing to successive rows of the result set. It returns FALSE after it fails to find a row to retrieve.

The Java compiler detects type mismatches for the accessor methods.

The validity of the types and names of the iterator columns and their related columns in the SELECT statement are checked at translation time if you perform on-line checking. See "On-Line Checking" for information about setting up on-line checking.

Using Column Aliases

When an expression returned by a SELECT statement has an SQL name that is not a valid Java identifier, use SQL column aliases to rename them. For example, the name Not valid for Java is acceptable as a column name in SQL, but not as a Java identifier. You can use a column alias that has a name acceptable as a Java identifier by using the AS clause:

When you create a named iterator class for this query, you specify the column alias name for the Java variable, as in:

Iterator Methods

Both named and positional iterator objects have the following methods:

    Returns the number of rows retrieved by the iterator object.

    Closes the iterator; raises an SQLException if the iterator is already closed.

    Returns TRUE after the iterator's close() method has been called; otherwise FALSE.

Positional iterators also have the endFetch() method. The endFetch() method returns TRUE when no more rows are available.

Named iterators also have the next() method. The next() method advances processing to successive rows of the result set. It returns FALSE after it fails to find a row to retrieve. An example of how to use the next() method appears in "Named Iterators".

Positioned Updates and Deletes

To perform positioned updates and deletes in a result set, you use the WHERE CURRENT OF clause with a host variable that contains an iterator object. For example:

At runtime, the variable :iter must contain an open iterator object that contains a result set selected from the same table accessed by the query in either delete_statement or update_statement. The current row of that iterator object is deleted or updated.




IBM Informix Embedded SQLJ User's Guide, Version 1.0
Copyright © 1999, IBM Corporation. All rights reserved.