REALSQLDatabase -> SQLiteDatabase: How to turn off AutoCommit

  1. 8 months ago

    Jürg O

    15 Mar 2019 Pre-Release Testers, Xojo Pro

    I'm going to update a couple of older projects that are still using REALSQLDatabase with using SQLiteDatabase.
    Quite some functions are currently setting REALSQLDatabase(DB).AutoCommit = False. And the generated SQL Statements of course require that (e.g. add an explicit COMMIT after an Update-Statement that is outside of a Transaction).

    So: How am I going to deal with that?
    It seems there is no SQLiteDatabase.AutoCommit any longer… Is there some other way to get the "old" behavior? Or do I need to dig through all of the code to respect that (by removing the Commit's for Updates outside of Transactions - such as it would not be neccessary if AutoCommit is ON)?

    I'd prefer to have AutoCommit explicitly disabled... and not having to use SQLiteDatabase in AutoCommit mode.

    Let's sum this up: AutoCommit can't be turned off with SQLiteDatabase.
    When moving from REALSQLDatabase to the newer SQLiteDatabase:
    One has to be aware the REALSQLDatabase used by default AutoCommit=False, whereas SQLiteDatabase is using SQLite default behavior AutoCommit=True.

    @Norman P Autocommit = FALSE is, if i recall correctly, only code in the plugin itself which is why there is no pragma etc to change it in SQLite
    And it just starts transactions for you instead of you doing it by hand

    If one has been explicitly using REALSQLDatabase.AutoCommit=True: just change to the new class.
    If not: you have to manually take care of the now different AutoCommit behavior.
    The simplest way is

    @Norman P just subclass sqlitedatabase and override connect so it starts a transaction, and commit so that on success it starts another
    then use this custom subclass without changing the rest of your code

    Without a custom subclass: Start a Transaction after Connect. Replace SQLExecuted COMMIT/ROLLBACK's to start a new Transaction. Take care of places where one has manually started a BEGIN TRANSACTION.

  2. Joseph E

    15 Mar 2019 Pre-Release Testers CA

    I think what you are are after is Begin Transaction and Commit. It is in the language reference for the SQLite DB.

    If you start your transaction with Begin, it will not commit until the commit is initiated.

    COMMIT

  3. Jürg O

    15 Mar 2019 Pre-Release Testers, Xojo Pro

    No. What I'm after is trying to figure out what the old REALSQLDatabase did when setting .AutoCommit=True/False
    The applications use both… Transactions with Begin/Commit-Rollback. And some places where there is just a single Record Update/Delete/Insert (along with a COMMIT). And I don't want to find and remove all those "COMMITS" - but set the Connection again to AutoCommit=Off mode, so that the old behavior can be used without changes. :)

  4. Norman P

    15 Mar 2019 Pre-Release Testers, Xojo Pro great-white-software.com/blog

    Any where you set autocommit = false put in a start transaction since somewhere later you do call Commit
    Correct ?

  5. Jürg O

    15 Mar 2019 Pre-Release Testers, Xojo Pro

    @Norman P Any where you set autocommit = false put in a start transaction since somewhere later you do call Commit
    Correct ?

    Well... the thing is that it's only in one place:

     if DB.Connect then
        REALSQLDatabase(DB).AutoCommit = false

    So right after .Connecting I used to set AutoCommit to OFF - that doesn't help to quickly find all places in the code where this might have an effect, and for that reason I can't just do a search/replace :)

    I'm still wondering: What has been going on when setting REALSQLDatabase.AutoCommit=True/False?
    Can this be done with some Pragma (I doubt it, as I couldn't find anything)? Or has this changed some "underlying Plugin/API behavior" (which is not possible using SQL Statements)? What is the replacement for the "no longer existing" .AutoCommit in SQLiteDatabase (if there is none: why is that "by design")?

  6. Norman P

    15 Mar 2019 Pre-Release Testers, Xojo Pro great-white-software.com/blog

    basically everything from that to any commit is in a transaction

    so replacing that with begin transaction would have the same effect - everything would be in a transaction

    but .. you'd probably need to turn every commit into "commit begin transaction" to get the same efffect as autocommit off

  7. Jürg O

    15 Mar 2019 Pre-Release Testers, Xojo Pro

    I'm fully aware of that... it's just that REALSQLDatabase.AutoCommit =False has been the default (and it's what I'm used to, and what all these applications are originally written to use).
    And it seems to me that SQLiteDatabase is using Autocommit=True (the documentation is sparse and just says: By default, SQLite does a Commit after each SQL command that changes the database or its data.)

    So moving from REALSQLDatabase -> SQLiteDatabase obviously isn't just a simple replace of the Class-name...?
    Because the AutoCommit behavior is the other way around - and I can't see how to change it back to the other behavior with SQLiteDatabase.

    That's what I have been hoping for.
    If not - then yes, I know how/what to look out for and how to modify it accordingly (should it really be necessary to ->have to<- use the AutoCommit=True).

  8. Norman P

    15 Mar 2019 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 8 months ago

    SQLite does that by default - yes

    Autocommit = FALSE is, if i recall correctly, only code in the plugin itself which is why there is no pragma etc to change it in SQLite
    ANd it just starts transactions for you instead of you doing it by hand (which I prefer)

    but the effect is just as I stated
    start a transaction when you connect and after every successful commit start another

    cant say I recall why autocommit didnt move to sqlitedatabase but its trivial to replace
    just subclass sqlitedatabase and override connect so it starts a transaction, and commit so that on success it starts another
    then use this custom subclass without changing the rest of your code

    done and done

  9. Jürg O

    15 Mar 2019 Pre-Release Testers, Xojo Pro Answer
    Edited 8 months ago

    Let's sum this up: AutoCommit can't be turned off with SQLiteDatabase.
    When moving from REALSQLDatabase to the newer SQLiteDatabase:
    One has to be aware the REALSQLDatabase used by default AutoCommit=False, whereas SQLiteDatabase is using SQLite default behavior AutoCommit=True.

    @Norman P Autocommit = FALSE is, if i recall correctly, only code in the plugin itself which is why there is no pragma etc to change it in SQLite
    And it just starts transactions for you instead of you doing it by hand

    If one has been explicitly using REALSQLDatabase.AutoCommit=True: just change to the new class.
    If not: you have to manually take care of the now different AutoCommit behavior.
    The simplest way is

    @Norman P just subclass sqlitedatabase and override connect so it starts a transaction, and commit so that on success it starts another
    then use this custom subclass without changing the rest of your code

    Without a custom subclass: Start a Transaction after Connect. Replace SQLExecuted COMMIT/ROLLBACK's to start a new Transaction. Take care of places where one has manually started a BEGIN TRANSACTION.

  10. Norman P

    15 Mar 2019 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 8 months ago

    That seems about right

    And in the process if you mov e up to 2018r4 you get sqlite 3.25.3 with sqlitedatabase where realsqldatabase is 3.7.14.1
    So lots of bug fixes etc just in sqlite itself

  11. Norman P

    15 Mar 2019 Pre-Release Testers, Xojo Pro great-white-software.com/blog

    and you could even mimic the setting for autocommit so that your subclass behaves just like the realsqldatabase

    makes you do even less work in the long run

  12. Tim S

    16 Mar 2019 Pre-Release Testers Canterbury, UK

    @Jürg O Let's sum this up: AutoCommit can't be turned off with SQLiteDatabase.
    When moving from REALSQLDatabase to the newer SQLiteDatabase:
    One has to be aware the REALSQLDatabase used by default AutoCommit=False, whereas SQLiteDatabase is using SQLite default behavior AutoCommit=True.

    If you issue an UPDATE or INSERT or any other SQL which modifies the database, then either:

    1) You hadn't preceded that with a BEGIN, in which case SQLite surrounds your single statement with an implied BEGIN/COMMIT.

    2) You had preceded that with a BEGIN, so your statement and any others don't take effect until you do a COMMIT.

    That's how it works. When you do such a statement, if you are not already in a transaction, SQLite starts one for you, and COMMITs it for you too.

or Sign Up to reply!