I am trying to master the editing of master/detail datasets without any success.
If someone can help me understand what I need to do it would be really, really, really appreciated because at the moment it is beyond me.

I am using the Dive Shop tables Customers and Orders with each order belonging to a Customer.
I can insert and edit a Customer.  I am using a nested transaction to edit or insert a customer's order.

When I click OK to COMMIT the ORDER transaction, I get the error:
** Cannot close csOrder dataset whilst there are still active transactions for the dataset

I have at 2 windows.

1. fmEditCustomer:
  * shows the details for a customer and all their orders
  * allows customer to be edited or inserted
  * a customer order can be selected for editing
  * requests that an order be added to the customer
2. fmOrder:
  * allows an order to be inserted or edited.

     
// ************** fmEditCustomer *********************************************

procedure TfmEditCustomer.fmEditCustomerShow(Sender: TObject);
begin
  Database.AfterCommit := AfterCommit;
  Database.AfterRollback := AfterRollback;

  if fCustNo = 0 then
  begin     
     //insert
     csCustomer.Open;       //opens a one row dataset
     csCustomer.Insert;
     // other stuff
  end else
  begin
     // edit
     // set parameters for csCustomer to load row for single customer
     DataBase.LoadRows(csCustomer);

     // set parameters for csOrdersForCustomer to load all orders for the customer
     Database.LoadRows(csOrdersForCustomer);
  end;

  DataBase.StartTransaction;
end;

// ************************ OK button clicked on fmEditCustomer *****************************

procedure TfmEditCustomer.buOKClick(Sender: TObject);
begin
  csCustomer.CheckBrowseMode;

  if Database.InTransaction then
  begin
     Database.Commit;
  end else
  begin
     Close;
  end;
end;


// *************************************** Inserting an Order for customer then create fmOrder window

procedure TfmEditCustomer.InsertAnOrder;
begin
  fmEditOrder := TfmEditOrder.Create(nil,
        csOrdersForCustomer.Columns['CustNo'].AsInteger,   // Customer number
        0);                                                // 0 to insert order
  fmEditOrder.OnClose := fmEditOrderClose;
  fmEditOrder.ShowModal;
end;

// **************************************** Close after commit has completed
procedure TfmEditCustomer.AfterCommit(Sender: TObject);
begin
  ShowMessage('After Commit: ' + IntToStr(Database.TransactionLevel));
  Close;
end;



// ********************** fmEditOrder ***************************************************
A nested transaction is started when the Edit Order window is shown.

When I click OK to COMMIT the transaction, I get the error:
** Cannot close csOrder dataset whilst there are still active transactions for the dataset

I am not closing the form (and dataset) until the transaction is completed (in AfterComplete).


procedure TfmEditOrder.csOrderAfterLoad(Sender: TObject);
begin
  if fOrderNo = 0 then
  begin
     //insert
     fEmpNameOld := '';
  end else
  begin
     // edit
     fEmpNameOld := csOrder.Columns['Employee'].AsString;     
  end;
end;


procedure TfmEditOrder.buOKClick(Sender: TObject);
begin
  csOrder.CheckBrowseMode;

  if Database.InTransaction then
  begin
     Database.Commit;
  end else
  begin
     Close;
  end;
end;


procedure TfmEditOrder.AfterCommit(Sender: TObject);
begin
  ShowMessage('After Commit: ' + IntToStr(Database.TransactionLevel));
  Close;
end;

//==========================================================================

Richard,

<< When I click OK to COMMIT the ORDER transaction, I get the error:
** Cannot close csOrder dataset whilst there are still active transactions for the dataset >>

Commits are asynchronous in EWB because the underlying server requests used to execute them are asynchronous.

Given this architecture, you need to use a TDatabase.AfterCommit event handler to close any datasets involved in the transaction, otherwise you'll try to close a dataset while the transaction is still committing.

BTW, you can use the LogOutput procedure in the WebHTTP unit to conveniently log debug messages to the IDE's Messages panel while your application is running.  It's sometimes more convenient than using ShowMessage because it doesn't interrupt the application execution and you can see the messages in the proper execution order.

Tim Young
Elevate Software
www.elevatesoft.com

Thank you Tim.

I must be missing something basic.

Below is the AfterCommit and Before Commit methods.  I changed Close to csOrder.Close to the After Commit event.

procedure TfmEditOrder.AfterCommit(Sender: TObject);
begin
  // logging code  
  csOrder.Close;
