Hi Team,

   Never done much with Transactions so I thought I would put one in place.  Works perfectly here on my Dev PC but
constantly 'fails' and rolls back at the Customers.

{code}
 dmS.DBC1.StartTransaction(['Backorders', 'Parts', 'ChangesLog']);
 //
 Try
   if nQtyReceived > 0 then
   begin
     Backorders.Edit;
     //
     Backorders.FieldByName('Qty').AsInteger := Backorders.FieldByName('Qty').AsInteger - nQtyReceived;
     Backorders.FieldByName('DateOfLastRcpt').AsDateTime := now;
     if nQtyReceived = nOrderQty then Backorders.FieldByName('ONBO').AsBoolean := False;
     //
     Backorders.Post;
     //
     if Backorders.FieldByName('WtyStock').AsBoolean then
     begin
       Parts.Edit;
       Parts.FieldByName('WtyQty').AsInteger := Parts.FieldByName('WtyQty').AsInteger + nQtyReceived;
     end
     else
     begin
       Parts.Edit;
       Parts.FieldByName('CoyQty').AsInteger := Parts.FieldByName('CoyQty').AsInteger + nQtyReceived;
     end;
     //
     Parts.Post;
     //
   end;
   //
   dmS.DBC1.Commit;
   //
 except
   dmS.DBC1.Rollback;
   MessageBeep(MB_ICONERROR);
   MessageDlg('Backoredered Line Item receipt failed!' + #13 + #10 + 'This may be due to either the Backorders or
Parts tables being locked at the moment.' +
     #13 + #10 + 'Please re-try.', mtError, [mbOK], 0);
   lFailed := True;
 End;
 //
 if not lFailed then
 begin
   //
   edtQty.Text := '0';
   //
   MessageBeep(MB_ICONINFORMATION);
   MessageDlg('Backorder Line Item ' + Backorders.FieldByName('PartNo').AsString + ', Qty ' + IntToStr(nQtyReceived) +
', processed.', mtInformation,
     [mbOK], 0);
   //
 end;
{code}

   My first assumption is that when the transaction attempts to commit that one of the tables is locked although I don't
believe I apply any table level locks in my Apps.

   My second assumption was that the relevant record was open for editing at the time of committal but I have no way of
confirming that.

   My third option is that my code/implementation of the transaction is incorrect..  Yeah, yeah, it's my third not first
option.
   
   dmS. is a DataModule, DBC1 is an EDBDatabase.  But you probably guessed that.

   Is there anything wrong with my implementation please?

   Is there a fourth option?

Regards & TIA,
Ian

On 10/25/2018 7:48 PM, Ian Branch wrote:
>
>    Is there anything wrong with my implementation please?

Looks OK to

>    Is there a fourth option?

Print the actual exception message and it will likely have some useful info

Raul

Raul wrote:

> On 10/25/2018 7:48 PM, Ian Branch wrote:
> >
> >    Is there anything wrong with my implementation please?
>
> Looks OK to
>
> >    Is there a fourth option?
>
> Print the actual exception message and it will likely have some useful info
>
> Raul

Tks Raul,
   I felt it was, just needed the cross check.
   I have added the error handling to the code now so all good.
Regards,
Ian

Ian


>    My second assumption was that the relevant record was open for editing at the time of committal but I have no way of
>confirming that.

This would be my guess, but you do have a way of telling - wrap the .Edit in a try except block. This should enable you to specify which table is locked and also which row.

Couple of points:

1. May be tired eye syndrome but I don't see ChangesLog being used. It shouldn't be there if its not used.
2. Move the Parts.Edit outside the if test


Since this is your first go at a transaction here's an exercise for you. Add in a loop to try two or three times before issuing the error message.  Use a Sleep or a WaitFor (usually I find 150ms enough) to give the other process a change to complete then you can update the stock.

Roy

Hi Roy,

   I reworked the 'except' block to include the error code and message.  It turned out 'DateOfLastRcpt' wasn't in the
Backorders Query fields.  
   I also added specific testing/flagging for a locked table and a locked row.
   Working as expected now.

Regards,
Ian

Also ChangesLog removed.

Ian


>    I reworked the 'except' block to include the error code and message. It turned out 'DateOfLastRcpt' wasn't in the
>Backorders Query fields.

This is why I prefer persistent fields - you get told either as you type or at compile.

Whilst it wouldn't stop this its worth adding a unit (mine's called appFldNames) which is basically a massive const list:

unit appFldNames;

interface

const

_Attribute = '_Attribute';
_Token = '_Token';
_Spam = '_Spam';
_NonSpam = '_NonSpam';
_Total = '_Total';
_Probability = '_Probability';
_ID = '_ID';

At least you can use autocomplete and it stops you ,mistyping field names.

Roy

Hi Roy,
   Interesting concept.
   I generally use Table/Query.fieldbyname('fieldname').asWhatever
   It didn't hiccup in this case at build time.
Regards,
Ian

Ian


I started doing it when I started using more SQL and wanted to interrogate the result sets. The key factor is I'm lazy so I can use code completion. It has the side effect of, if rigorously adhered to, or making sure you only use defined fields. Unfortunately there's nothing to stop that field being defined for another table, or not being included in the result set.

I'm not sure of the effect for either memory or speed in doing one or t'other. Maybe someone with more knowledge may comment.

Roy Lambert