Previous Topic: Using SUSPEND SESSION and RESUME SESSIONNext Topic: Updating a Row After a Pseudoconverse


Scrolling Through a List of Rows

Retrieval List Using Bulk Fetch

You can use a bulk fetch and a suspended session to develop an online application for scrolling through a list of rows. Each fetch statement retrieves a screen display of rows. The session is suspended before the pseudoconverse and resumed when the user requests the next set of rows to display. Since the DBMS has maintained cursor position during the suspended session, the next execution of the fetch statement automatically retrieves the next set of rows in the cursor result table.

Retrieval List Example

In this example, having already declared a host variable array with as many occurrences as there are rows in a screen display, the program declares and opens the POSITION_CRSR cursor to retrieve data about employees by department:

EXEC SQL
  DECLARE POSITION_CRSR CURSOR FOR
    SELECT P.EMP_ID,
           E.DEPT_ID,
           P.JOB_ID,
           P.SALARY_AMOUNT,
      FROM POSITION P, EMPLOYEE E
      WHERE P.EMP_ID = E.EMP_ID
        AND E.DEPT_ID = :DEPT-ID
END-EXEC.

EXEC SQL
  OPEN POSITION_CRSR
END-EXEC.

The program then iterates the following logic until the online user exits this thread of the application. The first fetch uses the value of INPUT-DEPT-ID. The second fetch retrieves the next set of employees for the department because the DBMS has maintained the cursor position during the suspended session:

 EXEC SQL    FETCH POSITION_CRSR      BULK :BULK-POSITION  END-EXEC.  IF SQLCODE = 100 MOVE 'Y' TO END-FETCH.  EXEC SQL    SUSPEND SESSION  END-EXEC.  (Move retrieved values to display fields)  MAP OUT ...  (Pseudoconverse)  MAP IN ...  EXEC SQL    RESUME SESSION  END-EXEC.  IF END-FETCH = 'Y' ...  ◄─── Close cursor and either                               select a new department or exit

Scrolling Backwards

Scrolling backwards through an online retrieval list requires pageable map processing. If necessary, you can manage pageable map processing by using:

Note: For more information about scratch area management, see the applicable CA IDMS program language reference manual.