Recent

Author Topic: TFakeTransaction useful in some situation  (Read 598 times)

kinlion

  • Jr. Member
  • **
  • Posts: 68
  • I Love Lazarus
TFakeTransaction useful in some situation
« on: February 27, 2020, 05:16:50 am »
Problem: I need to change MSSQL Server's login password by fpc code. Unforunately, MSSQL Server tells me that sp_password can NOT be used in a transaction.

I looked on the wiki document https://wiki.lazarus.freepascal.org/SQLdb_Programming_Reference, it tells that every command must be in a transaction.

So, how to do?

Yes, we can define a fake transaction class to cheat the db driver  :)

Below is the code:
Code: Pascal  [Select][+][-]
  1. type
  2.  
  3.   { TFakeTransaction }
  4.  
  5.   TFakeTransaction = class(TSQLTransaction)
  6.   public
  7.     procedure Commit; override;
  8.     procedure CommitRetaining; override;
  9.     procedure Rollback; override;
  10.     procedure RollbackRetaining; override;
  11.     procedure StartTransaction; override;
  12.     procedure EndTransaction; override;
  13.   end;
  14.  
  15. implementation
  16.  
  17. { TFakeTransaction }
  18.  
  19. procedure TFakeTransaction.Commit;
  20. begin
  21.   EndTransaction;
  22. end;
  23.  
  24. procedure TFakeTransaction.CommitRetaining;
  25. begin
  26.   // do nothing
  27. end;
  28.  
  29. procedure TFakeTransaction.Rollback;
  30. begin
  31.   EndTransaction;
  32. end;
  33.  
  34. procedure TFakeTransaction.RollbackRetaining;
  35. begin
  36.   // do nothing
  37. end;
  38.  
  39. procedure TFakeTransaction.StartTransaction;
  40. begin
  41.   OpenTrans; // Just set the active flag
  42. end;
  43.  
  44. procedure TFakeTransaction.EndTransaction;
  45. begin
  46.   CloseTrans; // Just clear the active flag
  47. end;
  48.  

The TFakeTransaction just set/clear the FActive flag which defined in TDBTransaction class privatly on need, nothing else to do.

So, you can do something without a transaction now.  :D

Maybe helpful and enjoy FPC  :)
Lazarus 2.0.8 / FPC 3.0.4 / SVN 62944 / Win10x64

PascalDragon

  • Hero Member
  • *****
  • Posts: 1694
  • Compiler Developer
Re: TFakeTransaction useful in some situation
« Reply #1 on: February 27, 2020, 09:19:55 am »
Alternatively you could take a look at TSQLStatement or TSQLConnection.ExecuteDirect.

kinlion

  • Jr. Member
  • **
  • Posts: 68
  • I Love Lazarus
Re: TFakeTransaction useful in some situation
« Reply #2 on: February 29, 2020, 08:26:58 am »
Alternatively you could take a look at TSQLStatement or TSQLConnection.ExecuteDirect.

Yes, I have seen the source code of TSQLConnection.ExecuteDirect. However, in this procedure, start transaction is also called before execute my command.
So I have to use a TFakeTransaction.

I have not try TSQLStatement.

Lazarus 2.0.8 / FPC 3.0.4 / SVN 62944 / Win10x64

eljo

  • Sr. Member
  • ****
  • Posts: 407
Re: TFakeTransaction useful in some situation
« Reply #3 on: February 29, 2020, 08:54:42 am »
Although a bit backwards its a very useful component that needs to be included by default on the existing SQLDB library. So I would say to add it to the bug tracker as a feature request with the code attached. There are databases that do no support transactions at all as well if I recall correctly (TDBF, or TParadox or something).

kinlion

  • Jr. Member
  • **
  • Posts: 68
  • I Love Lazarus
Re: TFakeTransaction useful in some situation
« Reply #4 on: March 02, 2020, 06:57:14 am »
Although a bit backwards its a very useful component that needs to be included by default on the existing SQLDB library. So I would say to add it to the bug tracker as a feature request with the code attached. There are databases that do no support transactions at all as well if I recall correctly (TDBF, or TParadox or something).

Yes, there are some DB systems have no transaction, but I guess the FPC DB driver has handle it correctly ( I have not tested ). So I don't think it's a bug.
I prefer to advise that the TDBConnection may have a new boolean property AlwaysUseTransaction. Should it be better ?
Lazarus 2.0.8 / FPC 3.0.4 / SVN 62944 / Win10x64

 

TinyPortal © 2005-2018