Page 1 of 1
Check UpdateCount on TZUpdateSQL.PostUpdates
Posted: 24.02.2009, 17:18
by ddantoni
Hi,
it should be very interesting if the TZUpdateSQL.PostUpdates method included a parameter which turn on a validation of updates.
In the TZUpdateSQL.PostUpdates method, replace the usual "Statement.ExecutePrepared;" by
Code: Select all
lValidateUpdateCount := StrToBoolEx(
Sender.GetStatement.GetParameters.Values['ValidateUpdateCount']);
lUpdateCount := Statement.ExecuteUpdatePrepared;
if (lValidateUpdateCount) and (lUpdateCount <> 1) then
raise EZSQLException.CreateWithCode(2802, Format('%d record(s) updated. Only one record should have been updated.', [lUpdateCount]));
(or pass the lUpdateCount value to an event)
Thanks,
David
Posted: 25.02.2009, 11:15
by mdaems
Depends on what zeoslib version you are using, but since 6.6.3 this has been added to the changelist:
Default value is false. In Zeoslib 7 it will be enabled by default.
Mark
Posted: 25.02.2009, 12:17
by ddantoni
Hi Mark,
thank for this quick answer.
I use the version "6.6.4-stable" and after a search for "ValidateUpdateCount", I found the modification was only in ZDbcGenericResolver.pas.
The PostUpdates method of TZUpdateSQL is not modified yet.
(I use this purpose to check if another client hasn't modified the same record before I apply the update. I do this thing using a field named LastModifiedTime and I added "WHERE LastModifiedTime=:OLD_LastModifiedTime" in my update query. By this way, if LastModifiedTime has been changed, an exception is raised.)
David
Posted: 25.02.2009, 12:30
by mdaems
Sorry, didn't read your first post correctly, I'm afraid.
Experimenting now...
Mark
Posted: 25.02.2009, 22:24
by mdaems
SVN commit 597. (Testing branch)
Attention : this test will only be done when ValidateUpdateCount is explicitly set in the query properties. This is a different behaviour as in case of the automatically generated queries. There the check will be done always unless the property is explicitly set to false in Zeoslib 7. The reason : TZUpdateSQL is intended for manual intervention by a programmer. When he writes queries for updating he should be aware of possible multiple or zero updates. There checks are only needed when the programmers explicitly wants them.
In 6.6 the property must also be set explicitly for automatically generated updates. We had to do this because the feature was only added very late and we didn't want to break existing applications.
Mark
Posted: 26.02.2009, 10:53
by ddantoni
Thank you !!!
Patch causes duplicate updates
Posted: 15.04.2009, 14:15
by trupka
Current patch causes big problem with duplicate updates and indirectly, violations of primary keys. This completely breaks application. Problem is in ZSQLUpdate.pas , line 789:
Code: Select all
if ExecuteStatement then
begin
Statement.ExecutePrepared; //<-- EXECUTES STATEMENT FIRST TIME
lValidateUpdateCount := StrToBoolEx(
Sender.GetStatement.GetParameters.Values['ValidateUpdateCount']);
lUpdateCount := Statement.ExecuteUpdatePrepared; //<-- EXECUTES STATEMENT AGAIN
if (lValidateUpdateCount) and (lUpdateCount <> 1) then
raise EZSQLException.Create(Format(SInvalidUpdateCount, [lUpdateCount]));
In attachment is small patch against svn://zeos.firmos.at/zeos/branches/6.6-patches rev 624 that fixes this issue.
Posted: 15.04.2009, 14:31
by mdaems
Oops Trupka. I suppose that's my fault... merge error... Problem was automatical merge didn't work because of other differences between testing and stable versions in this file.
I'll try to fix this tonight...
Mark
Posted: 15.04.2009, 20:18
by mdaems
Should be alright now.
Posted: 15.04.2009, 20:59
by trupka
mdaems,
And it is ..
Thank You for effort