Using scrollable iterators in an SQLJ application

In addition to moving forward, one row at a time, through a result table, you might want to move backward or go directly to a specific row. The IBM® Data Server Driver for JDBC and SQLJ provides this capability.

About this task

An iterator in which you can move forward, backward, or to a specific row is called a scrollable iterator. A scrollable iterator in SQLJ is equivalent to the result table of a database cursor that is declared as SCROLL.

Like a scrollable cursor, a scrollable iterator can be insensitive or sensitive. A sensitive scrollable iterator can be static or dynamic. Insensitive means that changes to the underlying table after the iterator is opened are not visible to the iterator. Insensitive iterators are read-only. Sensitive means that changes that the iterator or other processes make to the underlying table are visible to the iterator. Asensitive means that if the cursor is a read-only cursor, it behaves as an insensitive cursor. If it is not a read-only cursor, it behaves as a sensitive cursor.

If a scrollable iterator is static, the size of the result table and the order of the rows in the result table do not change after the iterator is opened. This means that you cannot insert into result tables, and if you delete a row of a result table, a delete hole occurs. If you update a row of the result table so that the row no longer qualifies for the result table, an update hole occurs. Fetching from a hole results in an SQLException.

If a scrollable iterator is dynamic, the size of the result table and the order of the rows in the result table can change after the iterator is opened. Rows that are inserted or deleted with INSERT and DELETE statements that are executed by the same application process are immediately visible. Rows that are inserted or deleted with INSERT and DELETE statements that are executed by other application processes are visible after the changes are committed.

Important: Db2® on Linux®, UNIX, and Windows systems servers do not support dynamic scrollable cursors. You can use dynamic scrollable iterators in your SQLJ applications only if those applications access data on Db2 for z/OS® servers, at Version 9 or later.

Procedure

To create and use a scrollable iterator, you need to follow these steps:

Specify an iterator declaration clause that includes the following clauses:
- implements sqlj.runtime.Scrollable
  This indicates that the iterator is scrollable.
- with (sensitivity=sensitivity-attribute) or with (sensitivity=sensitivity-attribute, dynamic=true|false)
  sensitivity-attribute indicates whether update or delete operations on the underlying table can be visible to the iterator. Possible values are sqlj.runtime.ResultSetIterator.SENSITIVE, sqlj.runtime.ResultSetIterator.INSENSITIVE, or sqlj.runtime.ResultSetIterator.ASENSITIVE. sqlj.runtime.ResultSetIterator.ASENSITIVE is the default.
  
  dynamic=true|false indicates whether the size of the result table or the order of the rows in the result table can change after the iterator is opened. The default value of dynamic is false.
The iterator can be a named or positioned iterator.
Example: The following iterator declaration clause declares a positioned, sensitive, dynamic, scrollable iterator:
```
#sql public iterator ByPos
  implements sqlj.runtime.Scrollable
  with (sensitivity=sqlj.runtime.ResultSetIterator.SENSITIVE, dynamic=true)
  (String);
```
Example: The following iterator declaration clause declares a named, insensitive, scrollable iterator:
```
#sql public iterator ByName
  implements sqlj.runtime.Scrollable
  with (sensitivity=sqlj.runtime.ResultSetIterator.INSENSITIVE) (String EmpNo);
```
Restriction: You cannot use a scrollable iterator to select columns with the following data types from a table on a Db2 on Linux, UNIX, and Windows systems server:
- LONG VARCHAR
- LONG VARGRAPHIC
- BLOB
- CLOB
- XML
- A distinct type that is based on any of the previous data types in this list
- A structured type
Create an iterator object, which is an instance of your iterator class.
If you want to give the SQLJ runtime environment a hint about the initial fetch direction, use the setFetchDirection(int direction) method. direction can be FETCH_FORWARD or FETCH_REVERSE. If you do not invoke setFetchDirection, the fetch direction is FETCH_FORWARD.

For each row that you want to access:

For a named iterator, perform the following steps:

Position the cursor using one of the methods listed in the following table.

