Equivalent of RELEASE for DELETE...

kolonuk

Member
Hi All,

I have a requirement where I need to be able to add/update and delete records inside a transaction, regardless of the outcome of the transaction - the record must be changed no matter what.

Now, I can use RELEASE for adding/updating, but what do I use when I want to force the delete?

Code:
DO TRANSACTION
:[INDENT]CREATE rec1.
UPDATE rec1.field1.

FIND rec2.
DELETE rec2.

IF rec1.field1 = 1 THEN UNDO, RETURN.[/INDENT]END.
In this example, I want the delete of rec2 to happen regardless, while rec1 should do normal transaction things.

According to what I've read here, RELEASE after DELETE keeps the record lying around, when I want to force the delete to happen.

I can go into more detail if required, but any ideas would be welcome!
 

GregTomkins

Active Member
If you think this is what RELEASE does for adding / updating, you are mistaken.
 
Simply inside a block:
DO TRANSACTION:
DELETE record
END. /* transaction */
FIND another record NO-LOCK NO-ERROR

......
......
......

UR normal transaction block to delete the record.
Then immediately after the delete, find another record.
This will ensure the deleted record is no longer in the buffer.
Good luck.
BFN
 

tamhas

ProgressTalk.com Sponsor
What you are asking for is subtransactions in which the subtransaction can commit even if the transaction as a whole doesn't commit. ABL doesn't do that.

But, think about it a bit ... if the delete is unconditional, why does it need to be a part of the update transaction? If it is unconditional, just do it in its own transaction before or after the update as business logic dictates.
 

kolonuk

Member
Ok, I admit, I jumped the gun on the RELEASE... I just quickly read the "progress pocket reference v9" without doing research... research done, my bad!

Yes, in effect I am after transactions within transactions and there is a good reason!

We are trying to implement a help system, and for example there needs to be help for adding an order, which is in a transaction (I know, we shouldn't create or lock out the record until we need it, but this is an example!) To put help into the system for various objects and fields, you right click and, if allowed, get to edit the help. If, after adding the help, you click "cancel" to cancel adding an order, it undoes the transaction for adding an order, which at the moment includes the editing of help - both creates and deletes.

If we can't do independent transactions in transactions, can we run progress "without" transactions while we put help in? - I know this would create a mess in some places, but this would just be on our test DB!
 

RealHeavyDude

Well-Known Member
There is no way to update a Progress/OpenEdge database without having a transaction. The Progress 4GL compiler will automatically create a transaction when it encounters a statement that directly changes the database in the code. Furthermore it will extend the scope of that transaction to the next outer block with scoping capabilities.

Your problem is not the Progress technology, instead it is your design. Either you change your design to implement an optimistic locking strategy, which is what I would recommend you, or you should rethink how you nest the transactions. Either way you should make yourself familiar with the concept of transactions and their scope.

Although it is not best practice, but here goes:

There is the possibility to create a sub transaction which can be undone although the transaction is committed. The database is only aware about the outer most transaction. Everything nested in between is a sub transaction that can be undone - not by the database, it can be undone by the client (the information for that, amongst others, is in the lbi* temporary files created by the client). Therefore if the transaction on your help data is the outermost and the transaction on the order data is in the sub transaction the client can undo the contents of the sub transaction on the order data whereas the transaction on the database, which in that case only contains changes on the help data, can be committed.

Heavy Regards, RealHeavyDude.
 

LarryD

Active Member
All that has been said regarding transactions is true and you really should to re-think your design with an eye for minimizing transaction scope using temp-tables, defining buffers, and strong scoped small transactions that commit only after acceptance.

However that being said to accomplish what you are trying to do with the help system, you can utilize a no-undo temp-table if you wish.

A very very simple sample (leaving the necessary coding and endkey enderror stop handling up to you):

Code:
def temp-table a no-undo like mydbhelptable.
run the_update.
if can-find(first a) 
then run update_help.

procedure the_update.
def buffer mydbtable for mydbtable.
def buffer mydbhelptable for mydbhelptable.
empty temp-table a.
do transaction on endkey undo, leave:
     on right-click of mydbtable.field in frame myframe do:
            find mydbhelptable no-lock where <conditions> no-error.
            create a.
            if available mydbhelptable 
            then 
                 buffer-copy mydbhelptable to a.
            else
                 assign <mydbhelptable fields>.
            run update_a ( recid(a) ).  /*IP that updates the values in table a */
      end.
     <updates and wait-for>
end.
end procedure.

procedure update_a.
def input parameter a-recid as recid no-undo.
find a where recid(a) = a-recid.
<update a fields>
end.

procedure update_help.
def buffer mydbhelptable for mydbhelptable.
do for mydbhelptable transaction:
    <find, create and/or update help table>
end.
end procedure.
 

tamhas

ProgressTalk.com Sponsor
The way to run without transactions is not to start any. If you do all your work against local no-undo variables you can do anything you want and then commit to the database in a small transaction at the end which won't need this nesting. In some cases, it is also appropriate to use a no-undo variable within a transaction to record something and then when the transaction block is complete, whether or not the transaction completed, you can use that information to commit some other action which you want to do unconditionally.

RHD is right that what is really needed here is a clear architectural vision.
 
Top