Multiple open instances of an iterator in an SQLJ application

Multiple instances of an iterator can be open concurrently in a single SQLJ application. One application for this ability is to open several instances of an iterator that uses host expressions. Each instance can use a different set of host expression values.

The following example shows an application with two concurrently open instances of an iterator.

Figure 1. Example of opening more than one instance of an iterator in a single application

…
ResultSet myFunc(String empid)  // Method to open an iterator and get a resultSet
{
  MyIter iter;
  #sql iter = {SELECT * FROM EMPLOYEE WHERE EMPNO = :empid};
  return iter.getResultSet();
}

// An application can call this method to get a resultSet for each
// employee ID. The application can process each resultSet separately.
…
ResultSet rs1 = myFunc("000100");  // Get employee record for employee ID 000100
…
ResultSet rs2 = myFunc("000200");  // Get employee record for employee ID 000200

As with any other iterator, you need to remember to close this iterator after the last time you use it to prevent excessive storage consumption.