How make rollback?

Forum related to version 6.5.1 (alpha) and 6.6.x (beta) of ZeosLib's DBOs

Moderators: gto, cipto_kh, EgonHugeist

Post Reply
Fortuna1
Fresh Boarder
Fresh Boarder
Posts: 9
Joined: 04.11.2007, 11:47

How make rollback?

Post by Fortuna1 »

With AutoCommit set to True, every SQL statement is (auto)commited. When StartTransaction is executed then all statements are executed on the Commit.

I try programing with
linux,
Lazarus, and Firebird.

Zeos componet 6.6.1
Thanks.
piper62
Junior Boarder
Junior Boarder
Posts: 25
Joined: 25.06.2007, 10:40

Post by piper62 »

Hi,
I don't work with Firebird. We use MySQL but my tip is to send SQL commands over an ZQuery to the database.
Then you can directly start a transcation execute your comands/modifications on the database and after that you can decide if you would like to send a "COMMIT;" or a "ROLLBACK;"

Our experience is that to send this directly as SQL statements works perfectly.

Regards,
Tibor
Fortuna1
Fresh Boarder
Fresh Boarder
Posts: 9
Joined: 04.11.2007, 11:47

Post by Fortuna1 »

Conn.AutoCommit:=true;
Conn.StartTransaction;

Q1.Close;
Q1.SQL.Clear;
Q1.SQL.Add('INSERT INTO PLACILA(TEXT) VALUES( "TRALALA")');
Q1.SQL.ExecSQL;
Conn.Rollback;
every SQL statement is (auto)commited. and Rollback not worked.
Problem is AutoCommit:=true;
when I put this

Conn.AutoCommit:=false;
Conn.StartTransaction;


Then I get this message:

Project raised exception class 'EZDdatabaseError' with mesage;
Invalid operation in no AutoCommit mode;
pol
Senior Boarder
Senior Boarder
Posts: 91
Joined: 13.10.2005, 08:19

Post by pol »

Conn.StartTransaction only works in AutoCommit mode. StartTransaction means then "I want to start a new transaction, even when it is not necessary, as I am in AutoCommit mode". Quite strange, but it is so. With most database engines you do not need to start a transaction manually. After a commit or a rollback it is started automatically with a new DML statement.
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post by mdaems »

That not exactly the thruth...
StartTransaction is only available in AutoCommit mode as it provides a temporary escape from this mode. So it stops the adding of a 'commit' statement after every database call. It only lasts until the first commit/Rollback.
So the code fortuna1 showed should rollback indeed, unless some transaction had been closed between StartTransaction and the ExecSql call...
This looks like a bug if this is all code that's involved. If you can post your small sample project (and the database) we may check what's wrong with this functionallity.

Maybe you better only set autocommit to false. A transaction will be automatically started then, and a subsequent call to starttransaction is not necesarry (and impossible).
Fortuna1
Fresh Boarder
Fresh Boarder
Posts: 9
Joined: 04.11.2007, 11:47

Post by Fortuna1 »

mdaems wrote:and a subsequent call to starttransaction is not necesarry (and impossible).

Many thanks for your help.

Im put this rows in program and
now working.



Conn.AutoCommit:=false;

Q1.Close;
Q1.SQL.Clear;
Q1.SQL.Add('INSERT INTO PLACILA(TEXT) VALUES( "TRALALA")');
Q1.SQL.ExecSQL;
Conn.Rollback;

My best regards from Slovenia.
zippo
Silver Boarder
Silver Boarder
Posts: 322
Joined: 12.10.2005, 18:01
Location: Slovenia

Post by zippo »

Hello to Slovenia, too.. :) BTW: When needing commit/rollback control, I usually do it directly via SQL statements, but I admin it is mainly because some bugs in Zeos 5.x .. :)
jpgonzalez
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 31.03.2013, 21:07

Post by jpgonzalez »

I use ZConnection with de autocommit = true
I Have this code:

try
DmMain.ZConnection.StartTransaction;
................
DmOperacionesVehiculos.QryInsertarVehiculo.ExecSQL;
................
................
DmOperacionesVehiculos.QryGuardarOperacion.ExecSQL;
................
DmOperacionesVehiculos.QryGuardarBoleto.ExecSQL;
................
................
DmMain.ZConnection.Commit;
ShowMessage('La operacion de compra se ha guardado con exito');
except
DmMain.ZConnection.Rollback;
ShowMessage('No se pudo guardar la compra');
end;

When I have a problem in the second query (QryGuardarOperacion), Rollback don´t work correctly, because the first query (QryInsertarVehiculo) post the data in the BD, despite i use startTransaction.
This problem creates inconsistency in the database, because the vehicle is post, but not the other information.
I don´t know if this is a bug of the Zeos version.
When I use the autocommit = false, the data is not post in the BD despite I use the commit command.


After that, I tested putting autocommit = false, without the command StartTransaction
This was running the entire block of code perfectly, but the data is not stored in the database.
I could see the data in the program, but not in the database, as if making a post in memory.

I do not know what happens, but in both cases (autocommit = autocommit = true and false) bring me any problems.

Can you tell me what can I do?
The Zeos Version is = 7.0.0-dev

PD: sorry for my english...
doidopb
Fresh Boarder
Fresh Boarder
Posts: 9
Joined: 01.12.2012, 19:52

Post by doidopb »

Check your TransactionLevelIsolation <> TiNone
jpgonzalez wrote:I use ZConnection with de autocommit = true
I Have this code:

try
DmMain.ZConnection.StartTransaction;
................
DmOperacionesVehiculos.QryInsertarVehiculo.ExecSQL;
................
................
DmOperacionesVehiculos.QryGuardarOperacion.ExecSQL;
................
DmOperacionesVehiculos.QryGuardarBoleto.ExecSQL;
................
................
DmMain.ZConnection.Commit;
ShowMessage('La operacion de compra se ha guardado con exito');
except
DmMain.ZConnection.Rollback;
ShowMessage('No se pudo guardar la compra');
end;

When I have a problem in the second query (QryGuardarOperacion), Rollback don´t work correctly, because the first query (QryInsertarVehiculo) post the data in the BD, despite i use startTransaction.
This problem creates inconsistency in the database, because the vehicle is post, but not the other information.
I don´t know if this is a bug of the Zeos version.
When I use the autocommit = false, the data is not post in the BD despite I use the commit command.


After that, I tested putting autocommit = false, without the command StartTransaction
This was running the entire block of code perfectly, but the data is not stored in the database.
I could see the data in the program, but not in the database, as if making a post in memory.

I do not know what happens, but in both cases (autocommit = autocommit = true and false) bring me any problems.

Can you tell me what can I do?
The Zeos Version is = 7.0.0-dev

PD: sorry for my english...
Post Reply