DELETE (Positioned)

Syntax:

>>---EXEC SQL---.------------.--->
                +-AT db_name-+

 >--DELETE---FROM---table_name--->

 >--WHERE CURRENT OF--cursor_name---END-EXEC---><

Parameters:

AT `db_name`	The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement.
`table_name`	The same table used in the SELECT FROM option (see DECLARE CURSOR).
`cursor_name`	A previously declared, opened, and fetched cursor.

Comments:

ODBC supports positioned delete, which deletes the row most recently fetched by using a cursor in the Extended Syntax (it was in the Core Syntax for ODBC 1.0 but was moved to the Extended Syntax for ODBC 2.0). Not all drivers provide support for positioned delete, although OpenESQL sets ODBC cursor names to be the same as COBOL cursor names to facilitate positioned update and delete.

With some ODBC drivers, the select statement used by the cursor must contain a 'FOR UPDATE' clause to enable positioned delete.

You cannot use host arrays with positioned delete.

The other form of DELETE used in standard SQL statements is known as a searched delete.

Most data sources require specific combinations of SCROLLOPTION and CONCURRENCY to be specified either by SET statements or in the DECLARE CURSOR statement.

The ODBC cursor libray provides a restricted implementation of positioned delete which can be enabled by compiling with SQL(USECURLIB=YES) and using SCROLLOPTION STATIC and CONCURRENCY OPTCCVAL (or OPTIMISTIC).

Example:

* Declare a cursor for update
     EXEC SQL DECLARE C1 CURSOR FOR
        SELECT staff_id, last_name FROM staff FOR UPDATE
     END-EXEC

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

* Open the cursor
     EXEC SQL
        OPEN C1 
     END-EXEC
     IF SQLCODE NOT = ZERO
        DISPLAY 'Error: Could not open cursor for update.'
        DISPLAY SQLERRMC
        DISPLAY SQLERRML
        EXEC SQL DISCONNECT ALL END-EXEC
        STOP RUN
     END-IF

* Display staff member's details and give user the opportunity
* to delete particular members. 
     PERFORM UNTIL SQLCODE NOT = ZERO
        EXEC SQL FETCH C1 INTO :staff-id,:last-name END-EXEC
        IF SQLCODE = ZERO
           DISPLAY 'Staff ID:     ' staff-id
           DISPLAY 'Last name:    ' last-name
           DISPLAY 'Delete <y/n>? ' WITH NO ADVANCING
           ACCEPT usr-input
           IF usr-input = 'y'
              EXEC SQL
                 DELETE FROM staff WHERE CURRENT OF C1
              END-EXEC
              IF SQLCODE NOT = ZERO
                 DISPLAY 'Error: Could not delete record.'
                 DISPLAY SQLERRMC
                 DISPLAY SQLERRML
              END-IF
           END-IF
        END-IF
     END-PERFORM