ISQL 16 Autocommit

[Former Member]

It's bad enough that the default action of ISQL in version 16 is to commit on closing. At least you can turn that off. However when you do you are presented with this

every time you close down. I just can't find anywhere to turn this off. Can anyone tell me how to stop this message appearing?

Paul

[former_member195252 · Accepted solution]

The development team had another look at this enhancement request. 

Recognize that the warning dialog was an enhancement request in its own right. Starting in version 16, DBISQL displays a warning if you shut it down (or disconnect) and you have uncommitted database changes, and DBISQL is not configured to commit on exit.

In SQL Anywhere version 16, Build 2159 or higher, and version 17 Build 1212 or higher, the window that contains that warning now has a checkbox which allows you to suppress the warning.

These builds will be available in a future patch level.

[Former Member · Accepted solution]

I've spent a while wondering how to answer Mirco's response from the developers; Breck was right on the money as usual. It sounds like the solution was more complicated than necessary but nevertheless as long as I don't have to put up with that box coming up I'll have to be content.

Whether any tool should commit on disconnect by default is well and truly debatable but I guess we'll just have to accept that the developers of DBISQL think it's a good idea.

Thanks Chris.

Paul

[former_member207653]

I have not found a way to disable that message. It could be an enhancement request.

Why would you need to run SQL statements without committing them?

[Former Member]

That particular screen grab came from a session that contained 5 select statements. Why would you need to ask such a stupid question?

[former_member188493]

Phrases like "stupid question" are generally regarded as too harsh and impolite for this forum... but not in this case : )

[Former Member]

I apologise. I read that remark at the end of a very tiring day, much of it spent cleaning my PCs of the Microsoft virus that's going to download 3Gb of Windoze 10 whether you want it or not. Although my response was intemperate and, as Breck points out not befitting the forum, at least he understands my point.

There is no circumstance in which you'd want any software to automatically commit changes to the database. Being of the old, tired and very grumpy persuasion I can do a whole bunch of SQL and just close the dbisql session, not pausing to think about any updates I might have tried. Ask any PowerBuilder instructor (if you can find one) and amongst the first thing you talk about when discussing database connections is to set AutoCommit=FALSE. The default for dbisql to autocommit is, in my view at least, criminal.

The plain fact is that DBISQL isn't very good, and I generally use another tool. However, certain things, especially the OUTPUT command, mean that you have to use it.

So the question remains unanswered. How can I get rid of this stupid, and this time I mean it, message?

Paul

[former_member207653]

In effect, if you were just executing SELECT statements, that becomes a stupid question.

I am trying to reproduce with SQL Anywhere 16.0.0.2127 and the demo database. I have just written five very simple queries and if I disconnect or exit ISQL, that window does not appear.

Are you using the same build number? If yes, I would like to see the code of your queries please.

[Former Member]

I checked out what I'd been doing. You can try this with the demo db....

begin

declare local temporary table emps(dept varchar(64),emp varchar(64));

insert into emps

select d.DepartmentName,e.Surname||', '||e.GivenName

from departments d join employees e on e.DepartmentID = d.DepartmentID;

select * from emps;

end;

and when you close it will prompt you to commit changes to a table that (in theory at least) no longer exists. Here's my settings:

FWIW I'm also on build 2127.

[former_member207653]

I have reproduced. It is because of the INSERT statement. If I comment it out, the window does not pop up.  Otherwise, I have not found any option to disable that message. What about if I submit an enhancement request?

[Former Member]

I think that's a very good idea, yes please. You'd hope that people using SQL Anywhere, or more specifically dbisql, are competent to be using databases. The autocommit default and this message smacks of what they call the 'nanny state' down here.

I can't help wondering if it would be a reversion rather than an enhancement. In 20+ years using SQLA and various flavours of dbisql I can't recall this message coming out before, and I always turn off autocommit (and fast launcher) immediately after installing the software.

[former_member207653]

It is CR # 786658. Let us see what happens.

Thank you

[former_member207653]

Our developers have looked into this enhancement request and turning off the dialog would mean an implicit ROLLBACK on disconnect or shutdown. You should add a commit or rollback as needed for preventing the dialog pop-up. No code change will be implemented in the product.

[former_member188493]

Please explain your response.

If "implicit ROLLBACK" means "dbisql would have to execute a ROLLBACK before disconnecting", the question is "who is currently doing the COMMIT"?

The SQL Anywhere server does not commit on disconnect, so dbisql would just have to disconnect and the rollback would be effectively done. The only code change would be to remove the dialog, or add an option to suppress the dialog.