DB-FETCH

DB-FETCH sequentially retrieves rows from the cursor set defined by DB-DECLARE. When you define a cursor set with DB-DECLARE, SQL places the selected rows in a results table, or cursor set. DB-FETCH retrieves these rows. Each DB-FETCH returns the next row of the cursor set until the end of the results table is reached.
Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

Target:

SQL

Syntax:

DB-FETCH CUR[SOR] cursorname
... [SENSITIVE|INSENSITIVE]
... [ABSOLUTE number|RELATIVE number|CURRENT|FIRST|LAST|NEXT|PREVIOUS|PRIOR]
... [NEXT ROWSET | FIRST ROWSET  | PRIOR ROWSET |
... CURRENT ROWSET | LAST ROWSET |
... ROWSET STARTING AT {ABSOLUTE number | RELATIVE number}] 
... [FOR number ROWS]
... [INTO dataname]

General Rules:

  1. The row returned by the current iteration of DB-FETCH is the current row.
  2. You can code DB-FETCH within an S-COBOL loop to retrieve multiple rows from a cursor set.
  3. To FETCH into individual columns, specify those alternate host variables in DB-DECLARE.
  4. When using ROWSET operations, set up host-variable array structures to receive multiple values for each column.
  5. AMB creates default array structures for records that specify multiple-row fetches or entire-row, multiple-row inserts. The array size is the maximum row number value found specified in the FOR number ROWS clause for that table. For example:
    01   recordname-ARR.
         05  CobolHostVar1-ARR     picture occurs row-number times.
         05  IND-CobolHostVar1-ARR picture occurs row-number times.
         05  CobolHostVar2-ARR     picture occurs row-number times.
         05  IND-CobolHostVar2-ARR picture occurs row-number times.
         …..
         05  CobolHostVarn-ARR     picture occurs row-number times.
         05  IND-CobolHostVarn-ARR picture occurs row-number times.
    

Parameters:

CUR[SOR] cursorname

Specify cursor. Cursorname must be previously named by DB-DECLARE or DB-PROCESS-ID.

  • ABSOLUTE number
  • RELATIVE number
  • CURRENT
  • FIRST
  • LAST
  • NEXT
  • PREVIOUS
  • PRIOR

Cursor positioning keywords. NEXT is the default.

INTO dataname

Move host variable structure into the alternate data structure data name. Data moves after the actual SQL call via a MOVE statement. Generated code isIF OK-ON-RECMOVE hostname TO dataname

SENSITIVE

Reflects changes made outside of this cursor.

INSENSITIVE

Returns rows from the result table as is.

  • NEXT ROWSET
  • FIRST ROWSET
  • PRIOR ROWSET
  • CURRENT ROWSET
  • LAST ROWSET
  • ROWSET STARTING AT
  • ABSOLUTE number
  • RELATIVE number

Rowset positioning keywords. NEXT ROWSET is the default.

FOR number ROWS

Controls how many rows are returned on each FETCH.

Example:

Retrieve columns and rows in cursor set D2MAST-CURSOR; place information into WS-D2MAST-RECORD in Working-Storage.
DB-FETCH CURSOR D2MAST-CURSOR
... INTO WS-D2MAST-RECORD