How to implement transactions?

[stiank81]

Hi. I'm trying to implement transactions with Zeos and mysql, but I can't make it work... The mysql tabels are InnoDB, so transactions should be available.

Here is an example:
ZMySqlQuery.Transaction.AutoCommit := False;
ZMySqlQuery.Close;
ZMySqlQuery.Sql.Clear;
ZMySqlQuery.Sql.Add(query);
ZMySqlQuery.ExecSql;
ZMySqlQuery.Transaction.Rollback;
ZMySqlQuery.Transaction.AutoCommit := True;
..
But it won't roll back! What the query did remainds done in the database.... So, what do I do wrong.

I should note that I'm using an older version of Zeos, but as transactionobjects are available here I assume it would be okay. I'm considering an Zeos update next week. If I do should I choose the latest version (alpha) or is it recommended to go for the latest stabel release?


Best Regards,
Stian Karlsen

[apiove]

Please post the code of you create table sql script and the log of TZSQLMoinitor.

Byez,
Andrea

[a_nouri_s]

mysql 5.0.x when supported?

[ste_ba]

stiank81,

while MySQL does support transactions in general with InnoDB, there are a number of things you cannot do within a transaction, e.g. alter a table structure. So it would be of use to know what you tried to do. When inserting, updating or deleting records it should be possible to roll back. Dropping DB objects or adding new columns won't work. Firebird also has certain restrictions for transactions. If you look for a free db where you can do virtually everything within a transaction and roll it back afterwards, use PostgreSQL.

Regards,
Stefan

[stiank81]

Hi. I'm sorry about the late reply - I've been away for a while..

I'm not trying to do any altering of the database structure. What I'm doing is inserting, updating and deleting records. So, yes I thought you should be able to do rollbacks then. But is the code to do it witch I submitted above correct?

Here is an example of a create statement as requested (I can't post the whole DB as that would be quiet long..).

Code: Select all

CREATE TABLE Searchword(
 id int not null auto_increment,
 word varchar(100), 
 primary key(id),
 altered timestamp,
 created timestamp,
 active enum('TRUE','FALSE') not null default 'TRUE'
) ENGINE=InnoDB;

Have transactions been supported in Zeos for a while? As I mentioned I'm using an older version of it. 5.3.4 to be precise.

Any help or ideas is still gladly accepted!


Best Regards,
Stian Karlsen

[apiove]

Hi. I'm sorry about the late reply - I've been away for a while..

I'm not trying to do any altering of the database structure. What I'm doing is inserting, updating and deleting records. So, yes I thought you should be able to do rollbacks then. But is the code to do it witch I submitted above correct?

Here is an example of a create statement as requested (I can't post the whole DB as that would be quiet long..).

Code: Select all

CREATE TABLE Searchword(
 id int not null auto_increment,
 word varchar(100), 
 primary key(id),
 altered timestamp,
 created timestamp,
 active enum('TRUE','FALSE') not null default 'TRUE'
) ENGINE=InnoDB;

Have transactions been supported in Zeos for a while? As I mentioned I'm using an older version of it. 5.3.4 to be precise.

Any help or ideas is still gladly accepted!


Best Regards,
Stian Karlsen

I'm sorry but I don't know if that version of zeos support transaction.
I use the stable version of zeos (6.1.5) with mysql-4.0 driver and transactions works fine!

Regards,
Andrea Piovesan

[ste_ba]

Unfortunately, I am not too familiar with version 5. In version 6, you use a ZConnection component to handle transactions. It has a "StartTransaction" method that sets autocommit to false automatically (just as commit or rollback sets it to true again).
If the old ZQuery.Transaction object has such a method, you might need to call it explicitly. But that's only a guess.

Stefan

[stiank81]

Thanks, great. How could I miss the function "StartTransaction". It is in my version as well, and sounds like something I should use! I've tried, but without luck

Here is what I write now.

Code: Select all

ZMySqlQuery.Transaction.AutoCommit := False;
ZMySqlQuery.Transaction.StartTransaction;
ZMySqlQuery.Close;
ZMySqlQuery.Sql.Clear;
ZMySqlQuery.Sql.Add(query);
ZMySqlQuery.ExecSql;
ZMySqlQuery.Transaction.Rollback;
ZMySqlQuery.Transaction.AutoCommit := True; 

..But the query(delete) is still applied.. Do I have to include the toggling of AutoCommit? I've tried both with and without, but it's the same anyway..

I assume transactions are implemented in Zeos 5 as the "Transaction" object exists... But perhaps I'll update to the latest stable release. It's always good to have the latest things, but it may be some pain to do the updates...

-Stian

[ste_ba]

perhaps I'll update to the latest stable release. It's always good to have the latest things, but it may be some pain to do the updates...

It may be so, but the moment one realises that dealing with the old version is more painful than the act of updating is probably the right moment to do it...

Stefan

[stiank81]

True indeed.. Well - yes, I guess this is a good time to do the update. Thanks for good help anyway!

Stian

[matsgefvert]

We did the update about six months ago. While that was a fairly big job involving updating hundreds and hundreds of units to the new Zeos API, it was actually pretty straightforward.

Mostly, it's about changing the unit names, i.e. including "ZConnection" instead of "ZMySqlDb"; and then changing all TZMySQLDatabase to TZConnection (and similar objects). And all of those pesky Transaction objects, away with them! Phooey!

Once we went through all the dfm and pas files, we just opened them up in Delphi and let Delphi deal with all the superfluous properties. Save, close, rinse. (And find a suitable replacement for StringToSql.)

All in all, after six months of working with Zeos 6.1.5 (in our case), it was all well, well worth it.

/ Matt