if me.Error then
System.DebugLog “ERROR: Could not drop table”
me.DisplayError( true )
else
me.SQLExecute(“BEGIN TRANSACTION”)
me.SQLExecute( “VACUUM” )
me.Commit[/code]
The table does get dropped. The DB file however remains the same size. Checking the file data manually shows the deleted table is still there. Calling VACUUM has absolutely no effect.
I also have this pragma already set in the DB constructor:
me.SQLExecute "PRAGMA auto_vacuum = 1"
The DB file is never vacuumed. The deleted data never goes away.
This is a 64-bit app but I also tested with 32-bit compiling and the result is the same. It doesn’t work.
as to VACUUM ,… I don’t believe it is allowed to be inside a Transaction… check the error message there as well
With database operations you can rarely have too many error checks
and to being “same size”… it could be the OS filesystem… not SQLite… VACUUM optimizes the database internally but may not necessarily change the physical file size.
Vacuum doens’t care about the size of the “garbage collection”…
but I don’t think it actually changes the file area used by the OS, meaning you might not “notice” a filesize reduction
My project on my SQLite db file works fine and I have a size (down) change the original user deleted some Records (probably) with Xojo2014r4, running in the IDE (I cannot test the Standalone).
[quote=426939:@Emile Schwarz]@Aaron Hunt Xojo2018r4, Mac. In the code below, “me” is an SQLiteDatabase object.
How can this be ? Isnt me be a reserved word ?[/quote]
I understand the OP as that the code mentioned is in a subclass of SQLiteDatabase. Me is the same as self and referring to the instance if used outside of a control.
but you should get that in the ErrorMessage as well.[/quote]
The problem was indeed that VACUUM can’t be done within a transaction. Although, there was no error code. Calling VACUUM without the transaction works finally.
But the pragma simply seems not to work. Setting it = 1 should vacuum automatically after a table is deleted, but this doesn’t happen, so the pragma appears to have absolutely no effect.
Autovacuum will only work if one of the vacuum modes was set before creating the table. You can then change between mode 1 and 2 on the fly, but you cannot activate auto_vacuum for a table that was created with the default = off.