FireDAC Transactions in MySQL and UniGUI

[Osama Ghazal]

Hi All,

I have a big problem in FireDAC Transactions With UniGUI and MySQL

The Problem is that the Connection does not commit the transaction until I close the program and re-open it or until i add another voucher.

for example,

If I have two users working on the same database, every user open the program on his browser.

If (User 1) add new voucher let's say the new VoucherID will be 125, after seconds (User 2) wants also to add a new voucher, the fdquery can't find that the (User 1) added voucherID Number 125, so it will gives him that the new VoucherID also 125 not 126. This happened when I have many queries executed with one transaction.

I did't face this problem when I work on Normal Desktop Application in delphi, only when I use UniGUI Trial Version:1.90.0 build 1564 and older

The MySQL Database is on Outside Server with Mochahost and I am using Delphi 10.3

My Code is like the following:

var

VoucherID : Integer;

Begin

  qryGetVoucherID.Close;

  qryGetVoucherID.Open;

VoucherID :=   qryGetVoucherIDNewID.Value; // the query statement is: Select IfNull((Select Max(VoucherID) + 1 From vouchersheaders),1) as NewID;

  qryVouchersHeaders.Append;

  qryVouchersHeadersVoucherID.Value := VoucherID; //

.......

.....

  FDConnection.StartTransaction;

try

  qryVouchersHeaders.Post;

....

....

 qrysaveCheques_payments.execute(chRecCount,0);

 qrysaveWallets_payments.execute(chRecCount,0); 

qrysaveAccountingEntries.execute(chRecCount,0);

FDConnection.Commit;

qryVouchersHeaders.Refresh;
qryCheques_Payments.Refresh;
qryWallets_Payments.Refresh;

qryAccounting_Entries.Refresh;

except

FDConnection.rollback

end;

 

End;

[asapltda]

could you add :

  Except
    on E: Exception do begin
      ShowMessage(E.Message);
  end;

 

then Try again 

[zhyhero]

What place (or unit） is your " FDConnection" created ?

[Osama Ghazal]

1 hour ago, asapltda said:

could you add :

  Except
    on E: Exception do begin
      ShowMessage(E.Message);
  end;

 

then Try again 

I did it already, but this code is abridgement code, and it did commit but no effect until append a new record or log out from the program.

it didn't run exception or rollback, it run commit and save the voucher

[Osama Ghazal]

36 minutes ago, zhyhero said:

What place (or unit） is your " FDConnection" created ?

the connection place is uniMainMudule and in uniservermodule i put fdManager

[Osama Ghazal]

what i see is when I make the voucher cash payment it will execute the post command and  qrysaveAccountingEntries.execute(chRecCount,0);

and it goes ok, and other user can found the voucher directly, but if the payments id cheques or e-wallets and run many execute commands on many tables this cause that the new voucher does not appeared on the other user until the first user add another voucher or logout from his program.

only the last voucher don't appeared on other users, it seems that it stored in cache, put the cache update always false not true

[zhyhero]

How about check "InTransaction" ?

 

  if  FDConnection.InTransaction then
  begin
    ShowmessageN('Last Transaction is not finish');
    Exit;
  end;

 

  FDConnection.StartTransaction;

  try

      ...

      FDConnection.Commit;

  except

      FDConnection.Rollback;

  end;

[Osama Ghazal]

i did it already yesterday 😪

[Abaksoft]

On 10/23/2022 at 1:31 PM, Osama Ghazal said:

Hi All,

 

Hello,

1. Your VoucherID should be  into your Global Transaction :

 

FDConnection.StartTransaction;

try

VoucherID:=

Select IfNull((Select Max(VoucherID) + 1 From vouchersheaders),1) as NewID;

....

2. It seems to me you forgot to commit

FDConnection.Commit;

except

FDConnection.rollback

end;

__________________

I am not using FireDac. Instead i use devart technology (so easy, light and robust)

That said, you can read :

http://www.unigui.com/doc/online_help/index.html?firedac.htm

[Osama Ghazal]

On 10/24/2022 at 10:29 PM, Abaksoft said:

 

Hello,

1. Your VoucherID should be  into your Global Transaction :

 

FDConnection.StartTransaction;

try

VoucherID:=

Select IfNull((Select Max(VoucherID) + 1 From vouchersheaders),1) as NewID;

....

2. It seems to me you forgot to commit

FDConnection.Commit;

except

FDConnection.rollback

end;

__________________

I am not using FireDac. Instead i use devart technology (so easy, light and robust)

That said, you can read :

http://www.unigui.com/doc/online_help/index.html?firedac.htm

Thank you for your answer.

but Only Now I Found the problem.

the problem is that When I Open a Cheques Form by Mistake I forget to delete StartTransaction from Onshow.

so there is two starttransaction, one when save the voucher and other when show the cheques form.

thank you again

[Tarshku]

👂