4GL help - Transaction needs to be undone but one subtransaction needs to be commited

[spach]

Hello Team,

This is regarding progress 9 code. I have a code where there is a large transaction block and when user hits CTRL-C the transaction is undone which is fine. One small piece of code withing the transaction block should not be undone and I am trying to accomplish this without making significant changes and restructuring of the existing code. Please note that the sub block is a database updates which should not be undone.

My question you expert is: Is there a way we can undo a transaction block but commit one small sub block within it using some trick?

Thank you all for your help in advance.

 

[LarryD]

Re: 4GL help - Transaction needs to be undone but one subtransaction needs to be comm

There are a few ways to do this besides the obvious one of use no-undo fields and no-undo temp-tables and do the transaction commits at the end....

Can you give us a sample of the code? Is it event driven? Character or GUI?

 

[tamhas]

Re: 4GL help - Transaction needs to be undone but one subtransaction needs to be comm

You can declare variables no-undo, but not database fields. Your only way to preserve the database updates is to move them into a separate transaction scope.

 

[spach]

Re: 4GL help - Transaction needs to be undone but one subtransaction needs to be comm

Thanks LarryD and Tamhas.

It is character code and not event driver.

In its simplest form is may look as follow:

Do transaction:
/* block 1 */
1. Do some DB updates
/* block 2 */
2. generate a new document based on some conditions and print a document..... Update db tables
/* block 3 */
3. continue processing ... with db updates
end. /* transaction block */

What I have to do is to commit block 2 all the time whereas block 1 and 3 can be undone. I was wondering if there is something available in Progress/Openedge, that I am not aware of, will allow this without dividing the transaction if possible.

I hope this helps to make understand the issue.

 

[tamhas]

Re: 4GL help - Transaction needs to be undone but one subtransaction needs to be comm

The simple answer is no. You can do the processing of 1 and 3 in one transaction block, saving information if necessary in no-undo variables and then follow this with block 2 which commits whether or not the first one succeeds., but you can't commit a DB transaction within a transaction scope and have it stick, whether or not the overall transaction sticks.

You might want to talk a little about the business use case here and why, if this is all designated as one transaction, you now want to make one part unequivocal.

 

[LarryD]

Re: 4GL help - Transaction needs to be undone but one subtransaction needs to be comm

I'm making the assumption that the document does print regardless...

EDIT: I'd suggest trying the second example, as it is a tad more straighforward...

you might want to try something similar to this:

def temp-table t-table no-undo like db-table.
def var save-recid as recid no-undo.
def var was-updated as logical no-undo.
def var was-stopped as logical no-undo.

assign was-updated = false
          was-stopped = true.

Do transaction
    on stop undo, leave: 
 /* block 1 */ 
 1. Do some DB updates 
 /* block 2 */ 
 2.   save-recid = recid(db-table).
2a.  create t-table.
2a.  buffer-copy db-table to t-table. /* save db buffer for initial values */
2b. generate a new document based on some conditions and print a  document..... Update db tables
2c. buffer-copy db-table to t-table.  /* now has updates */
2d.  was-updated = true.
/* block 3 */ 
 3.  continue processing ... with db updates 
was-stopped = false.
end. /* transaction block */ 
if was-stopped and was-updated then run now-update-table (save-recid).

procedure now-update-table.
def input parameter this_recid as recid no-undo.
def buffer db-table for db-table.
do for db-table transaction:
     find db-table exlusive-lock where recid(db-table) = this_recid.
     buffer-copy t-table to db-table.
end.
end procedure.

another better example below ...

 

[LarryD]

Re: 4GL help - Transaction needs to be undone but one subtransaction needs to be comm

or if you have multiple tables and a few fields to update, you could try the following.

say 2 tables, 2 fields from table1 and 1 field in table2 ... don't forget to clear the temp-table if needed.

def temp-table t-table no-undo 
    field tbl1-recid as recid
    field tbl2-recid as recid
    field tbl1-fld1 like table1.fld1
    field tbl1-fld2 like table1.fld2
    field tbl2-fld1 like table2.fld1.

def var was-updated as logical no-undo.
def var was-stopped as logical no-undo.

assign was-updated = false
          was-stopped = true.

Do transaction
    on stop undo, leave: 
 /* block 1 */ 
 1. Do some DB updates 
 /* block 2 */ 
2. generate a new document based on some conditions and print a  document..... Update db tables
2a.  create t-table.
2b.  assign t-table.tbl1-recid = recid(table1) /* save stuff for initial values */
            t-table.tbl2-recid = recid(table2)
            t-table.tbl1-fld1 = table1.fld1
            t-table.tbl1-fld2 = table1.fld2
            t-table.tbl2-fld1 = table2.fld1
            was-updated = true.
/* block 3 */ 
 3.  continue processing ... with db updates 
was-stopped = false.
end. /* transaction block */ 
if was-stopped and was-updated then run now-update-table.

procedure now-update-table.
def buffer table1 for table1.
def buffer table2 for table2.

find first t-table.
do for table1,table2 transaction:
     find table1 exlusive-lock where recid(table1) = t-table.tbl1-recid.
     find table2 exlusive-lock where recid(table2) = t-table.tbl2-recid.
     assign table1.fld1 = t-table.tbl1-fld1
            table1.fld2 = t-table.tbl1-fld2
            table2.fld1 = t-table.tbl2-fld1.
end.
end procedure.

I may be throttled for using "like" and "find first" and many other non-"best practice" issues with either of the above ... but something along these lines should do what you need it to do.

Seriously, though, Thomas is correct... in the long run you do need to think about redoing it with proper transaction scope. The db locking issues alone are a reason to re-write it.

 

[TomBascom]

Re: 4GL help - Transaction needs to be undone but one subtransaction needs to be comm

Another option is to move the part that you do not want undone to another session. Either an app server call or a spawned process.

 

[sdjensen]

Re: 4GL help - Transaction needs to be undone but one subtransaction needs to be comm

Export the records you do not want to be undoed to a file and after the transaction, import them.

Ugly hack but it could do the trick. :biggrin:

 

[spach]

Re: 4GL help - Transaction needs to be undone but one subtransaction needs to be comm

Thank you Gentlemen for your great suggestions. I am restructuring the program and divide the transaction.