>>---EXEC SQL---.------------.---> +-AT db_name-+ >--DELETE---FROM---table_name---> >--WHERE CURRENT OF--cursor_name---END-EXEC---><
|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.|
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).
* 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