[solved] "hard" commit

[k1attila1]

Hello


Can i send "hard" commit to the sqlserver (firebird) from ZEOS ?

Thanks

[marsupilami]

Hello k1attila1,

what do you mean by a "hard" commit?
Best regards,

Jan

[k1attila1]

hello, marsupilami

"only" Commit and not commitretain.


I think (studied source code of zeos) ZEOS commit = commitretain

[marsupilami]

Hello k1attila,

I think, this should be possible but I have to take a closer look at the sources and transaction arameters.
But if you change to commit instead of commit retaining this might lead to problems with open cursors because they will get lost and so you might get get problems with open datasets.
Best regards,

Jan

[k1attila1]

Yes it is true, but mi biggest problem is that at RepeatableRead isolation level i can't see th changes of another users. But i have used Lazarus SQLDB componets set, and there i have tried sqlquery.commit (and not sqlquery.commitretain) + sqlquery.open and i saw the changes. (and that not lost the cursor - why i don't know ? i will studie the source code of SQLDB)

I would like to simulate it in ZEOS.

ZConnection.ExecuteDirect('COMMIT') - lost the cursor

[marsupilami]

Maybe you should try to use tiReadCommitted and try to set the WhereMode of the ZQuery to wmWhereAll? Mabe Zeos will then realize when nothing is updated and raise an exception in that case?

[k1attila1]

Yes, it works perfectly.

[k1attila1]

But at "repeatableread" isolation level, i think the only one solution is :

Refresh button onclick e.g. :

ZTable1.Close;
ZConnection1.Disconnect;
ZConnection1.Connect;
ZTable1.Open;

[marsupilami]

Hello k1attila1,

you can force the Firebird driver to do "hard commits" by adding 'hard_commit=TRUE' to TZConnection.Properties. But it may be that this might just not do what you want. It will break cursors if they are used by the Firebird driver so you will have to close all datasets beforehand. Also it might not help with the problem of lost updates if Zeos is doing commits too often. So maybe you want to create a log using ZSQLMonitor, and watch out for the timiong of all the commits done.
Best regards,

Jan

[k1attila1]

thanks for your patience marsupilami

But i think there is 2 good solution :

at READCOMMITED : (your suggestion : wmwhereall)

at REPEATABLE READ : ZTable1.Close;
ZConnection1.Disconnect;
ZConnection1.Connect;
ZTable1.Open;

This not too elegant, but i can't find better solution.

I think these are the easiest solutions.

[marsupilami]

Hello k1attila1,

I did not test anything else yet and so cannot make any useful suggestions. In most of my applications lost updates are no big problem, so I don't have to deal with this problem too much.
You could try to use something like this for repeatable read with explicit transaction control:

ZConnection1.Properties.Add('hard_commit=TRUE');
ZConnection1.AutoCommit := False;
ZConnection1.Open;
ZTable1.Open;
// Do some editing here
ZTable1.Close;
ZConnection1.Commit;
ZTable1.Open;

But beyond this, I cannot make any suggestions, that are more useful.
Best regards,

Jan

[k1attila1]

OKAY , JAN !

Many thanks ! It works !

Attila Kov?cs