Multiple updates in one transaction using browse widgets

[mra]

I'm building an application with a master-detail relationship.
The master table i represented in a frame, while the detail table is represented using a browse.

I'm looking for a way to commit both the changes in the master table, and all the changed rows in the detail table in one transaction.

I know it is possible using temp-tables, where the application controls all updates to the database, but I find this approach less elegant.

/Mike

 

[tdi]

I guess a solution for this "most elegant" contest would be to make the main block of the procedure a DO TRANSACTION block.
I can foresee a few disadvantages this would have in <u>my</u> current system, i can't see how is structured yours, but, can you afford a VERY BIG .bi? on the master table, how long will you have blocked the primary key?
The browsers will be share-lock? how many users will use this procedure simultaneusly?, and that's for simplicity, other questions would have been about some internals of the browser...

it's interesting to know where you would prefer "elegant" over "eficient"....

 

[mra]

HMM!

I thought of the "long transaction" solution, but I'm afraid that it won't work because "my" application is heavily and continuously updated by batch programs. They would be blocked by a long transaction in the GUI.

I know that ORACLE can POST changes to the database, which makes them available to the session as though they where committed, but not available to other sessions - Do PROGRESS have a similar feature.

/Mike

 

[Chris Kelleher]

Long transactions, or any transaction that spans user interface is a bad idea... even if your application has a low number of users.

What you should do is work with the record buffer, then commit both the master and detail changes all at once in a small transaction. Here is a sample of what I mean:

<BLOCKQUOTE><font size="1" face="Arial, Verdana">code:</font><HR><pre>
DEF BUTTON btn-save.

FIND FIRST customer NO-LOCK NO-ERROR.

DEFINE FRAME fcust
btn-save customer.NAME.

ON 'choose':U OF btn-save IN FRAME fcust
DO:
FIND CURRENT customer EXCLUSIVE-LOCK NO-ERROR.
ASSIGN customer.NAME.
RETURN.
END.

DO WITH FRAME fcust:
DISPLAY btn-save customer.NAME WITH FRAME fcust.
ENABLE btn-save customer.NAME.
WAIT-FOR "close":U OF THIS-PROCEDURE.
END.

[/code]

Hope this helps,
-Chris

------------------
Chris Schreiber
ProgressTalk.com Manager
chris@fast4gl.com

 

[tdi]

Hi Chris.

You haven't take on account the fact that you may be seen a customer name, while other user has changed it, when you commit, you woul need to use a way ("changed") to know if the record you were seing has been changed.

All this points to that mra has to re-think his (her) need of spanning the UI on a big transaction, as this has been heavily discussed here and there, and all the time the answer is exactly that, it's a bad idea to span the trx to the UI.

Octavio

 

[mra]

I May not have described my problem clearly, so I'll try to be a little more specific.

I've designed a number of screens where users can manipulate the data in the database. Each screen uses one frame and a query to retrieve data from the database.

Users manipulate the data on the screen, and saves it by pressing the SAVE-button.
By pressing the SAVE-button the application locks the data, checks for locks and changes in the database, updates data and finally releases the lock.

- So far so good - This works out fine.

Here is the problem.

To represent master-detail relationships, In use browse widgets.
The frame is used to manipulate the master, and the browse to manipulate the detail.
The problem is, that while changes in the master table is done only by pressing the SAVE-button, changes in the detail table is done automatically whenever users leave the browse-row.

Users find this difference very confusing - can't blame them.

Is there a way, so that all changes both master and detail can be done together??
One solution would be to create a shadow temp-table.
Copy data in to the temp-table on post-query in the master, and save changes in the temp-table when the master is saved.
This however requires lots of coding, plus you have to be very careful about locks and changes made to the detail tables.
- What is the correct action to take, if one of the detail rows is changed by another user, but not the rest???

In hope somebody can help me.

/Mike


By the way Octavio - I'm a HE!!