Previous Topic Next topic Print topic


CLOSE

Discards unprocessed rows and frees any locks held by the cursor.

Syntax:

>>---EXEC SQL---CLOSE---cursor_name---.------------.---END-EXEC---><
                                      +WITH RETURN-+

Parameters:

cursor_name A previously declared and opened cursor.

Comments:

The cursor must be declared and opened before it can be closed. All open cursors are closed automatically at the end of the program.

You can specify WITH RETURN in a SQL CLR stored procedure not compiled with DIALECT=MAINFRAME. If you do so, the client application must use the GET NEXT RESULT SET statement to retrieve the result set(s).

Example:

*Declare the cursor...
     EXEC SQL
         DECLARE C1 CURSOR FOR
            SELECT staff_id, last_name
            FROM staff
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not declare cursor.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF
     
     EXEC SQL
        OPEN C1
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not open cursor.'
        DISPLAY SQLERRMC    
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT CURRENT END-EXEC
        STOP RUN
     END-IF

     PERFORM UNTIL sqlcode NOT = ZERO
*SQLCODE will be zero as long as it has successfully fetched data
        EXEC SQL
           FETCH C1 INTO :staff-staff-id, :staff-last-name
        END-EXEC
        IF SQLCODE = ZERO
           DISPLAY "Staff ID: " staff-staff-id
           DISPLAY "Staff member's last name: " staff-last-name
        END-IF
     END-PERFORM

     EXEC SQL
        CLOSE C1
     END-EXEC

     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not close cursor.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
     END-IF
Previous Topic Next topic Print topic