Undo A Transaction Created From An Internal Procedure

[dlangschied]

I have a program that launches a browse from an internal procedure. From the browse, the user can update fields in a table, as well as add/delete rows. The issue is that once the control is returned to the main program, that user gets a chance to approve of the changes by selecting proceed "yes" at a prompt. When the user selected "no", the transaction needs to roll back the changes, but this does not occur. Code below:

    innerrack:
    do on error undo, leave:
        if retry then leave.
        ststatus = stline[2].
        status input ststatus.

        p-rackview
        assign proceed_yn = yes.
        {pxmsg.i
            &MSGNUM=12
            &CONFIRM=proceed_yn
            &ERRORLEVEL=1}
        disable all with frame f2.
        hide frame f2 no-pause.
        if not proceed_yn then undo innerrack, next outterloop.

        close query q2.
    end.

procedure p-rackview:
/*Defs*/
    on "f3", "insert-mode", "ctrl-a" anywhere do:
        method-status = b2:insert-row("after") in frame f2.
        return no-apply.
    end.

    on "f5", "get", "ctrl-d" anywhere do:
        def var i as integer.
        message "Are you sure you wish to delete?"
        update yn.
        if not yn then return no-apply.

        do i = b2:num-selected-rows in frame f2 to 1 by -1:
            method-return = b2:fetch-selected-row(i).
            get current q2 exclusive-lock.
            if table_loc = "train" then do:
                message
                "ERROR: Loaded to Train. Cannot delete.".
                pause.
                return no-apply.
            end.
            else delete table.
        end.
        method-return = b2:delete-selected-rows().
        return no-apply.
    end.
    on row-leave of browse b2 do:
        def var i as integer.
        def buffer tbuf for table.
        if b2:new-row in frame f2 then do:
            do on error undo, return no-apply:
                if input browse b2 table_loc = ""
                    and input browse b2 table_part = ""
                    then do:
                    bell.
                    message
                     "ERROR: Location or Part must be entered. Please retry.".
                    pause.
                    method-return = b2:delete-selected-rows().
                    undo, return no-apply.
                end.
        end.
    end.

    on leave of frame f2 do:
    end.

    open query q2
    for each table where table_domain = global_domain
        AND table_nbr = plid
        by table_line.
        enable all with frame f2.
    if startat <> ? then do:
        reposition q2 to rowid startat no-error.
        if not query-off-end("q2") then
        get next q2.
        else get first q2.
    end.
    wait-for "go" of frame f2 or
        "f4" of frame f2.
end procedure.

 

[Stefan]

If you want to entice anyone to look at your code, please add code tags.

 

[tamhas]

And, compile with the listing option to see where the transaction blocks are.

 

[TomBascom]

It is fundamentally wrong to use transaction semantics in conjunction with user-interface logic.

You will be holding record locks and growing the bi file while the user goes to get coffee or a 2 hour lunch or goes home for the day

 

[RealHeavyDude]

You should re-think your design for the reasons Tom explained. This style of pessimistic locking ( implicitely achieved by extending the transaction scope to include user intertaction ) has no place in a modern application that is designed for performance and concurrency.

If you want the user to give an option to commit or rollback a batch of changes you should have the user work on Temp-Tables contained in a ProDataSet. This allows you to track the changes on each of the Temp-Tables and either commit them in an atomic operation to the database or undo them without ever bothering the database.

---------- DISCLAIMER - THIS IS REALLY BAD PRACTICE ----------
Nevertheless -if your life is depending on this and you are not telling anybody that you have this from me: Wrap the call to the internal procedure and your user interaction in a do transaction statement and undo based on the user input. I can't believe that I zaid this ...
---------- DISCLAIMER - THIS IS REALLY BAD PRACTICE ----------

Heavy Regards, RealHeavyDude.