Commit Transaction

[MPHANSON]

Hello,

I am having issues deleting a transaction on a screen when a user F4's out. This is basically a two step process, a user is asked to delete a specific record, then a new record is created. The problem is I need an undo statement in case the user does F4 when they only want to delete the old record and not create a new one. Doing this rolls back the delete transaction causing it to never happen. Is there a commit function that I can add to the delete statement so that happens immediately regardless of what the user does?

The code is like this:

repeat on endkey, undo leave:
if delete = yes
then do:
for each record exclusive-lock
where criteria:
delete record.
end.
end.

create new record.
update field. <--- this is where user F4's
end.

Thanks,

Matt

 

[DevTeam]

Hi Matt,

I would surround the "delete" part by a DO TRANSACTION ... END, so that if the delete completes successfully, it will be committed.

Then, if I got it right, there will only be 1 record found (thus deleted) by your "for each" block. So why don't you use a "FIND table WHERE criteria" ? It could be much faster !

You may try this code :

repeat :
  DO TRANSACTION :
    IF delete
    then do:
      FIND FIRST table WHERE criteria EXCLUSIVE-LOCK NO-ERROR.
      IF AVAILABLE table
      THEN DELETE table.
      RELEASE table.
    END.
  END TRANSACTION.

  CREATE table.
  UPDATE field.
end.

HTH !

Julien

 

[MPHANSON]

Hi,

Thanks for the quick response. I actually tried using the transaction keyword and get the following message "** WARNING--TRANSACTION keyword given within actual transaction level." This also didn't solve the problem as the whole transaction is rolled back because the scope is the entire repeat statement. Also to clarify, there could be multiple records to be deleted, hence the for each.

Matt

 

[DevTeam]

Okay, my bad... I should have tried before answering :lol:

Why do you use the "undo" on the "repeat" command ?

 

[MPHANSON]

I have this in case a user does F4 out, then the record they are in the middle of creating(after the delete) is backed out, just how it should be(to my understanding). I wouldn't want the record it just created to be left out there - so that's why I need the undo.

 

[DevTeam]

I have this in case a user does F4 out, then the record they are in the middle of creating(after the delete) is backed out, just how it should be(to my understanding). I wouldn't want the record it just created to be left out there - so that's why I need the undo.

Right !

What about surrounding only the "create / update" statement by the repeat block? So that the delete will remain even if F4 is pressed !

 

[MPHANSON]

The delete/create process is repeated over and over again. So I need both parts in the repeat loop. I've tried moving things around so that I can avoid this problem but I still get back to the same conclusion that I need to force the commit at a particular spot....

 

[DevTeam]

Here's another idea :

DEF VAR doQuit AS LOGICAL NO-UNDO.

REPEAT :
    FOR EACH maTable WHERE condition EXCLUSIVE-LOCK :
        DELETE maTable.
    END.

    DO ON ENDKEY UNDO, LEAVE :
        doQuit = yes.
        CREATE maTable.
        UPDATE maTable.
        doQuit = no.
    END.

    IF doQuit
    THEN LEAVE.
END.

I'm not sure it works in every case !

 

[MPHANSON]

After doing some more testing, I took out the whole undo statement on the repeat, and it's still rolling back the transaction after it's deleted and F4 is used. I did checks to see that record is being deleted prior to F4(it is), but as soon as F4 is used, it acts like the record was never deleted. So progress must roll back automatically if an iteration of the loop doesn't complete succesfully? These seems pretty odd to me.....am I missing something?

 

[tamhas]

Sounds like normal transaction scoping to me.

 

[TomBascom]

If I understand you correctly you have a two step process which deletes some records and then optionally creates a new empty record that the user can update or choose to abandon. If the update is abandoned the record is expected to go away but the original deletion should still take place.

Let us pretend that these are ORDER records for a particular customer - this makes it easy to test against the sports database

I'll assume that the criteria is all orders for customer #1

message
  "Delete Customer 1's Orders? " skip(1) 
  view-as alert-box buttons yes-no
  title " Really? "
  update ok as logical
.

if ok = yes then
  do:

    do for order transaction:
       for each order where order.custNum = 1 exclusive-lock:
         delete order.
       end.
    end.

    do for order transaction:
      create order.
      order.custNum = 1.
      update order except order.custNum.
    end.

  end.

 

[MPHANSON]

This worked perfectly, thank you for taking the time to help.

 

[Storzum]

What does the "for order" in

do for order transaction:

mean!

Have it the same effect, when I only use "do transaction:"?

 

[tamhas]

The For Order scopes the order buffer to the block.