SQL Programming Techniques › Pseudoconversational Programming › Updating a Row After a Pseudoconverse

Updating a Row After a Pseudoconverse

Using an Updateable Cursor

During a suspended session, the DBMS maintains the cursor position of an open cursor and also the lock on the current cursor row. Therefore, a program running under the cursor stability isolation level can resume the suspended session and perform a positioned update without checking whether the row has been updated by a concurrent database transaction.

Updateable Cursor Example

In this example, the program fetches a row from the BENEFITS_CRSR cursor, suspends the session, and displays the row to the online user. Following user input, the program resumes the session and performs a positioned update with user input:

 EXEC SQL
   DECLARE BENEFITS_CRSR FOR
     SELECT JOB_ID,
            SALARY_AMOUNT,
            BONUS_PERCENT
       FROM BENEFITS
       WHERE EMP_ID = :EMP-ID
 END-EXEC.

 EXEC SQL
   OPEN BENEFITS_CRSR
 END-EXEC.

 EXEC SQL
   FETCH BENEFITS_CRSR
     INTO :JOB_ID,
          :SALARY_AMOUNT,
          :BONUS_PERCENT
 END-EXEC.

 EXEC SQL
   SUSPEND SESSION
 END-EXEC.

 (Move retrieved values to display fields)

 MAP OUT ...

 (Pseudoconverse)

 MAP IN...

 (Program moves input data to host variables)

 EXEC SQL
   RESUME SESSION
 END-EXEC.

 EXEC SQL
   UPDATE BENEFITS
     SET SALARY_AMOUNT = :SALARY-AMOUNT,
         BONUS_PERCENT = :BONUS-PERCENT
     WHERE CURRENT OF BENEFITS_CRSR
 END-EXEC.

 EXEC SQL
   COMMIT
 END-EXEC.

Searched Update After a Pseudoconverse

When a database transaction running under the default isolation mode of cursor stability suspends the session, the DBMS releases any lock it set on the base row(s) of a single-row SELECT result. No locks are maintained on rows resulting from bulk selects in this situation, and only the lock on the last row fetched in a bulk fetch is maintained under cursor stability during a suspended session.

A concurrent database transaction can update the data retrieved by a single-row SELECT statement or FETCH BULK statement while the session of the original transaction is suspended. In these situations, the program should check whether the data has been modified since it was retrieved before applying an update after the pseudoconverse.

Checking Whether the Row Was Modified

To be able to check whether a row has been modified, your processing environment can create and maintain a column for a last-update timestamp value. An alternative is to compare the values of all fields to be updated with the values that were retrieved.

Maintaining a Last-Update Timestamp

To maintain a last-update timestamp for a table row, use these procedures:

1. Define a last-update column for each table with data type TIMESTAMP and NOT NULL WITH DEFAULT
2. In the program, define the host variable for the last-update timestamp column as a character field with length 26
3. Set the last-update timestamp column to the value of the special register CURRENT TIMESTAMP when modifying the row

You can add a last-update column to an existing table using the ALTER TABLE statement.

Note: For more information about the ALTER TABLE, see the CA IDMS SQL Reference Guide.

How You Check the Row Before Updating

To determine whether a row has been modified since the program retrieved it, you attempt a searched update with a search condition that includes a comparison to verify that the last-update timestamp value has not changed.

Searched Update Example

In this example, the program issues a single-row SELECT statement from the POSITION table using the primary key of the table. The program suspends the SQL session and displays the retrieved row to the online user:

 MOVE MAP-EMP-ID TO EMP-ID.
 MOVE MAP-JOB-ID TO JOB-ID.

 EXEC SQL
   SELECT EMP_ID,
          JOB_ID,
          SALARY_AMOUNT,
          LAST_UPDATED
     INTO :EMP-ID,
          :JOB-ID,
          :SALARY-AMOUNT,
          :LAST-UPDATED
     FROM POSITION
     WHERE EMP_ID = :EMP-ID
 END-EXEC.

 EXEC SQL
   SUSPEND SESSION
 END-EXEC.
  .
  .
  .
 MAP OUT ...

 (Pseudoconverse)

Following the pseudoconverse, the program issues an update to the single row using input from the online user. The update executes only if the row has not been modified since it was retrieved:

 MAP IN ...

 MOVE MAP-SALARY-AMOUNT TO SALARY-AMOUNT.

 EXEC SQL
   RESUME SESSION
 END-EXEC.

 EXEC SQL
   UPDATE POSITION
     SET SALARY_AMOUNT = :SALARY-AMOUNT,
         LAST_UPDATED = CURRENT TIMESTAMP
     WHERE EMP_ID = :EMP-ID
       AND JOB_ID = :JOB-ID
       AND LAST_UPDATED = :LAST-UPDATED
 END-EXEC.

 IF SQLCODE = 100 PERFORM ROW-CHANGED.