Forum > Databases
TSQLQuery.Open starts 2 transactions
Sann-X!:
Hi,
I want to use 2 different transactions (read and write) with same connection to embedded Firebird. My code:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- 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:
Because transactions are uni-directional. You indeed need two separate.
Sann-X!:
--- Quote from: Thaddy on September 24, 2018, 01:20:09 pm ---Because transactions are uni-directional. You indeed need two separate.
--- End quote ---
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:
--- Quote from: Sann-X! on September 24, 2018, 12:35:09 pm ---I want to use 2 different transactions (read and write) with same connection to embedded Firebird.
--- End quote ---
For your example to work correctly, you need, at least, 3 SQLTransactions.
--- Quote ---Line 34 'SQL_R.Open' starts only one transaction Transaction_R. But line 48 'SQL_W.Open' starts two transactions W and R. Why?
--- End quote ---
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- 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!:
valdir.marcos, thank you
Navigation
[0] Message Index
[#] Next page