CubeSQL Transactions: Serious issue?

  1. 6 weeks ago

    Ralph A

    Feb 15 Santa Monica, California
    Edited 6 weeks ago

    Just sent this to Marco:

    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?

    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.

  2. David C

    Feb 15 Pre-Release Testers, Xojo Pro Derby, ITM

    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.

  3. Ralph A

    Feb 15 Santa Monica, California

    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.

  4. Phillip Z

    Feb 15 Pre-Release Testers, Xojo Pro Florence, SC

    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.

  5. 5 weeks ago

    Greg O

    Feb 16 Xojo Inc

    @Ralph A 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.

    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.

  6. Ralph A

    Feb 17 Santa Monica, California

    Just got this from Marco via email:

    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.

    Also in the same email:

    Please note that we are working on a major 6.0 upgrade where autotransactions will be off by default.

  7. Ralph A

    Feb 17 Santa Monica, California
    Edited 5 weeks ago

    @Greg OLone 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.

    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?

or Sign Up to reply!