CubeSQL Transactions: Serious issue?

Just sent this to Marco:

[code]I always set AutoCommit = True when connecting to a SQLite file that is hosted by CubeSQLServer. This is so that file acts like a native SQLite file when doing INSERTS, UPDATES and DELETES. This way I don’t have to use COMMIT unless I explicitly create a TRANSACTION.

But this has a very serious consequence! If I do a TRANSACTION, ROLLBACK will not work! I never noticed this until last night. The way I have to work around this is to set AutoCommit = False before starting a TRANSACTION, and then set AutoCommit = True after any ROLLBACK or COMMIT.

Do you know about this?[/code]

Actually, I suspect that explicitly written Transactions don’t even work if I set AutoCommit = True. But setting AutoCommit = True lets me do INSERTS, UPDATES and DELETES without doing a Commit each time. Otherwise, CubeSQL is always in Transaction mode and requires explicit Commits.

I found the same reliability issue, despite setting AutoCommit to True, so I always perform a db.Commit after each successful write. I’m glad you have identified it.

Well, it seems a lot easier for me to go find every explicit Transaction I have and change things there. I think it would take a lot more work to look for all writes that are outside Transactions and then DB.Commit them. I have many more of them outside Transactions.

The more I think about this, I doubt my explicit Transactions were ever really working as Transactions, now that I see this. I think AutoCommit overrules them.

I think AutoCommit does not make much sense. It’s a kind of simple idea that fundamentally changes SQLite behavior and its presented in a very obscure way as if it does not matter. I wish it was explicit and performed exactly like SQLite.

[quote=425124:@Ralph Alvy]Well, it seems a lot easier for me to go find every explicit Transaction I have and change things there. I think it would take a lot more work to look for all writes that are outside Transactions and then DB.Commit them. I have many more of them outside Transactions.

The more I think about this, I doubt my explicit Transactions were ever really working as Transactions, now that I see this. I think AutoCommit overrules them.[/quote]
Have you considered making an object for handling your sql commands? It would be fairly easy to have it call Commit in the destructor if there were no errors.

Just got this from Marco via email:

[quote]I confirm the issue and I confirm that your workaround works fine.

We already have the new 5.7.0 version ready to be announce so this fix will not appear in the upcoming 5.7.0 release.[/quote]

Also in the same email:

Not sure exactly how to do that, or that doing that will take less work than simply adding my workaround to Transactions. Such an object would have to handle Database.SQLExecute, RecordSet.Update, RecordSet.InsertRecord and RecordSet.DeleteRecord. Right?