Deletes the row most recently fetched by using a cursor.
Syntax:
>>---EXEC SQL---DELETE---FROM---table_name--------------->
>----WHERE CURRENT OF---cursor_name---END-EXEC---><
Parameters:
table_name
|
The same table used in the SELECT FROM option (see DECLARE CURSOR).
|
cursor_name
|
A previously declared, opened, and fetched cursor.
|
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
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).