Table 1. sqlj.runtime.Scrollable methods for positioning a scrollable cursor
Method	Positions the cursor
first^5.a.i	On the first row of the result table
last^5.a.i	On the last row of the result table
previous^5.a.i,5.a.ii	On the previous row of the result table
next	On the next row of the result table
`absolute(int n)`^{5.a.i,5.a.iii}	If n>0, on row n of the result table. If n<0, and m is the number of rows in the result table, on row m+n+1 of the result table.
`relative(int n)`^5.a.i,5.a.iv	If n>0, on the row that is n rows after the current row. If n<0, on the row that is n rows before the current row. If n=0, on the current row.
afterLast^5.a.i	After the last row in the result table
beforeFirst^5.a.i	Before the first row in the result table
Notes: This method does not apply to connections to IBM Informix®. If the cursor is after the last row of the result table, this method positions the cursor on the last row. If the absolute value of n is greater than the number of rows in the result table, this method positions the cursor after the last row if n is positive, or before the first row if n is negative. Suppose that m is the number of rows in the result table and x is the current row number in the result table. If n>0 and x+n>m, the iterator is positioned after the last row. If n<0 and x+n<1, the iterator is positioned before the first row.

If you need to know the current cursor position, use the getRow, isFirst, isLast, isBeforeFirst, or isAfterLast method to obtain this information.
If you need to know the current fetch direction, invoke the getFetchDirection method.
Use accessor methods to retrieve the current row of the result table.
If update or delete operations by the iterator or by other means are visible in the result table, invoke the getWarnings method to check whether the current row is a hole.

For a positioned iterator, perform the following steps:

Use a FETCH statement with a fetch orientation clause to position the iterator and retrieve the current row of the result table. Table 2 lists the clauses that you can use to position the cursor.

Table 2. FETCH clauses for positioning a scrollable cursor
Method	Positions the cursor
FIRST^5.a.i	On the first row of the result table
LAST^5.a.i	On the last row of the result table
PRIOR^5.a.i,5.a.ii	On the previous row of the result table
NEXT	On the next row of the result table
ABSOLUTE(n)^{5.a.i,5.a.iii}	If n>0, on row n of the result table. If n<0, and m is the number of rows in the result table, on row m+n+1 of the result table.
RELATIVE(n)^5.a.i,5.a.iv	If n>0, on the row that is n rows after the current row. If n<0, on the row that is n rows before the current row. If n=0, on the current row.
AFTER^5.a.i,5.a.v	After the last row in the result table
BEFORE^5.a.i,5.a.v	Before the first row in the result table
Notes: This value is not supported for connections to IBM Informix If the cursor is after the last row of the result table, this method positions the cursor on the last row. If the absolute value of n is greater than the number of rows in the result table, this method positions the cursor after the last row if n is positive, or before the first row if n is negative. Suppose that m is the number of rows in the result table and x is the current row number in the result table. If n>0 and x+n>m, the iterator is positioned after the last row. If n<0 and x+n<1, the iterator is positioned before the first row. Values are not assigned to host expressions.

If update or delete operations by the iterator or by other means are visible in the result table, invoke the getWarnings method to check whether the current row is a hole.

Invoke the close method to close the iterator.

Example

The following code demonstrates how to use a named iterator to retrieve the employee number and last name from all rows from the employee table in reverse order. The numbers to the right of selected statements correspond to the previously-described steps.

#sql context Ctx;            // Create connection context class Ctx
#sql  iterator ScrollIter implements sqlj.runtime.Scrollable         1 
  (String EmpNo, String LastName);
{
  …
  Ctx ctxt =
   new Ctx("jdbc:db2://sysmvs1.stl.ibm.com:5021/NEWYORK",
   userid,password,false);   // Create connection context object ctxt
                             // for the connection to NEWYORK
  ScrollIter scrliter;                                               2   
  #sql [ctxt]
    scrliter={SELECT EMPNO, LASTNAME FROM EMPLOYEE};                
  scrliter.afterLast();                                              
  while (scrliter.previous())                                        4a 
  {
    System.out.println(scrliter.EmpNo() + " "                        4c 
      + scrliter.LastName());   
  }
  scrliter.close();                                                  5 
}