Positioned Update

ODBC supports positioned update, which updates the row most recently fetched by using a cursor. However, not all drivers provide support for positioned update.

With some ODBC drivers, the select statement used by the cursor must contain a FOR UPDATE clause to enable positioned update. Most data sources require specific combinations of SCROLLOPTION and CONCURRENCY to be specified either by SET statements or in the DECLARE CURSOR statement. If this fails to work, the ODBC Cursor Library provides a restricted implementation of positioned update which can be enabled by compiling with the directive SQL(USECURLIB=YES) and using SCROLLOPTION STATIC and CONCURRENCY OPTCCVAL (or OPTIMISTIC). To avoid multiple rows being updated when using the ODBC Cursor Library, the cursor query should include the primary key column(s) for the table to be updated.

Example

$SET SQL(usecurlib=yes)
 WORKING-STORAGE SECTION.
 
 EXEC SQL INCLUDE SQLCA  END-EXEC
 
*> after an sql error this has the full message text
 01 MFSQLMESSAGETEXT  PIC X(250).
 01 IDX               PIC X(04)  COMP-5.

 EXEC SQL BEGIN DECLARE SECTION  END-EXEC
*> Put your host variables here if you need to port
*> to other COBOL compilers
 EXEC SQL INCLUDE Products END-EXEC
 
 EXEC SQL END DECLARE SECTION END-EXEC
 
 PROCEDURE DIVISION.
 
     EXEC SQL
         WHENEVER SQLERROR perform OpenESQL-Error
     END-EXEC
          
*> Demo for positioned updates using ACCESS datasource  
     EXEC SQL
       CONNECT TO 'Inventory' USER 'admin'
     END-EXEC
 
*> Put your program logic/SQL statements here
 
     EXEC SQL
       DECLARE CSR679 CURSOR
         FOR SELECT
              A.ProductID
             ,A.ProductName
             ,A.UnitPrice
         FROM Products A
        WHERE ( A.ProductID <  3 )
     END-EXEC
 
     EXEC SQL SET SCROLLOPTION static  END-EXEC
     EXEC SQL SET CONCURRENCY optccval END-EXEC
 
     EXEC SQL OPEN CSR679 END-EXEC
     PERFORM UNTIL SQLSTATE >= "02000"
        EXEC SQL
        FETCH CSR679 INTO
            :ProductID
           ,:ProductName:ProductName-NULL
           ,:UnitPrice:UnitPrice-NULL
        END-EXEC
        *> Process data from FETCH
        IF SQLSTATE = "00000"
           *> increase price by 10%
            compute unitprice = unitprice * 1.10
            EXEC SQL
               UPDATE Products
                 SET UnitPrice = :UnitPrice:UnitPrice-NULL
               WHERE CURRENT OF CSR679
           END-EXEC
        END-IF
     END-PERFORM
     EXEC SQL CLOSE CSR679 END-EXEC
 
     EXEC SQL COMMIT END-EXEC
 
     EXEC SQL DISCONNECT CURRENT END-EXEC
     EXIT PROGRAM.
     STOP RUN.

*> Default sql error routine - modify to stop program if 
*> needed
  OpenESQL-Error Section.
 
      display "SQL Error = " sqlstate " " sqlcode
      display MFSQLMESSAGETEXT
*> stop run
      exit.