isCOBOL Evolve : Language Reference : Embedded SQL Statements : FETCH
FETCH
The FETCH statement retrieves a row from a cursor.
Format 1
EXEC SQL [ AT Database ]
 
  [ FOR Iterations ]
 
  FETCH { NEXT     } FROM { Cursor-Name } [ INTO Host-Variable, ... ]
                          { Host-Var    }
        { PREVIOUS }
        { PRIOR    }]
        { FIRST    }
        { LAST     }
 
  END-EXEC
Format 2
EXEC SQL [ AT Database ]
 
  FETCH  FROM { Cursor-Name } [ USING DESCRIPTOR Sql-Descriptor ]
              { Host-Var    }
  END-EXEC
Syntax rules
1. Iterations can be either a host variable or a numeric literal. It specifies the number of rows to be processed.
2. Cursor-Name is a Nonnumeric Literal, as defined in the Definitions section of the Preface of this document.
3. Host-Var must be USAGE HANDLE
4. Host-Variable is a host variable.
5. PREVIOUS and PRIOR are synonymous.
6. Sql-Descriptor is a SQLDA structure. This syntax is compiled only if iscobol.compiler.esql.db2 (boolean) is set to ‘1’, ‘on’, ‘true’ or ‘yes’ in the Compiler configuration.
General rules
1. Cursor-Name must be previously defined by a DECLARE statement.
2. When Host-Variable is a host variable declared as a group-item, the runtime uses all subordinate items as separate values instead of using the group-item as a single value.
3. If Host-Variable is array, it fetches enough rows to fill the array.
Array host variables can have different sizes. In this case, the number of rows it fetches is determined by the smaller of the following values:
o The size of the smallest array
o The value of the host_integer in the optional FOR clause
 
If the array is not completely filled then the warning is issued and you should check SQLERRD(3) to see how many rows were actually fetched.
 
If one of the host variables in the INTO clause is an array, they must all be arrays.
4. When the runtime framework property iscobol.jdbc.cursor.type * is set to its default value 1 (FORWARD_ONLY), moving backward is not allowed.
5. Database identifies the active connection that will execute the query and must be previously defined using a Format 4 DECLARE statement.
6. Host-Variables are not initialized and they’re updated only if the statement is successful. If an error occurs or there are no more records, then the Host-Variables are left unchanged.
Examples
Format 1 - Declare, open and fetch a cursor
exec sql 
   declare cust_cur  cursor for select * from customers
end-exec               
 
exec sql 
   open cust_cur
end-exec     
 
perform until 1 = 2
   exec 
      sql fetch next cust_cur into :ws-cust-code, :ws-cust-name
   end-exec
 
   display "code: " ws-cust-code " name: " ws-cust-name
   if sqlcode = 100
      exit perform
   end-if
 
end-perform
 
exec sql 
   close cust_cur
end-exec