Previous Topic Next topic Print topic


UPDATE (POSITIONED)

Updates the DataRow most recently fetched by using a DataRows cursor.

Syntax:

>>-EXEC ADO-.--------------------.--UPDATE-datatable_name---->
            +-USING dataset_name-+

         +-----------  ,  -----------+
         V                           |
>--SET---column_name = -+- :hvar-----+----------------------->
                       -+- col_value-+


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

Parameters:

dataset_name The DataSet reference to be used. If you do not specify dataset_name, the current DataSet is used.
datatable_name The name of the DataTable to be updated.
column_name The column to be updated.
hvar Host variable that contains the column value.
col_value The column value.
datarows_name A previously declared, opened, and fetched DataRows cursor.

Comments:

EXEC ADO supports positioned update; however, not all ADO.NET data providers support positioned update.

With some ADO.NET data providers, the SELECT statement used by the cursor must contain a FOR UPDATE clause to enable positioned update.

The other form of UPDATE used in EXEC ADO statements is known as a searched update.

You cannot use host arrays with 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.

Example:

* Declare the cursor
     EXEC ADO
        DECLARE c1 DATAROWS FROM oline
     END-EXEC

* Open the cursor
     EXEC ADO
        OPEN c1 
     END-EXEC

* Modify the table using positioned updates and deletes
     PERFORM UNTIL EXIT
        * Delete row 1, 4, 7, ...
        EXEC ADO FETCH c1 INTO :oline END-EXEC
        IF SQLCODE NOT = 0
           EXIT PERFORM
        END-IF 
        EXEC ADO DELETE FROM oline WHERE CURRENT OF c1 END-EXEC
				
        * Update row 2, 5, 8, ...
        EXEC ADO FETCH c1 INTO :oline END-EXEC
        IF SQLCODE NOT = 0
           EXIT PERFORM
        END-IF
        MOVE 99 TO qty
        EXEC ADO UPDATE oline SET qty = :qty WHERE CURRENT OF c1 END-EXEC
				
        * Leave alone row 3, 6, 9, ...
        EXEC ADO FETCH c1 INTO :oline END-EXEC
        IF SQLCODE NOT = 0
           EXIT PERFORM
        END-IF
     END-PERFORM
Previous Topic Next topic Print topic