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.
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.
[quote=428662:@Norman Palardy]Any where you set autocommit = false put in a start transaction since somewhere later you do call Commit
Correct ?[/quote]
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”)?
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).
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
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.
[quote=428688:@Norman Palardy]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[/quote]
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
[quote=428688:@Norman Palardy]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[/quote]
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.
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
[quote=428692:@Jürg Otter]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.[/quote]
If you issue an UPDATE or INSERT or any other SQL which modifies the database, then either:
You hadn’t preceded that with a BEGIN, in which case SQLite surrounds your single statement with an implied BEGIN/COMMIT.
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.
