BKeeney Briefs

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?