Hi i use the following method to update a table
mdQuery : TUniQuery with cachedupdates := true
SQL: select * from table
In a Procedure I do the following:
with mdQuery do begin
StartTransaction;
"ExcuteSQL"('update table set field1 = 'test' where id = 8')
mdQuery.Edit; << ERROR "Record was changed by another user" only with MS-SQL, Oracle and MySQL works fine
mdQuery.FieldByName('field1').AsString := 'test';
mdQuery.Post;
Commit;
end;
I use this Method with cachedupdates to avoid to refresh from Table cause i only change 1 Field in 100.000 Records.
at the end of all modifikations i usually call mdQuery.CancelUpdates.
Problem with UniDac, CachedUpdates, MS-SQL
I found the Reason on this.
With OLEDBProvider set to prSQL (i use this) You create a savepoint in MS-SQL.
I modified the Code now Savepoints are disabled for prSQL also.
-> No Error anymore
I made try mdQuery.CancelUpdates except end;
to avoid the Exception at the end which is generated caus you rollback an not existing Transaction.
Please think about using TUniQuery as a cached Container which is read only to the Database for Speed - Reasons !
best regards
With OLEDBProvider set to prSQL (i use this) You create a savepoint in MS-SQL.
I modified the Code now Savepoints are disabled for prSQL also.
-> No Error anymore
I made try mdQuery.CancelUpdates except end;
to avoid the Exception at the end which is generated caus you rollback an not existing Transaction.
Please think about using TUniQuery as a cached Container which is read only to the Database for Speed - Reasons !
best regards
-
AndreyZ