[Lazarus] MySQL auto commit

[Aradeonas]

[Cross posted on forum so if you want answer there :
http://forum.lazarus.freepascal.org/index.php/topic,30343.0.html[http://forum.lazarus.freepascal.org/index.php/topic,30097.0.html]]

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.

Here is a test version for you :

SQL code for test db :

> 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:

> 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.

Regards, Ara

-- 
http://www.fastmail.com - A no graphics, no pop-ups email service

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20151111/d14458c1/attachment-0002.html>