Summary -
In this topic, we described about the below sections -
FETCH statement retrieves the current positioned row data and assigns it to the corresponding variable(s)/host variable(s).
FETCH statement is also responsible for repositioning the cursor to the next sequential row in the result table.
FETCH statement can retrieve one row at a time.
FETCH statement should have an INTO clause with host variable(s) or variable(s) to place the data retrieved.
Updatable (WITH HOLD) cursors use the current row's concept and update or delete it using WHERE CURRENT OF phase.
The order of host variables in the FETCH statement should be the same as the order of the columns specified in DECLARE CURSOR.
Syntax -
EXEC SQL FETCH FROM cursor-name/cursor-variable-name INTO :variable-for-col1, :variable-for-col2, . . :variable-for-coln END-EXEC.
cursor-name -
- Cursor-name is the name of the cursor used in an application program to refer to the cursor.
- The cursor-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
cursor-variable-name -
- Cursor-variable-name is a working-storage variable that contains the cursor name used in an application program to refer to the cursor.
- The cursor-variable-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
INTO clause -
- INTO clause identifies one or more target variables for assigning output values.
- The value assignments perform in the same order how the columns are specified in DECLARE CURSOR. i.e., The first column in the DECLARE CURSOR value is assigned to the first target variable in the FETCH, the second column value to the second target variable, and so on.
- Suppose any error occurs at the specific variable in the sequence. In that case, the variables before the error variable have the assigned values, and those after the error variable have no values.
- If the target variables in FETCH are less in number than the table columns in the DECLARE, then the value "W" is assigned to the SQLWARN3 field of the SQLCA.
variable-for-col1, variable-for-col2, .... -
- These variables are used to receive the data from the database table.
- Every variable should have an equivalent COBOL declaration for the corresponding DB2 table column type.
- Variable-for-col* can be a global-variable, host-variable, SQL-parameter, SQL-variable, transition-variable, array-variable, or field-reference.
- Host-variables from table DCLGEN are generally used to receive the table's data and avoid compatibility issues.
NULL Indicator -
While fetching the data, there might be a chance that the data might not be available in the source table. The column contains the value which is other than actual data called "NULL".
Suppose any NULL valued column exists in the retrieved columns list. In that case, the NULL indicator variable should code along with the host variable.
Suppose the NULL indicator variable is not coded with FETCH and the column returns a NULL value. In that case, the FETCH will fail with a -305 error.
The NULL indicator variables can always declare with –NI at the end of the field name.
The declaration of the NULL indicator is S9(04) COMP.
The NULL indicator variable handling in FETCH statement syntax as shown below -
Syntax -
EXEC SQL FETCH FROM cursor-name/cursor-variable-name INTO :variable-for-col1 :variable-for-col1-NI, :variable-for-col2 :variable-for-col2-NI, . . :variable-for-coln END-EXEC.
The NULL indicator validation for the value is valid or NULL is shown below -
IF host-variable-col-ni = -1 (NULL value received from table column) = 0 (Received valid value) = 1 (Received truckated value)
Cursor positioning -
An open cursor has the below three possible positions -
- Before a row
- On a row
- After the last row.
If a cursor is on a row of the result table, that row is called the cursor's current row. The current row is referenced in an UPDATE or DELETE statement.
If the cursor is positioned or any row except the last row -
- The SQLCODE is set to 0.
- It repositions on the next row, and the values of that row are assigned to the target variables.
If the cursor is currently positioned on or after the last row of the result table -
- The SQLCODE is set to +100, and SQLSTATE is set to '02000'.
- The cursor positions after the last row.
- Values are not assigned to the target variables.
If the cursor state is unpredictable, an error occurs.
Examples -
Scenario1 - Fetch cursor for retrieving two columns.
Code- EXEC SQL FETCH F1 INTO :HV1, :HV2 END-EXEC.
Scenario2 - Fetch cursor for retrieving two columns with NULL indicator check.
Code- EXEC SQL FETCH F2 INTO :HV1 :HV1-NI, :HV2 :HV2-NI END-EXEC.
Scenario3 - FETCH statement uses an SQLDA.
Code- EXEC SQL FETCH F3 USING DESCRIPTOR :sqlda3 END-EXEC.
Scenario4 - Fetch cursor using a variable that contains cursor name for retrieving two columns.
Declaration- 05 WS-CS-VAR PIC X(10) VALUE "F4". Code- EXEC SQL FETCH WS-CS-VAR INTO :HV1, :HV2 END-EXEC.
Practical Example -
Scenario - The below example describes how the cursor is declared and used in the COBOL + DB2 program.
Requirement - Display employees id, name, designation from department 1000.
Input-

Code -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- ***************************** Top of Data ****************************** IDENTIFICATION DIVISION. PROGRAM-ID. CREADONL. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL INCLUDE EMPLDET END-EXEC. * DECLARING CURSOR EXEC SQL DECLARE CSR1 CURSOR FOR SELECT EMP_ID, EMP_NAME, DESIGNATION FROM EMPLOYEE_DETAILS WHERE DEPT_ID = 1000 FOR FETCH ONLY END-EXEC. PROCEDURE DIVISION. * OPENING CURSOR EXEC SQL OPEN CSR1 END-EXEC. * FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE IF SQLCODE EQUAL ZERO PERFORM FETCH-EMP-DETAILS THRU FETCH-EMP-DETAILS-EXIT UNTIL SQLCODE NOT EQUAL 0 END-IF. * CLOSING CURSOR EXEC SQL CLOSE CSR1 END-EXEC. STOP RUN. FETCH-EMP-DETAILS. * FETCH CURSOR EXEC SQL FETCH CSR1 INTO :EMP-ID, :EMP-NAME, :DESIGNATION END-EXEC. EVALUATE SQLCODE WHEN ZERO DISPLAY EMP-ID, "|", EMP-NAME, "|", DESIGNATION WHEN 100 CONTINUE WHEN OTHER DISPLAY "DB2 ERROR: ", SQLCODE END-EVALUATE. FETCH-EMP-DETAILS-EXIT. EXIT. **************************** Bottom of Data ****************************
Output -
