What is the correct way on setting and rolling back to savepoint on SQLAnywhere. Having this snippet:
begin transaction;
SAVEPOINT spt_abc;
insert into eins (pl1) values (5);
SAVEPOINT spt_123;
UPDATE eins SET pl1 = 10 where pl1 = 5;
ROLLBACK TO SAVEPOINT spt_abc;
commit;
Getting this error message while running it in dbisql:
Could not execute statement. Savepoint 'spt_abc' not found SQLCODE=-220, ODBC 3 State="HY000" Line 6, column 1 You can continue
executing or stop.ROLLBACK TO SAVEPOINT spt_abc
the very same happens when trying to rollback to spt_123.
Best Answer
This behaviour seems to be caused by the option to auto commit after each command in dbisql configurable via Tools->Options->SQLAnywhere->Execution->"Commit after every statement". If this option is set, I can reproduce the error described at the question. Unchecked it's working as expected.