It’s not been a good day. First, I found out that Commits and Rollsbacks don’t like to work in MS SQL Server because of a bug <https://xojo.com/issue/32121>.
So we switched to ODBC to the same MS SQL Server database and a simple Insert in a transaction doesn’t like to insert. DB shows no errors either. Anybody seen this? This provides no error but no insert either:
[code]db.SQLExecute(“Begin transaction”)
if db.error then
break
end if
db.SQLExecute(“insert into addresses (Name, Active) values(‘GEOFFRY’,0)”)
if db.Error then
break
end if
I’m not sure if it’s a known issue.
I’ve always done my database work with the raw SQL commands rather than the built in functions since they are a bit of a black box.
The LR does specifically say that you should be able to use db.Commit after (and only after) starting a transaction with the appropriate SQL command db.SQLExecute("BEGIN TRANSACTION")
I guess the question is, if db.SQLExecute("COMMIT TRANSACTION") works in Bob’s case, but db.Commit doesn’t, then what is db.Commit actually doing other than just sending the COMMIT TRANSACTION command to the database?
In your record insert example above, what is the advantage of using a transaction when using the MSSQL Server plug in? I only ask because it raises a question as to my understanding of transactions, at least how they relate to MSSQL.
In your case above, if the insert fails is there really anything to “RollBack”? My understanding, based somewhat on the linked article is the default mode for MSSQL is AutoCommit, unless of course you use the “begin transaction”, “end transaction” and “commit”, which turns it off for that transaction. Now I understand that if you are executing several dependent transactions then wrapping those in begin and end makes perfect sense, in case a portion of them fail. But for most simple inserts, deletes, updates to a single record in the table, what actually gets rolled back if the operation fails and is actually never written?
Well, it’s really a simple, nonsense example. It was proving what we were seeing in ActiveRecord without all the overhead.
In ActiveRecord we fire events Before/After Save/Create/Delete/Update and if the user does anything in those events they’re automagically part of the same transaction. But you’re right, most of the time it’s not necessary to be in a transaction but we provide it just in case. And in AR we provide the ability to create your own transaction block which overrides the one we do in the Save/Delete methods.
