FETCH Cursor
Summary
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.
Code -
Declaration- 05 WS-CS-VAR PIC X(10) VALUE "F4".
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 -
CURSOR Example 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 -
