SQL Programming Techniques › Pseudoconversational Programming › Scrolling Through a List of Rows

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:

• The CA IDMS scratch area and scratch management statements to temporarily store and re-access retrieved data
• CA ADS pageable mapping in a CA ADS application

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