Recent

Author Topic: Zeos transaction error - "invalid operation in non AutoCommit mode"?  (Read 13097 times)

JD

  • Hero Member
  • *****
  • Posts: 1767
Hi there everyone,

I am using the code below to manage transactions in my application.
Code: Pascal  [Select]
  1. procedure TForm1.DoTransaction(DataSet: TDataSet);
  2. begin
  3.   with ZConnection1 do
  4.   begin
  5.     if not AutoCommit then
  6.     begin
  7.       StartTransaction;     //Gives me an error 'invalid operation in non AutoCommit mode'
  8.       try
  9.         Commit;
  10.       except
  11.         Rollback;
  12.       end;
  13.       Dataset.Refresh;
  14.     end;
  15.   end;
  16. end;
  17.  
What I find odd is that I get an error "invalid operation in non AutoCommit mode" everytime I try to update the database. I later discovered that the error is due to the line

Code: [Select]
StartTransaction

Once I comment out this line, everything works fine.

Is this normal? Why should the explicit call to "StartTransaction" on a Zeos connection generate an error? Does setting "AutoCommit := False" implicitly start a transaction? Is it one or the other but not the two at the same time?

Thanks for your assistance.
Windows (10, 7) - Lazarus 2.0.6/FPC 3.2, Delphi

Indy 10.6 series; mORMot; Zeos 7.3; SQLite, Firebird, PostgreSQL & MariaDB; VirtualTreeView 5.5.3 R1

pierre.se.pos

  • New Member
  • *
  • Posts: 17
Re: Zeos transaction error - "invalid operation in non AutoCommit mode"?
« Reply #1 on: March 02, 2011, 06:47:12 am »
Hi

Please see the entry on StartTransaction on the following page:
http://zeos.firmos.at/kb.php?mode=article&k=6


Zoran

  • Hero Member
  • *****
  • Posts: 1475
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Zeos transaction error - "invalid operation in non AutoCommit mode"?
« Reply #2 on: March 02, 2011, 09:52:17 am »
Despite what you can expect, you must not use StartTransaction when AutoCommit is False!

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 explicitely to prevent this auto commiting, until you explicitely call Commit.
  • when AutoCommit is False, you should not call StartTransaction. Then the transaction is started automatically, but it will not commit automatically after every executed statement.

JD

  • Hero Member
  • *****
  • Posts: 1767
Re: Zeos transaction error - "invalid operation in non AutoCommit mode"?
« Reply #3 on: March 02, 2011, 10:10:55 am »
Thanks a lot pierre.se.pos & Zoran. I now understand how to use AutoCommit better.
Windows (10, 7) - Lazarus 2.0.6/FPC 3.2, Delphi

Indy 10.6 series; mORMot; Zeos 7.3; SQLite, Firebird, PostgreSQL & MariaDB; VirtualTreeView 5.5.3 R1