FETCH

FETCH is the third step of cursor processing. It returns data from the result table one row at a time into host variables.

What FETCH does

• Returns one row from the result table into the host variables named in the INTO clause.
• Each FETCH moves the cursor forward by one row - there is no FETCH BACKWARD in a simple cursor.
• If the result table was not built during OPEN, it is built at the first FETCH.
• After a successful FETCH, SQLCODE = 0. When no more rows are left, SQLCODE = 100 (end of cursor).
• FETCH is coded in the Procedure Division, normally inside a loop (FETCH-PARA).

Syntax and examples

• Syntax: EXEC SQL FETCH <cursor-name> INTO :hv1, :hv2, ... END-EXEC.
• Example with separate host variables:
  
  EXEC SQL FETCH EMPCUR INTO :WS-EMPNO, :WS-EMPNAME, :WS-DEPTNO, :WS-SALARY END-EXEC.
• Example fetching into a group item (DCLGEN style):
  
  EXEC SQL FETCH C1 INTO :DCLDEPT END-EXEC.
• The INTO list must have one host variable (or group item) for every column in the SELECT list, in the same order.
• For nullable columns, add a null indicator: FETCH EMPCUR INTO :WS-SALARY :WS-SAL-IND. If the indicator is -1 after FETCH, the column was NULL.

The FETCH loop pattern

• OPEN the cursor once, then FETCH repeatedly until SQLCODE = 100.
• A common pattern: one FETCH before the loop, then PERFORM ... UNTIL SQLCODE NOT = 0 with a FETCH at the end of each iteration.
• Check for negative SQLCODEs inside the loop - 100 is normal end-of-data, anything negative is an error.
• When SQLCODE = 100, CLOSE the cursor. Do not process the host variables after a 100 - they still hold the previous row.
• If the SELECT returns zero rows, the very first FETCH returns 100 - the loop body never runs, which is correct.