REAL Studio: Is AutoCommit False by Default Bad?

Since REALbasic (now REAL Studio) 2009 R5.1 updated the SQLite database engine the RB Forums have had more than a few RB users complaining that the database no longer works.  Thankfully the community has been able to get the message out that the AutoCommit property of the REAL SQL Database is now, by default, false.  I think it should be true by default and the RS did the users a disservice by forcing this change on us.

As an advanced user I understand completely why autocommit should be false.  However, as someone who has fielded some of the forum questions and have even have this bite me in an old project I can’t imagine why this change was considered a good idea.

I’m not sure if the change fixed a bug or the RS engineers realized that it wasn’t working the way they expected it to but setting the AutoCommit to false, however, breaks a lot of existing programs.  That all by itself, in my opinion, was a big mistake – even if it ‘fixed’ a bug.  Someone evaluating RB for their DB application now sees a spate of “The database doesn’t work for me anymore” messages in the forums.  Are they going to really look much further even if the solution is as simple as db.autocommit = true?

Second, because the change is silent and subtle, a lot of developers were pretty mystified (and pissed off).  Heck, I didn’t spot it until I was doing the REALbasic training videos and realized that data wasn’t being saved.  I’ve been using RB for for over eight years and I spent more than a few minutes scratching my head.  How does that make a beginner feel about REAL Studio?

I know that I should be checking the error condition of the database after every transaction.  I know that I should be handling the commit and rollback on my own.  I know all this and do those very things (most of the time) and yet still I got bit.  I can’t imagine how the rest of the RB universe felt when they finally found out why their db apps weren’t working properly any more.

What’s worse is the Language Reference for AutoCommit is lacking anything close to an appropriate example.  The AutoCommit entry has this to say:

The default is False: Changes open implicit transactions that you must close explicitly. If you set AutoCommit to True, then the database will no longer open implicit transactions for you, and instead, will automatically commit each database change immediately. It is recommended that you leave AutoCommit set to False unless you understand the implications of setting it to True.

How about a little discussion on the implications?  And of course there’s no example of how to properly do this unless you’re smart enough to check out the examples in the parent REALSQLDatabase  entry and smart enough to realize that you also need to read the examples in the Recordset entry.

Let’s be brutally honest for a minute.  Most RB users want their changes to be committed automatically because they’re not SQL guru’s.  They just want their application to work and don’t care about what the ‘best practices’ are for databases.  It’s been a while since I’ve used VB6 and ADO but I don’t ever remember having to tell my data to commit.  I do remember having to add code to start a transaction and commit and rollback my transactions.  So why was this RB change a good one?

Unfortunately, now that AutoCommit has been like this for a few releases we can’t put the genie back in the bottle.  I would ask that in the future that REAL Software deprecate ‘broken’ methods and replace them with a new one so that it’s obvious that something has changed.

Did the AutoCommit change affect you at all?  Did it make sense right away or did you have to hunt the reason down?  Was the change necessary and a good change?

3 thoughts on “REAL Studio: Is AutoCommit False by Default Bad?

  1. Once again it is a sensible move that is not adequately flagged/explained by RS causing unneccessary confusion to users.

    Most of my database projects use a standard DB object that deals with transactions and multiple database types, and has Autocommit set to false anyway. I only noticed when I loaded in an existing “quick and dirty” project which doesn’t use it, and it took a few minutes to work out what was wrong -but of course I knew the code worked previously, and what Commit/Autocommit is all about anyway.

    What the default should be is fairly arbitrary – good practice database is for Autocommit to be false, so it is fine for RB to default to that _providing this is made clear to users_. So IMO the fault is in the Language Reference and examples in not making clear to users what the implications are; and in RS not highlighting this as a “major change” for upgrading users.

    From RS’s point of view it would not appear a major change (AIUI it will be one switch in the compilation of the SQLite code) but the change of almost any default will affect a lot of users. Likewise with the introduction of Foreign Key constraints in SQLite (yipee!) the SQLite default is having this off, and if RB shipped with this on, this would need to be highlighted even though it would affect hardly any upgrading users. (Incidentally, to switch it on using a Pragma command, you cannot be in a multistatement transaction, so having Autocommit true by default would simplify switching Foreign Key constraints on 😉 )

  2. Prior to 5.1, REALSQLDatabase automatically did a commit when the program closed. I can kind of see how that isn’t 100% consistent with the documentation for AutoCommit, but my advice to REAL (if they had asked) would have been to add a line to the documentation explaining that that was the case instead of making a behind the scenes change that potentially causes people’s programs to “lose” data.

    Of course, it’s not too hard to simulate the old behavior if you have a global database object, you can just add a gDb.Commit to Application Close. I also try to always commit after a logical task in the program is complete, but there’s always the chance I missed a spot.

  3. Thanks for this article… I’ve just discovered this change this morning… I’m moving my project from RB 2008r3 to 2010r1 and didn’t understand why my tables wasn’t created anymore ‘-_-. Then I’ve read about this commit thing in forums and feared about the 2 hundreds sqlExecute that would need a db.Commit… By chance, I remembered that there was something like an “autocommit” boolean 😛 .

    Moving a project from a not so old version of RB is really a pain this time… 🙁 (not talking about the fact that a recordset with no record is no longer EOF but nil… I’ve had to change a ton of “if” statements)

Comments are closed.