The method for describing a retrieval using a FETCH statement is explained as follows.

(a) Defining a class for an iterator with position specification and declaring an iterator object

• Standard interface version

For the standard interface version, define a class for an iterator with a position specification and declare an iterator object. class-name indicates a Java identifier. data-type-N indicates the data type of a Java variable that stores the N-th retrieval item in the FETCH statement.

 
 #sql modifier iterator class-name
               (data-type-1,data-type-2,...) ;
 modifier class-name iterator-object ;
 

• Native interface version
  For the native interface version, the specification is as follows:
  

  #sql modifier iterator class-name [ implements JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.ForUpdate ] [ with keyword=setting-value,...] (data-type-1,data-type-2,...) ; modifier class-name iterator-object ;

If an iterator is used in an UPDATE or DELETE statement, the JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.ForUpdate interface is inherited.

keyword in the WITH clause indicates the function of the iterator. Only a constant can be specified. Table 18-7 shows the combinations of keyword in the WITH clause and setting values.

Table 18-7 Combinations of keyword in the WITH clause and setting values

(b) Defining and opening a cursor

Substitute the result set from the SELECT statement into the declared iterator object.

 #sql [context] iterator-object = { SELECT-statement } ;

(c) Extracting the retrieval result

Specify an iterator object instead of a cursor name and execute a FETCH statement. The iterator object must be preceded by a colon.

 #sql [context] {
    FETCH :iterator-object INTO :variable-1,:variable-2,...} ;

(d) Determining NOT FOUND

Invoke the endFetch method for the iterator object and determine whether the result is NOT FOUND. If there is no row to be retrieved, true is returned. If the next row is found, false is returned. If the endFetch method is invoked after the cursor is closed, true is returned.

 while(! iterator-object.endFetch()) {
      processing-on-the-extracted-row
 }

(e) Closing the cursor

To close the cursor, invoke the close method.

 iterator-object.close() ;

An example of a retrieval using a FETCH statement follows:

 #sql public iterator ByPos(String, int);
                   :
 {
   ByPos positer;
   String name = null;
   int code = 0;
 
   #sql positer = { SELECT PNAME,PCODE FROM STOCK };
   #sql { FETCH :positer INTO :name,:code };
   while( !positer.endFetch() ){
     System.out.println(name + ":" + code);
     #sql { FETCH :positer INTO :name,:pcode };
   }
   positer.close();
 }

Using the fields in the iterator with a column name specification, read out each column of the retrieval result.

(a) Defining a class for an iterator with column name specification

Define the same name (not case sensitive) as the retrieval item as the class field. For the data type, specify the data type of the Java variable that receives the retrieval result. This class cannot be used for the native interface.

If the retrieval item is a value expression, a column name that includes a character that cannot be used in Java, for example, use an AS clause to define an alias for the retrieval item, and use that alias.

 #sql modifier iterator class-name
            (data-type-1 column-name-1,
             data-type-2 column-name-2,...) ;
 modifier class-name iterator-object;

(b) Defining and opening a cursor

Substitute the result set from the SELECT statement into the declared iterator object.

 #sql [context] iterator-object = { SELECT-statement } ;

(c) Extracting the next row and determining NOT FOUND

Invoke the next method for the iterator object and determine whether the result is NOT FOUND. If the result is NOT FOUND, TRUE is returned. If a row is found, FALSE is returned. After the cursor is opened, it is not positioned on the first line of the retrieval result until the first next method is executed.

 while(iterator-object.next()){
      processing-on-the-extracted-row
 }

(d) Acquiring the retrieval result

Read data out from each field of the iterator object. If the result is NOT FOUND or if data is read out after the cursor is closed, the result is undetermined. If data is read out when the data type of the field is the Java basic data type and the retrieval result is a null value, the SQLNullException object occurs.

Data substituted into a field is not reflected in the database.

 variable-1 = iterator-object.column-name-1 ;
 variable-2 = iterator-object.column-name-2 ;
              ...

(e) Closing the cursor

To close the cursor, invoke the close method for the iterator object.

 iterator-object.close() ;

An example of retrieval without using a FETCH statement follows:

Example:

 #sql public iterator ByName(String pname,
                            int pcode);
                  :
 {
    ByName nameiter;
    String s;
    int i;
 
    #sql nameiter = { SELECT PNAME, PCODE FROM STOCK };
    while( nameiter.next() ){
      s = nameiter.pname();
      i = nameiter.pcode();
      System.out.println(s + ":" + i);
    }
    nameiter.close();
 }

For the native interface, a cursor can be used to update data.

To use an UPDATE or DELETE statement to manipulate the row on which the cursor is positioned, specify an iterator instead of a cursor name. Note that when the class for the iterator is being defined, it must inherit the ForUpdate interface.

 
 #sql [context] { DELETE-statement WHERE CURRENT OF :iterator-object } ;
 
 #sql [context] { UPDATE-statement WHERE CURRENT OF :iterator-object } ;
 

An example of an update that uses an iterator follows:

 #sql public iterator ByPos
             implements JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.ForUpdate
             (String, int);
              :
 
 {
   ByPos positer;
   String name = null;
   int year = 0;
   int newyear;
 
   #sql positer = { SELECT FULLNAME, BIRTHYEAR FROM PEOPLE };
   #sql { FETCH :positer INTO :name,:year };
   while( !positer.endFetch() ){
     newyear=year+10;
     #sql { UPDATE PEOPLE SET YEAR=:newyear WHERE CURRENT OF :positer; };
   }
   positer.close();
 }