Optimizing scrollable cursors

A programming pattern to get fresh data from scrollable cursors.

Generally, when using scrollable cursors, the database server or the database client software (i.e. the application) will make a static copy of the result set produced by the SELECT statement. For example, when using an IBM® Informix® database engine, each scrollable cursor will create a temporary table to hold the result set. Thus, if the SELECT statement returns all columns of the table(s) in the FROM clause, the database software will make a copy of all these values. This practice has two disadvantages: A lot of resources are consumed, and the data is static.

A good programming pattern to save resources and always get fresh data from the database server is to declare two cursors based on the primary key usage, if the underlying database table has a primary key (or unique index constraint): The first cursor must be a scrollable cursor that executes the SELECT statement, but returns only the primary keys. The SELECT statement of this first cursor is typically assembled at runtime with the where-part produced by a CONSTRUCT interactive instruction, to give a subset of the rows stored in the database. The second cursor (actually, a PREPARE/EXECUTE statement handle) performs a single-row SELECT statement listing all columns to be fetched for a given record, based on the primary key value of the current row in the scrollable cursor list. The second statement must use a ? question mark place holder to execute the single-row SELECT with the current primary key as SQL parameter.

If the primary key SELECT statement needs to be ordered, check that the database engine allows that columns used in the ORDER BY clause do not need to appear in the SELECT list. For example, this was the case with IBM Informix servers prior to version 9.4. If needed, the SELECT list can be completed with the columns used in ORDER BY, you can then just list the variable that holds the primary key in the INTO clause of FETCH.

Note also that the primary key result set is static. That is, if new rows are inserted in the database or if rows referenced by the scroll cursor are deleted after the scroll cursor was opened, the result set will be outdated. In this case, you can refresh the primary key result set by re-executing the scroll cursor with CLOSE/OPEN commands.

MAIN
   DEFINE wp VARCHAR(500)
   DATABASE test1
   -- OPEN FORM / DISPLAY FORM with c_id and c_name fields
   ...
   -- CONSTRUCT generates wp string...
   ...
   LET wp = "c_name LIKE 'J%'"
   DECLARE clist SCROLL CURSOR FROM "SELECT c_id FROM customer WHERE " || wp
   PREPARE crec FROM "SELECT * FROM customer WHERE c_id = ?"
   OPEN clist
   MENU "Test"
        COMMAND "First"    CALL disp_cust("F")
        COMMAND "Next"     CALL disp_cust("N")
        COMMAND "Previous" CALL disp_cust("P")
        COMMAND "Last"     CALL disp_cust("L")
        COMMAND "Refresh"  CLOSE clist OPEN clist
        COMMAND "Quit" EXIT MENU
   END MENU
   FREE crec
   FREE clist

END MAIN

FUNCTION disp_cust(m)
   DEFINE m CHAR(1)
   DEFINE rec RECORD
          c_id INTEGER,
          c_name VARCHAR(50)
      END RECORD
   CASE m
       WHEN "F" FETCH FIRST clist INTO rec.c_id
       WHEN "N" FETCH NEXT clist INTO rec.c_id
       WHEN "P" FETCH PREVIOUS clist INTO rec.c_id
       WHEN "L" FETCH LAST clist INTO rec.c_id
   END CASE
   INITIALIZE rec.* TO NULL
   IF SQLCA.SQLCODE == NOTFOUND THEN
      ERROR "You reached to top or bottom of the result set."
   ELSE
      EXECUTE crec USING rec.c_id INTO rec.*
      IF SQLCA.SQLCODE == NOTFOUND THEN
         ERROR "Row was not found in the database, refresh the result set."
      END IF
   END IF
   DISPLAY BY NAME rec.*
END FUNCTION