TSQLQuery.Open starts 2 transactions
lazarus / 16-Databases

----------------------------------------------------------------------
Sann-X! · 2018-09-24 10:35

Hi,
I want to use 2 different transactions (read and write) with same
connection to embedded Firebird. My 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  
IBConnection := TIBConnection.Create(nil);  IBConnection.LoginPrompt
:= False;  IBConnection.DatabaseName := scFirebirdDBFile; 
IBConnection.Dialect := 3;  IBConnection.CharSet := 'WIN1251';  
Transaction_R := TSQLTransaction.Create(nil);  Transaction_R.DataBase
:= IBConnection;  Transaction_R.Params.Add('read_committed'); 
Transaction_R.Params.Add('rec_version'); 
Transaction_R.Params.Add('read');   Transaction_W :=
TSQLTransaction.Create(nil);  Transaction_W.DataBase := IBConnection; 
Transaction_W.Params.Add('read_committed'); 
Transaction_W.Params.Add('rec_version'); 
Transaction_W.Params.Add('nowait');   SQL_R := TSQLQuery.Create(nil); 
SQL_R.DataBase := IBConnection;  SQL_R.Transaction := Transaction_R;  
SQL_W := TSQLQuery.Create(nil);  SQL_W.DataBase := IBConnection; 
SQL_W.Transaction := Transaction_W;   IBConnection.Open;  
SQL_R.Close;  SQL_R.SQL.Clear;  SQL_R.SQL.Add('select * from
Channels');  Transaction_R.StartTransaction;  try    SQL_R.Open;   
Result := SQL_R.FieldByName('Name').AsString;   
Transaction_R.Commit;  finally    if Transaction_R.Active then     
Transaction_R.Rollback;    SQL_R.Close;  end;   SQL_W.Close; 
SQL_W.SQL.Clear;  SQL_W.SQL.Add('select * from Channels'); 
Transaction_W.StartTransaction;  try    SQL_W.Open;    Result :=
SQL_W.FieldByName('Name').AsString;    Transaction_W.Commit;  finally 
  if Transaction_W.Active then      Transaction_W.Rollback;   
SQL_W.Close;  end;  
Line 34 'SQL_R.Open' starts only one transaction Transaction_R. But
line 48 'SQL_W.Open' starts two transactions W and R. Why?

----------------------------------------------------------------------
Thaddy · 2018-09-24 11:20

Because transactions are uni-directional. You indeed need two
separate.

----------------------------------------------------------------------
Sann-X! · 2018-09-24 11:53

Quote from: Thaddy on September 24, 2018, 01:20:09 pmBecause
transactions are uni-directional. You indeed need two separate.
Thanx for your reply, but I don't understand you. Please explain to me
in more detail or tell me how to fix my code.

----------------------------------------------------------------------
valdir.marcos · 2018-09-24 15:27

Quote from: Sann-X! on September 24, 2018, 12:35:09 pmI want to use 2
different transactions (read and write) with same connection to
embedded Firebird.For your example to work correctly, you need, at
least, 3 SQLTransactions.

QuoteLine 34 'SQL_R.Open' starts only one transaction Transaction_R.
But line 48 'SQL_W.Open' starts two transactions W and R.
Why?IBConnection needs a default transaction (Transaction_D) to send
and receive information from Firebird.
When you missed that, Transaction_R - the first transaction component
to be associated to IBConnection - is used as IBConnection default
transaction component for all SQLQuerys (SQL_R and SQL_W).
That's why you also see Transaction_R also being used on SQL_W
commands.

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      
IBConnection := TIBConnection.Create(nil);     
IBConnection.LoginPrompt := False;      IBConnection.DatabaseName :=
scFirebirdDBFile;      IBConnection.Dialect := 3;     
IBConnection.CharSet := 'WIN1251';                        // Default
transaction for all internal IBConnection needs      Transaction_D :=
TSQLTransaction.Create(nil);      Transaction_D.DataBase :=
IBConnection;      Transaction_D.Params.Add('read_committed');     
Transaction_D.Params.Add('rec_version');     
Transaction_D.Params.Add('read');                       
IBConnection.Transaction := Transaction_D;                       
Transaction_R := TSQLTransaction.Create(nil);     
Transaction_R.DataBase := IBConnection;     
Transaction_R.Params.Add('read_committed');     
Transaction_R.Params.Add('rec_version');     
Transaction_R.Params.Add('read');           Transaction_W :=
TSQLTransaction.Create(nil);      Transaction_W.DataBase :=
IBConnection;      Transaction_W.Params.Add('read_committed');     
Transaction_W.Params.Add('rec_version');     
Transaction_W.Params.Add('nowait');           SQL_R :=
TSQLQuery.Create(nil);      SQL_R.DataBase := IBConnection;     
SQL_R.Transaction := Transaction_R;           SQL_W :=
TSQLQuery.Create(nil);      SQL_W.DataBase := IBConnection;     
SQL_W.Transaction := Transaction_W;           IBConnection.Open;    
    SQL_R.Close;      SQL_R.SQL.Clear;      SQL_R.SQL.Add('select *
from Channels');      Transaction_R.StartTransaction;      try       
SQL_R.Open;        Result := SQL_R.FieldByName('Name').AsString;     
 Transaction_R.Commit;      finally        if Transaction_R.Active
then          Transaction_R.Rollback;        SQL_R.Close;      end;  
       SQL_W.Close;      SQL_W.SQL.Clear;      SQL_W.SQL.Add('select *
from Channels');      Transaction_W.StartTransaction;      try       
SQL_W.Open;        Result := SQL_W.FieldByName('Name').AsString;     
 Transaction_W.Commit;      finally        if Transaction_W.Active
then          Transaction_W.Rollback;        SQL_W.Close;      end;

----------------------------------------------------------------------
Sann-X! · 2018-09-25 04:36

valdir.marcos, thank you

----------------------------------------------------------------------
Sann-X! · 2018-09-25 04:47

So the default transaction will lives as long as IBConnection has
connected state? Is it safe and bugless?

----------------------------------------------------------------------
valdir.marcos · 2018-10-31 05:03

Quote from: Sann-X! on September 25, 2018, 06:47:48 amSo the default
transaction will lives as long as IBConnection has connected
state?Yes.

QuoteIs it safe and bugless?Yes.