How make rollback?

[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]

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]

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]

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.

[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]

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]

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]

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]

Check your TransactionLevelIsolation <> TiNone

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