Update To Data Base Table Is Getting Rolled Back

Pradeep · Jun 26, 2016

Hello All,
I am updating code_mstr field with exclusive lock and I can see it is getting updated. But once I am done with my transaction it is getting rolled back. I am pressing F4 or space bar in between which may be causing this issue.
Please suggest any way I can proceed with the update.

tamhas · Jun 26, 2016

You need to complete the transaction before hitting F4 or you will undo the transaction. Use COMPILE LISTING to see transaction scope.

Pradeep · Jun 26, 2016

Hello sir..Actually it is multiple programs that is being called and there is no separate transaction block being used.

Code:

FIND FIRST code_mstr EXCLUSIVE-LOCK WHERE code_mstr.code_fldname = "xyz"
                                        AND  code_mstr.code_value = "inner"
                                     NO-ERROR.
      IF AVAILABLE code_mstr THEN
      DO:
                  ASSIGN l_vendcd = TRIM(code_mstr.code_user1).
                  IF LENGTH(l_vendcd) < 5 THEN
                      ASSIGN l_vendcd = "0" + l_vendcd .
        IF code_mstr.code_user2 <> "" THEN
        DO:
           ASSIGN l_rsno = INTEGER(TRIM(code_mstr.code_user2)) .
           IF l_rsno > 99999 THEN ASSIGN l_rsno = 00001.
            ASSIGN l_cnt = l_rsno + 1 .
           ASSIGN code_mstr.code_user2 = TRIM(STRING(l_cnt)) .
           RUN dec2hex(INPUT l_rsno, OUTPUT l_seq) . 
           IF LENGTH(l_seq) < 5 THEN 
                    ASSIGN l_seq = FILL("0", 5 - LENGTH(l_seq)) + l_seq .
           ELSE
             ASSIGN l_seq = SUBSTRING(l_seq,1,5).
         END.  /* IF code_mstr.code_user2 */               
      END.

tamhas · Jun 26, 2016

There is always a transaction scope, whether you define it explicitly or not. It can propagate down from a higher level program, so a program that looks like it has no scope may still be within a transaction scope. One of the really big reasons to explicitly define scope is so that you can be sure what the scope is. Define it within an existing scope and the compiler will tell you (unless it comes from a higher level program, so they are separate compile units. COMPILE LISTING is your friend.

First clue in this code fragment is starting with a find. That is likely to end up scoping the transaction to the whole procedure.

Is code_fldname and code_value together a unique key? If so, why are you using find first? If not, why are you using find first?

Use Insert=>Code from the icons at the top to post code and keep it readable. E.g.

Code:

do for buffer_name transaction :
   get and operate on buffer.
end.
no buffer scope or transaction here.

Pradeep · Jun 26, 2016

Hello sir,
That is right. By default progress puts transaction scope.
Field and value together is primary unique key. Any specific reason why I shouldn't use find first. Are you suggesting to use FIND or for first?
Do you want me to try using buffer on code_mstr?

tamhas · Jun 26, 2016

find first is almost always wrong. If the keys provided identity a unique record, then you are sending a false signal to anyone who has to maintain the code since it appears that you want the first of a group. If the keys are not unique, then the implication is that you are treating the first member of the group differently than the rest of the group, which is a violation of third normal form.

I advocate using explicitly named buffers for all database access and explicitly scoping those buffers to a block with for buffer or do for buffer. It really is the only way to be sure where the buffer and transaction scope is. Define the buffer in the block. If you need to access the same table in more than one block, then use a different buffer name. And, use COMPILE LISTING to check your work so that you are sure of your buffer and transaction scope.

Pradeep · Jun 29, 2016

Sir, I have tried all the ways it is getting rolled back.
Is there anything else I could try on F4 to get details updated?

RealHeavyDude · Jun 30, 2016

I guess your transaction scope is much larger than you think. Maybe the procedure of which you provided the code snipet does not start a transaction, but maybe the parent procedure does.

As far as I remember, per default, F4 was always the escape key in the meaning - huh, I did something wrong, please undo and get me out of here.

Heavy Regards, RealHeavyDude.

tamhas · Jun 30, 2016

Have you done a COMPILE LISTING yet on on the programs in the stack?

Pradeep · Jul 4, 2016

Sir, Yes. As there are 5 programs could you please let me know on where to check for exact roll back i.e scoping that is causing the issue.

tamhas · Jul 4, 2016

If you F4 within a transaction, it will roll back. So, don't do that. You need to either limit the scope of the transaction ... always a good idea ... or provide another exit mechanism which won't cause and undo. Note that it is a strong design principle that one should never have a transaction open across UI. Do the UI into local no-undo variables and then do the actual database update in a small transaction that has no UI.

Update To Data Base Table Is Getting Rolled Back

Pradeep

New Member

tamhas

ProgressTalk.com Sponsor

Pradeep

New Member

tamhas

ProgressTalk.com Sponsor

Pradeep

New Member

tamhas

ProgressTalk.com Sponsor

Pradeep

New Member

RealHeavyDude

Well-Known Member

tamhas

ProgressTalk.com Sponsor

Pradeep

New Member

tamhas

ProgressTalk.com Sponsor

Similar threads