pragma auto_vacuum does nothing, calling VACUUM also does nothing

Xojo2018r4, Mac. In the code below, “me” is an SQLiteDatabase object.

[code]me.SQLExecute(“BEGIN TRANSACTION”)
me.SQLExecute( "DROP TABLE "+TableName )
me.Commit

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.

I’ve missed something, or this is a bug?

for the drop what is the ERROR MESSAGE?

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.

isn’t the vacuum effective only after a certain size of the table you drop ?

From the Docs:

but you should get that in the ErrorMessage as well.

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

http://www.sqlitetutorial.net/sqlite-vacuum/

How can this be ? Isn’t me be a reserved word ?

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).

My code is in a PushButton and Xojo does not like that (Me = New SQLiteDatabase) at all.

Code moved to a Window Method share the same trouble(s)…
“----------------” to a Module share the same trouble(s).

Also, Me is blue.

[quote=426939:@Emile Schwarz]@Aaron Hunt Xojo2018r4, Mac. In the code below, “me” is an SQLiteDatabase object.
How can this be ? Isn’t 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.

It’s a subclassed SQLiteDatabase object.

[quote=426937:@Tobias Bussmann]From the Docs:

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.

Thank you all for the help.

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.

EDIT: Only found a German site giving explanations, but maybe a translator app will help a bit:
https://code.i-harness.com/de/docs/sqlite/pragma#pragma_auto_vacuum

Thanks, that explains it.

[quote=426954:@Ulrich Bogun]Only found a German site giving explanations, but maybe a translator app will help a bit:
https://code.i-harness.com/de/docs/sqlite/pragma#pragma_auto_vacuum[/quote]

German is fine. I speak German. I live in Germany.

I had also read the SQLite documentation (in English) but I had overlooked the facts stated there that would have answered my questions.