TFakeTransaction useful in some situation
lazarus / 63-Database

----------------------------------------------------------------------
kinlion · 2020-02-27 04:16

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i type   {
TFakeTransaction }   TFakeTransaction = class(TSQLTransaction) 
public    procedure Commit; override;    procedure CommitRetaining;
override;    procedure Rollback; override;    procedure
RollbackRetaining; override;    procedure StartTransaction; override; 
  procedure EndTransaction; override;  end; implementation {
TFakeTransaction } procedure TFakeTransaction.Commit;begin 
EndTransaction;end; procedure TFakeTransaction.CommitRetaining;begin 
// do nothingend; procedure TFakeTransaction.Rollback;begin 
EndTransaction;end; procedure
TFakeTransaction.RollbackRetaining;begin  // do nothingend; procedure
TFakeTransaction.StartTransaction;begin  OpenTrans; // Just set the
active flagend; procedure TFakeTransaction.EndTransaction;begin 
CloseTrans; // Just clear the active flagend; 
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. 

Maybe helpful and enjoy FPC

----------------------------------------------------------------------
PascalDragon · 2020-02-27 08:19

Alternatively you could take a look at TSQLStatement or
TSQLConnection.ExecuteDirect.

----------------------------------------------------------------------
kinlion · 2020-02-29 07:26

Quote from: PascalDragon on February 27, 2020, 09:19:55
amAlternatively 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.

----------------------------------------------------------------------
eljo · 2020-02-29 07:54

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 · 2020-03-02 05:57

Quote from: eljo on February 29, 2020, 08:54:42 amAlthough 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 ?