ORA-01086: savepoint 'LOCK_ORAQUERY1' never established in this session or is invalid

[sinys]

Delphi XE2, ODAC 9.0.2
ORA-01086: savepoint 'LOCK_ORAQUERY1' never established in this session or is invalid

OraQuery1.Opetions.StrictUpdate := True;

1) create table simple_table (id number);
2) fill it with 1, 1 . Commit.
3) Update In grid 1 => 2
4) Update failed. Found 2 records - It's OK, but SQL Monitor showing
ORA-01086: savepoint 'LOCK_ORAQUERY1' never established in this session or is invalid
5) Commit;
6) Another session: select * from simple_table
show 2,2 instead of the expected 1, 1

[AlexP]

Hello,

Thank you for the information. We have reproduced the problem and will investigate the reasons of such behaviour.

[sinys]

If I change LockMode to lmLockImmediate then it's work fine, but I get message "Refresh failed. Found 2 records", why 'Refresh' if it Update operation?

[AlexP]

Hello,

After updating a record we try to call the Refresh operation for the updated record, and since there are more than 1 such records in your case - we generate this error. To avoid this error, you should set the StrictUpdate property to False

[sinys]

But in the first setuation I get "Update failed. Found 2 records" - it message more suitable.

>set the StrictUpdate property to False
I want to get this message when I update or delete more then 1 record, but I want to get the correct message for my operation. I do Update and get a message about Refresh - it's weird for my users.

[AlexP]

Hello,

You can handle this error and return your message, e.g., as follows:

Code: Select all

  try
    OraQuery1.Edit;
    OraQuery1.Fields[0].AsInteger := 2;
    OraQuery1.Post;
  except
    on E: EDatabaseError do
      if pos('Refresh failed. Found', e.Message) > 0 then        ShowMessage('Your message');
  end;

However, it will be more correct to use primary key in the table - in this case, only one record will be guaranteed updated

[sinys]

However, it will be more correct to use primary key in the table - in this case, only one record will be guaranteed updated

I know it, but its presence is dependent on the end-user query.

on E: EDatabaseError do
if pos('Refresh failed. Found', e.Message) > 0 then ShowMessage('Your message');

I think it's can be potentially dangerous because you can change this message in future or you can use this message for another operation (like Delete may be, I didn't check it, sorry)

[AlexP]

Hello,

You can add a ROWID field to user queries and use it as KeyField for unique record identification.
We will consider the possibility to modify the error message in one of the next versions.

[AlexP]

Hello,

We have fixed the behaviour: now, ROLBACK is not called on error occurrence if a Savepoint wasn't set before that.