end;

function TfmEditOrder.BeforeCommit(Sender: TObject): boolean;
begin
  // logging code
  Result := true;
end;

Below is the result of the log messages.  This explains why EWB thinks that the csOrder dataset still has active transactions associated with it - but I do not understand how the 2 operations in the Before Commit could be merged into 1 operation in the After Commit.

Does this make any sense?


Edit Order form - Before commit
-------------------------------
Timestamp:    11/3/2015 10:08 AM   Before Commit
Transaction Level:    1
Comment:    Click OK: Edit Order form
{   "transactions": [
{   "operations": [ { "dataset": "csCustomer", "operation": 2, "beforerow": { "EWB_RowID": 1, "CustNo": 1000, "Company": "A New Company", "Addr1": "28 Freeman Drive", "Addr2": null, "City": "Maitland", "State": "NSW", "Phone": "02 4930 7336", "FAX": null, "TaxRate": 3.25 }, "afterrow": { "EWB_RowID": 1, "Company": "A New Company +++++" } } ] },
{   "operations": [ { "dataset": "csOrder", "operation": 2, "beforerow": { "EWB_RowID": 1, "OrderNo": 1860, "CustNo": 1000, "SaleDate": 823392000000, "ShipDate": null, "EmpNo": 9, "Employee": "Forest, Phil", "AmountPaid": 28 }, "afterrow": { "EWB_RowID": 1, "AmountPaid": 1024 } } ] }
] }
----------------------------------------------------------

Edit Order form - After commit
------------------------------
Timestamp:    11/3/2015 10:08 AM   After Commit
Transaction Level:    0
Comment:    Click OK: Edit Order form
{   "transactions": [
{   "operations": [ { "dataset": "csCustomer", "operation": 2, "beforerow": { "EWB_RowID": 1, "CustNo": 1000, "Company": "A New Company", "Addr1": "28 Freeman Drive", "Addr2": null, "City": "Maitland", "State": "NSW", "Phone": "02 4930 7336", "FAX": null, "TaxRate": 3.25 }, "afterrow": { "EWB_RowID": 1, "Company": "A New Company +++++" } },  { "dataset": "csOrder", "operation": 2, "beforerow": { "EWB_RowID": 1, "OrderNo": 1860, "CustNo": 1000, "SaleDate": 823392000000, "ShipDate": null, "EmpNo": 9, "Employee": "Forest, Phil", "AmountPaid": 28 }, "afterrow": { "EWB_RowID": 1, "AmountPaid": 1024 } } ] }
] }
----------------------------------------------------------


I was unable to get LogOutput to work - no messages are appearing in the message panel with the following line.
LogOutput('After Commit: ' + IntToStr(Database.TransactionLevel), DEFAULT_LOG_URL);

Richard

On 11/2/2015 6:29 PM, Richard Harding wrote:
> I must be missing something basic.


Richard - for most of us it would help if you could attach a simplified
sample project - this is involved enough (and missing enough code) that
i would not even attempt to set it up from scratch. Tim might be the
only one.

