MySQL auto commit lazarus / 16-Databases ---------------------------------------------------------------------- aradeonas · 2015-11-11 12:24 Hi, Is there any support for automatically commit changes for MySQL? I test it and it seems there isnt any and I should commit every time I execute a query. ---------------------------------------------------------------------- miab3 · 2015-11-11 12:45 Yes, if you use ZeosLib. http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=3795&p=51399#p5139 9 Michal ---------------------------------------------------------------------- aradeonas · 2015-11-11 12:48 Excuse me for not mentioning component,Im using default TSQLConnector and I just want to use ExecuteDirectwithout committing. Also after that I will get more problem. I always have problem with Lazarus DB component,it is one thing that I miss from Delphi. ---------------------------------------------------------------------- aradeonas · 2015-11-11 13:12 OK here is a test version for you : SQL code for test db : 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  DROP TABLE IF EXISTS `testtable`;CREATE TABLE `testtable` (  `ID` int(11) NOT NULL,  `Title` varchar(255) DEFAULT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;  INSERT INTO `testtable` VALUES ('1', null);INSERT INTO `testtable` VALUES ('2', null);INSERT INTO `testtable` VALUES ('3', null);INSERT INTO `testtable` VALUES ('4', null);INSERT INTO `testtable` VALUES ('5', null);INSERT INTO `testtable` VALUES ('6', null);INSERT INTO `testtable` VALUES ('7', null);INSERT INTO `testtable` VALUES ('8', null);INSERT INTO `testtable` VALUES ('9', null);  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 unit Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, sqldb, mysql55conn; type   { TForm1 }   TForm1 = class(TForm)    procedure FormCreate(Sender: TObject);  private    { private declarations }  public  end; var  Form1: TForm1; implementation {$R *.lfm} { TForm1 } procedure TForm1.FormCreate(Sender: TObject);var  Connector: TSQLConnector;  Transaction: TSQLTransaction;  Query: TSQLQuery;begin  Connector := TSQLConnector.Create(nil);  with Connector do  begin    ConnectorType := 'MySQL 5.5';    HostName := 'localhost';    UserName := 'root';    Password := '';    DatabaseName := 'test';    CharSet := 'utf8';  end;  Transaction := TSQLTransaction.Create(nil);  Transaction.DataBase := Connector;  Query := TSQLQuery.Create(nil);  Query.DataBase := Connector;  Connector.Connected := True;  Query.SQL.Text := 'SELECT * FROM testtable';  Query.Open;  while not Query.EOF do  begin    Connector.ExecuteDirect(Format('UPDATE testtable SET Title="Test" WHERE ID=%d;',[Query.FieldByName('ID').AsInteger]));    Connector.Transaction.Commit;//problem!    Query.Next;  end;end; end.   If I dont do "Connector.Transaction.Commit;" changes will not save (and I want to every changes save excatly when thay post not after all changes done) and if I do "Connector.Transaction.Commit;" then Next line will give error "Operation can't be performed on active transaction". Please someone let me know what is the problem. ---------------------------------------------------------------------- mirce.vladimirov · 2015-11-11 13:19 Friends told me (but I have never tried it) that you should execute a query onafterconnect : 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 thequery.sql.text:='set autocommit=1';thequery.execsql; and that does it while you are connected. But, as I said, I have never tried this. Myself, after each insert,update,delete   I just do 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 thequery.execsql;sqltransaction.commitretaining;  Important, this is all if you are using TMySQLConnection.  I dont have any experience with Connectors, I dont know how it's used. ---------------------------------------------------------------------- aradeonas · 2015-11-11 13:35 Thanks. Changing to MysqlConnection not solved anything even I do auto commit.nothing else solved problem.just commitretaining help to not getting "Operation can't be performed on active transaction" error but still Im seraching for a better way. ---------------------------------------------------------------------- goodname · 2015-11-11 13:38 In the upcoming FPC 3.0 sqlDB there is a implicit transaction option which means every query is automatically committed. My understanding is that the current Lazarus release will work with FPC 3.0rc2. TSQLTransactionOption = (stoUseImplicit, stoExplicitStart); ---------------------------------------------------------------------- aradeonas · 2015-11-11 13:49 I checked it and thanks,it work with Trunk version if I set   Transaction.Options:=[stoUseImplicit];   but there is a problem it just work with MySQLConnection and not with TSQLConnector even if you set ConnectorType to MySQL. This problem is not good but solve my problem in half right now. Thanks. ---------------------------------------------------------------------- goodname · 2015-11-11 15:50 mirce.vladimirov with the upcoming FPC 3.0 you will no longer have to do sqltransaction.commitretaining; which leaves long running idle transactions. You will be able to use sqltransaction.commit; which will keep result sets and close the transaction. With FPC 2.6 commit closed result sets and transactions. This applies to all database connection types. aradeonas some databases do not support implicit transactions such a Firebird. My understanding implicit transactions has been implemented with MySQL and PostgreSQL. Don't know the status of other database engines. You may be one of the first to try implicit transactions with TSQLConnector. This may be something to bring up with the FPC developers. ---------------------------------------------------------------------- LacaK · 2015-11-11 18:48 Quote from: aradeonas on November 11, 2015, 02:49:41 pmI checked it and thanks,it work with Trunk version if I set   Transaction.Options:=[stoUseImplicit];   but there is a problem it just work with MySQLConnection and not with TSQLConnector even if you set ConnectorType to MySQL. With TSQLConnector you get any error ? ---------------------------------------------------------------------- aradeonas · 2015-11-11 20:46 Yes it says your connector not support Implicit. ---------------------------------------------------------------------- LacaK · 2015-11-12 12:06 Okay, then I think, that I see error. Can you test patch If I append them here ? (do you know how to apply patch to source file and recompile?) ---------------------------------------------------------------------- aradeonas · 2015-11-12 14:22 Sure,I can test. No I didnt done it before but a hint will do the job. ---------------------------------------------------------------------- LacaK · 2015-11-13 08:05 Look at attached patch. It is only one line added into sqldb.pp (if you do not know how apply patch, you can edit sqldb.pp and add this one line after 3329 line) ---------------------------------------------------------------------- aradeonas · 2015-11-14 05:56 I edit the file and compile it and even compile and clean all the project and SQLDBLaz package but still it not run the changes even I when I wrote a test writeln in it. Did you know why? ---------------------------------------------------------------------- LacaK · 2015-11-14 17:17 Take sqldb.pp and copy it to your project directory or add to project search paths path to it. (something like: ($FPCSrcDir)/packages/fcl-db/src/sqldb/ ) ---------------------------------------------------------------------- aradeonas · 2015-11-15 07:45 I checked it and now it will compile and run test lines in transaction create but it will never run CreateProxy procedure. Also why we need to add path?how can we change a base unit and compile it? ---------------------------------------------------------------------- LacaK · 2015-11-18 12:52 Try this patch please. ---------------------------------------------------------------------- aradeonas · 2015-11-18 13:29 Now it works very good. Thank you!