IBM Informix Embedded SQLJ User's Guide

######

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:

    #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 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:

    #sql iterator CustRec(
    int    customer_num,
    String fname,
    String lname ,
    String company ,
    String address1 ,
    String address2 ,
    String city ,
    String state ,
    String zipcode ,
    String phone
    );
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.

    // Declare Iterator of type CustRec
    CustRec cust_rec;

    #sql cust_rec = { SELECT *  FROM customer };

    int row_cnt = 0;
    while ( cust_rec.next() )
    {
    System.out.println("===================================");
    System.out.println("CUSTOMER NUMBER :" +
    cust_rec.customer_num());
    System.out.println("FIRST NAME      :" + cust_rec.fname());
    System.out.println("LAST NAME       :" + cust_rec.lname());
    System.out.println("COMPANY         :" + cust_rec.company());
    System.out.println("ADDRESS         :" + cust_rec.address1()
    +"\n" +
    "                 " + cust_rec.address2());
    System.out.println("CITY            :" + cust_rec.city());
    System.out.println("STATE           :" + cust_rec.state());
    System.out.println("ZIPCODE         :" + cust_rec.zipcode());
    System.out.println("PHONE           :" + cust_rec.phone());
    System.out.println("===================================");
    System.out.println("\n\n");
    row_cnt++;
    }
    System.out.println("Total No Of rows Selected :" + row_cnt);
    cust_rec.close() ;
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:

    SELECT "Not valid for Java" AS "Col1" FROM tablename
When you create a named iterator class for this query, you specify the column alias name for the Java variable, as in:

    #sql iterator Iterator_name (String Col1);
### Iterator Methods Both named and positional iterator objects have the following methods: rowCount() Returns the number of rows retrieved by the iterator object. close() Closes the iterator; raises an SQLException if the iterator is already closed. isClosed() 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:

    #sql { delete_statement/update_statement
                  WHERE CURRENT OF :iter };
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.