Looking at your initial post your customer insert logic appears to be
doing a "csCustomer.Open" and "csCustomer.Insert" in local context only
(i.e. you don't have an active transaction).


> Below is the result of the log messages.  This explains why EWB thinks that the csOrder dataset still has active transactions associated with it - but I do not understand how the 2 operations in the Before Commit could be merged into 1 operation in the After Commit.

It looks like you have mismatched starttransaction and commit calls:

- Transaction Level 1  means there are 2 pending commits.

- When you now call commit the transaction level is decremented and JSON
appended to previous level but nothing is sent to server (meaning we're
still active). AfterCommit is called still.

- transaction level 0 means you are still in transaction and need
another commit
- when you call commit now the data is packaged and sent to server and
AfterCommit is called again. Transaction level now is -1.

> Does this make any sense?

Yes.

> Edit Order form - Before commit
> -------------------------------
> Timestamp:    11/3/2015 10:08 AM   Before Commit
> Transaction Level:    1
> Comment:    Click OK: Edit Order form
> {   "transactions": [
> {   "operations": [ { "dataset": "csCustomer", "operation": 2, "beforerow": { "EWB_RowID": 1, "CustNo": 1000, "Company": "A New Company", "Addr1": "28 Freeman Drive", "Addr2": null, "City": "Maitland", "State": "NSW", "Phone": "02 4930 7336", "FAX": null, "TaxRate": 3.25 }, "afterrow": { "EWB_RowID": 1, "Company": "A New Company +++++" } } ] },
> {   "operations": [ { "dataset": "csOrder", "operation": 2, "beforerow": { "EWB_RowID": 1, "OrderNo": 1860, "CustNo": 1000, "SaleDate": 823392000000, "ShipDate": null, "EmpNo": 9, "Employee": "Forest, Phil", "AmountPaid": 28 }, "afterrow": { "EWB_RowID": 1, "AmountPaid": 1024 } } ] }
> ] }
> ----------------------------------------------------------

Two transactions active

> Edit Order form - After commit
> ------------------------------
> Timestamp:    11/3/2015 10:08 AM   After Commit
> Transaction Level:    0
> Comment:    Click OK: Edit Order form
> {   "transactions": [
> {   "operations": [ { "dataset": "csCustomer", "operation": 2, "beforerow": { "EWB_RowID": 1, "CustNo": 1000, "Company": "A New Company", "Addr1": "28 Freeman Drive", "Addr2": null, "City": "Maitland", "State": "NSW", "Phone": "02 4930 7336", "FAX": null, "TaxRate": 3.25 }, "afterrow": { "EWB_RowID": 1, "Company": "A New Company +++++" } },  { "dataset": "csOrder", "operation": 2, "beforerow": { "EWB_RowID": 1, "OrderNo": 1860, "CustNo": 1000, "SaleDate": 823392000000, "ShipDate": null, "EmpNo": 9, "Employee": "Forest, Phil", "AmountPaid": 28 }, "afterrow": { "EWB_RowID": 1, "AmountPaid": 1024 } } ] }
> ] }
> ----------------------------------------------------------


One transactions still active - you still need to call Commit one more
time.


> I was unable to get LogOutput to work - no messages are appearing in the message panel with the following line.
> LogOutput('After Commit: ' + IntToStr(Database.TransactionLevel), DEFAULT_LOG_URL);

Are you by any chance running non port other than 80 ? If so then do not
use the "DEFAULT_LOG_URL".

Simply calling  "LogOutput('After Commit: ' +
IntToStr(Database.TransactionLevel));" is all you need.

logoutput('transaction Level =' + inttostr( database.TransactionLevel));
works just fine here and i see following in the message window:

[Internal Web Server] Log output: transaction Level =0



Raul

Thanks Raul,

I have completely misunderstood nested transactions.  I finally found the bit in the relevant part in the manual.

*** If the current transaction level is greater than 0, then append all operations in the current transaction to the next lower transaction.

I was expecting that the POST request would be sent to the server irrespective of whether the transaction was nested or not.

I was creating, opening and closing the datasets associated with the detail datasets (in this case, the ORDERS) on the same form the was editing the detail dataset.  The detail datasets need to be created, opened and closed on the form that edits the master dataset - not the detail datasets.

Hopefully I am getting closer to completing my first EWB project.  Just need to sort out reporting - which Tim has already addressed in a previous post.

Many thanks.

Richard

On 11/2/2015 11:05 PM, Richard Harding wrote:
> I have completely misunderstood nested transactions.  I finally found the bit in the relevant part in the manual.
> *** If the current transaction level is greater than 0, then append all operations in the current transaction to the next lower transaction.
> I was expecting that the POST request would be sent to the server irrespective of whether the transaction was nested or not.

Current design IMHO makes it really easy to start/commit transactions on
module level and also allows proper rollback for situations like nested
transactions.

> I was creating, opening and closing the datasets associated with the detail datasets (in this case, the ORDERS) on the same form the was editing the detail dataset.  The detail datasets need to be created, opened and closed on the form that edits the master dataset - not the detail datasets.

If i understand this right then it's very much part of your app design
and transaction considerations - technically it can work either way.


> Hopefully I am getting closer to completing my first EWB project.  Just need to sort out reporting - which Tim has already addressed in a previous post.

Sounds great!

Raul

Richard,

I see you got the rest straightened out...

<< I was unable to get LogOutput to work - no messages are appearing in the message panel with the following line. >>

You don't need to include the URL parameter since it defaults to "localhost/log", but it should work (I use it a *lot*, as you can imagine).  If you're not seeing the output in the messages panel in the IDE, then it may not be executing for some reason.

Tim Young
Elevate Software
www.elevatesoft.com