Problem saving an Sqlite database to disk

dghoskins

Reposting as I had the wrong tag.

As far as I can tell, Objo doesn’t provide a wrapper for the SQLite Online Backup API the way Xojo does (SQLiteDatabase.BackUp). A common way for saving an in-memory database is:

sql: VACUUM main INTO 'path\filename.db';

In Objo, ExecuteSQL runs this without raising an error, but no file is created. I wrote a small test program that:

Creates an in-memory database, adds a table, and inserts some data
Runs the VACUUM INTO command
Checks whether the file exists. It does not.
Closes the database
Creates a file-based database, adds a table, and inserts some data
Runs the same VACUUM INTO command to back it up
Checks whether the file exists. The backup file is created.

So VACUUM INTO works from a file-based database but not from an in-memory one. I then converted the test program to Xojo, and there the in-memory database saved to disk without any problem.

I’m not sure why VACUUM INTO fails silently for in-memory databases in Objo. Has anyone else run into this or found a workaround?

Also, would it be possible to add a wrapper for the SQLite Online Backup API? That would give a clean, supported way to persist in-memory databases and do live backups.

The attached test program has hard coded file names in the root of my D:\ drive.

vacuum-test.zip

4kB

Garry

Thanks for the test project. I’ve reproduced this and logged it as an Objo bug.

The problem is in how Objo opens an in-memory SQLite connection. One of the connection settings also causes the VACUUM INTO destination to be created in memory. SQLite reports success, but the backup never reaches disk. That explains both the missing file and the lack of an exception.

I’ll correct the connection setup so your original command works as expected. In the meantime, try explicitly requesting disk storage in the destination URI:

imDB.ExecuteSQL("VACUUM main INTO 'file:///D:/imdbbackup.db3?mode=rwc';")

I verified the equivalent workaround on macOS, including reopening the backup and reading its data. As with your original command, the destination must either not exist or be an empty file.

Garry

I’ve fixed this for the next update. Your original VACUUM main INTO command will save the in-memory database to disk correctly, without needing the URI workaround.

Thanks again for the test project.