Hello,
Can someone check my code below. I'd like to update all of the records in a table with one transaction, but record the records that can't be locked into a separate file. Is this the best way? Thanks.
blkCl3gComm: FOR
EACH cl3gComm NO-LOCK WHERE cl3gComm.cd-device-communication = vChangeKeyFrom:
FIND CURRENT cl3gComm EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
IF ERROR-STATUS:ERROR THEN DO:
PUT STREAM sLog UNFORMATTED
'cl3gComm' + vDelim + STRING(cl3gComm.num3g-comm-id) SKIP.
END.
ELSE DO:
ASSIGN cl3gComm.cd-device-communication = vChangeKeyTo.
END.
END
.
If you have started a transaction outside of this loop then it may be ok, but this your code does not ensure a single transaction. You need to add an external transaction block as below.
DO TRANSACTION:
FOR
EACH cl3gComm NO-LOCK WHERE cl3gComm.cd-device-communication = vChangeKeyFrom:
FIND CURRENT cl3gComm EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
IF ERROR-STATUS:ERROR THEN DO:
PUT STREAM sLog UNFORMATTED
'cl3gComm' + vDelim + STRING(cl3gComm.num3g-comm-id) SKIP.
END.
ELSE DO:
ASSIGN cl3gComm.cd-device-communication = vChangeKeyTo.
END.
END.
END.
However, as you want this as one transaction it will lock all records until all the records are updated. One transaction means that the change to the first record must be held in case it fails before the end and need to undo the lot. Even if you recorded the value before the change and if any failed you then set all the records back to the old value, what if the reversal failed or somebody made a change after you and your reverse that.
So if you one the one transaction you will have locked records.
You cannot have a FIND of the same table that is in the FOR EACH, so you will need something like this:
DEFINE BUFFER bufCl3gComm for Cl3gComm.
DO TRANSACTION:
FOR EACH Cl3gComm NO-LOCK:
FIND bufCl3gComm EXCLUSIVE-LOCK
WHERE ROWID(bufCl3gComm) = ROWID(Cl3gComm) NO-WAIT NO-ERROR.
IF AVAILABLE bufCl3gComm...... etc.
END.
END.
Good points, thanks.
This is a oneshot table update. Not sure if I need to worry about it being all one transaction though.
Thanks for the help. I still feel like a newbie when it comes to coding in Progress. I haven't found the best place yet to find samples of code. The Help screens have some good examples, and then there are the co-workers. But I hate to bother them.