ODAC 6.0.0.6: Problem with savepoint command.

[MarkF]

Hi Folks!

Thanks for the new release! I think I've found a problem in the latest build (it may exist in earlier builds as well.) Basically it looks like a command such as 'savepoint spname' won't actually run. Here's my test code:

OQuery.SQL.Text := 'savepoint sp1';
OQuery.Execute();

OQuery.SQL.Text := 'update bonus set ename = ''Testing''';
OQuery.Execute();

OQuery.SQL.Text := 'rollback to savepoint sp1';
OQuery.Execute();

The last statement fails with ORA-01086: savepoint 'SP1' never established.

I found the problem while testing the 'StrictUpdate' option of TOraSQL with a record that I knew would cause more than 1 record to be affected. The internal rollback to savepoint fails with the same 1086 error.

Any help is greatly appreciated!

-Mark

[Plash]

The SAVEPOINT statement has no effect when the AutoCommit property of TOraQuery is True.

[MarkF]

Doh! You're right, I forgot that autocommit defaults to true! However I think there still might be an issue since this means that if autocommit is on, then the strictupdate option doesn't work. Even if autocommit is on, the rollback to savepoint on an update that affects more than one record needs to work. To test this just create a table with a single varchar field and multiple records with the same values. Then try to update any of the values with StrictUpdate turned on. You should see that all the records are affected by the update.

Let me know what you think!

Thanks!

-Mark

[Plash]

When the StrictUpdate option is True, ODAC just raises an exception if more than one record is updated. This option is not designed to rollback changes.

[MarkF]

I could be wrong, but I don't actually think that that is correct. When lock is called it creates a savepoint called 'LOCK_xxx'. If strictupdate is on and more than 1 record is affected, then we get a 'rollback to savepoint LOCK_xxx' which works as long as autocommit is off and fails if autocommit is on.

Also (and this is the kicker) note that the exception says "Update failed. Found n records" but of course the update didn't fail at all! It updated those n records and there is no way to go back.

I think you may want to revisit this, or change the exception message to something like 'Warning: Update changed n records and was commited automatically.'

Now I don't think this should happen very often, but if it does it could be a real problem. I've switched over to using my own commits, so it's not a big deal to me, but others could be affected.

Thanks for your help!

[Plash]

We'll investigate possibility to add such functionality in either of the next ODAC versions.