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-

employee_details table

Code -

CURSOR Program 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 -

CURSOR Program Output