commit_on_exit dbisql.exe option is not overriden in SA 12.0.1.4165

[former_SQLA_member1694875]

Hello,

It seems that commit_on_exit option is not overriden in dbisql.exe (version 12.0.1.4165). By default that option is set to "On" (Tools->Options->SQL Anywhere->Commands->Commit on exit or disconnect). When using

SET TEMPORARY OPTION commit_on_exit = 'Off';

I would expect this option to be overriden. But the following example gives unexpected results.
Create simple table and insert single row:

CREATE TABLE IF NOT EXISTS tmp_test_commit_on_exit (t TIMESTAMP);
INSERT INTO tmp_test_commit_on_exit (t) VALUES (CURRENT TIMESTAMP);
COMMIT;

Get inserted value (and remember it):

SELECT * FROM tmp_test_commit_on_exit;

Then run the following statements (without commit):

SET TEMPORARY OPTION commit_on_exit = 'Off';
UPDATE tmp_test_commit_on_exit SET t = CURRENT TIMESTAMP;

After that press F12 (disconnect) or close Interactive SQL. Reconnect/reopen and check the value again:

SELECT * FROM tmp_test_commit_on_exit;

Unfortunately, it gives different timestamp than above.

In SA11 it works as expected (the option is temporarily overriden).

Can anybody confirm that this is a bug, or am I missing something? Thanks. 🙂

[Former Member]

Thank you for this observation Arthoor,

This looks like a small oversight introduced in the rewrites introduced for 12.0.0 and up.

This is being looked into as CR#773279.

As a possible workaround you should be able to use dbisqlc or stay with dbisql.exe from version 11.0.x until this gets resolved. If one could anticipate the disconnect, one could control the transaction directly but that would not always be possible if an unexpected exit occurs, say, due to a user prompt on a error dialog.