commit on MySql + Zeos lib

Question

I use next code to post some records on a non autocommin connection:

ZConnection1.AutoCommit := False;

  try
    ZTable1.Insert;
    ZTable1.FieldByName('name').AsString := 'John Doe';
    ZTable1.Post;
    ZConnection1.Commit;
  except
    ZConnection1.Rollback;
  end;

When I run this code nothing is saved on my database.

After I try next code ...

ZConnection1.AutoCommit := False;

   with ZQuery1 do begin
      SQL.Text := 'INSERT INTO mytable (name) values ("John Doe")';
      ExecSQL;
      SQL.Text := SQL.Text + 'COMMIT' ;
      try
        ExecSQL;
      except
        SQL.Text := 'ROLLBACK';
        ExecSQL;
      end;
    end;

... all work perfect.

What I do wrong on first case? I use Delphi6, MySql, Zeos library and InnoDB table.

Have you actually activated/opened the table before calling the code you have shown? — Marjan Venema, Mar 15 '13 at 09:44
Yes, all are opened. I belive that in case of inactive table Delphi report it as error. I don't get any error message. — gogoloi, Mar 15 '13 at 09:52
Yes it does but you had the insert within the try and didn't mention the exact error message... — Marjan Venema, Mar 15 '13 at 11:02

score 7 · Answer 1 · edited Mar 15 '13 at 11:36

Have fixed my problem:

This is how AutoCommit property actually works in Zeos:

when AutoCommit is True, then the transactions are commited automatically after each executed SQL statement, but you can use the StartTransaction command explicitly to prevent this auto commiting, until you explicitly call Commit.
when AutoCommit is False, you shouldn't call StartTransaction. Then the transaction is started automatically, but it will not commit automatically after every executed statement.

ZConnection1.AutoCommit := True;
ZConnection1.StartTransaction;
try
  ZTable1.Insert;
  ZTable1.FieldByName('name').AsString := 'John Doe';
  ZTable1.Post;
  ZConnection1.Commit;
except
  ZConnection1.Rollback;
end;

commit on MySql + Zeos lib

1 Answers1