7.96.2 UPDATE Comments / Warnings
Understand UPDATE Command
The use of automatic "crossed update" checks by the UPDATE command should be clearly understood.
Consider the following flow of commands:
FETCH WITH_KEY( ) or WITH_RRN( )
DISPLAY
IF_MODE *CHANGE
UPDATE
ENDIF
Since the UPDATE command has no WITH_KEY or WITH_RRN parameter it is indicating that the last record read (by the FETCH command) should be updated.
In this situation, the "crossed update window" is in the interval between the time the record was FETCHed and the time that it is UPDATEd. This could be very long if the user went and had a cup of coffee when the DISPLAY command was on their workstation.
This is a correct and valid use of the automatic "crossed update" checking facility. If the record was changed by another job/user between the FETCH and the UPDATE, then the UPDATE will generate a "crossed update error" (which should be handled just like any other type of validation error).
Now consider the following flow of commands:
FETCH WITH_KEY( ) or WITH_RRN( )
DISPLAY
IF_MODE *CHANGE
UPDATE WITH_KEY( ) or WITH_RRN( )
ENDIF
Since the UPDATE command has a WITH_KEY or WITH_RRN parameter it is indicating that a specific record (or group of records) should be read and updated.
This is a common coding mistake. Everybody knows that the WITH_KEY or WITH_RRN values on the UPDATE command should/would be the same as those on the FETCH command. However, the RDML compiler cannot be sure that the values were not changed, so it is forced to (re)read the record before attempting the UPDATE.
In this situation, the "crossed update window" is in the interval between the time the record is (re)read by the UPDATE command and then updated by the UPDATE command. This interval is very short, and thus the "crossed update" check is effectively disabled.
This is not considered to be a valid and correct use of the UPDATE command in an interactive program like this because it effectively disables the automatic "crossed update" check.
No KEY
Where an UPDATE operation is issued with no WITH_KEY or WITH_RRN parameters specified the last record read from the file will be updated. Thus the following are equivalent operations:
CHANGE FIELD(#DATDUE) TO(*DATE)
UPDATE FIELDS(#DATDUE) IN_FILE(ORDHDR) WITH_KEY(#ORDNUM)
is functionally equivalent to:
FETCH FIELDS(#DATEDUE) FROM_FILE(ORDHDR) WITH_KEY(#ORDNUM)
CHANGE FIELD(#DATDUE) TO(*DATE)
UPDATE FIELDS(#DATDUE) IN_FILE(ORDHDR)
and:
CHANGE FIELD(#QUANTITY) TO(100)
UPDATE FIELDS(#QUANTITY) IN_FILE(ORDLIN) WITH_KEY(#ORDNUM)
is functionally equivalent to:
SELECT FIELDS(#QUANTITY) FROM_FILE(ORDLIN) WITH_KEY(#ORDNUM)
CHANGE FIELD(#QUANTITY) TO(100)
UPDATE FIELDS(#QUANTITY) IN_FILE(ORDLIN)
ENDSELECT
Note that the last 2 examples change the #QUANTITY field of all order lines to 100. This is an example of multiple record updating (or "set at a time" updating).
Note 'UPDATE WITH_KEY' should not be used within a select loop or in a subroutine called from within a select loop.
SQL NULL
When an SQL Null field is updated into a table's database column, one of the following will occur:
- If the column does not have the NOT NULL constraint, the column is set to SQL Null.
- If the column does have the NOT NULL constraint, the update will fail. (This can only occur if the database definition of the column does not match the LANSA definition of the field.)