VACUUM results in "Unable to open database file"

Question

I'm getting the following error in Spatialite, when I'm trying to add new files to my database.

10-05 11:50:01.408: E/GDH(1545): jsqlite.Exception: unable to open database file
10-05 11:50:01.408: E/GDH(1545):    at jsqlite.Database._exec(Native Method)
10-05 11:50:01.408: E/GDH(1545):    at jsqlite.Database.exec(Database.java:177)
10-05 11:50:01.408: E/GDH(1545):    at com.kd7uiy.hamfinder.database.GeoDataHelper.addDb(GeoDataHelper.java:153)

This code is run in a separate thread from the UI thread, and executes the following code, with the line 153 marked from GeoDataHelper.addDb.

Database db = this.getWritableDatabase();
TableResult result=new TableResult(); //Filler, isn't really needed, but...
db.exec("ATTACH DATABASE \"%q\" AS newDb",result,new String[]{path});
db.exec("REPLACE INTO counties(name,cntyidfp,geometry) "
        + "SELECT name,cntyidfp,geometry FROM newDb.counties",result);
db.exec("VACUUM",result);
db.exec("DETACH DATABASE newDb",result);

The code that opens the database includes this:

Database db = new Database();
db.open(mDatabasePath + "/" + mName,
        jsqlite.Constants.SQLITE_OPEN_READWRITE);
// set parameters
db.exec("PRAGMA journal_mode = PERSIST;", null);
db.exec("PRAGMA temp_store = FILE;", null);
db.exec("PRAGMA temp_store_directory = \"" + mDatabasePath+"/"+mName + "\";", null);

I don't understand how the first two calls are valid, but the VACUUM is not. Any ideas?

Note that [the `temp_store_directory` is depreciated.](http://sqlite.org/pragma.html#pragma_temp_store_directory) — Colonel Thirty Two, Oct 05 '14 at 18:16

score 1 · Answer 1 · answered Oct 05 '14 at 12:07

As I was writing the answer to this, I realized one important thing. VACUUM depends on having a temporary directory available. Code I found from the net uses a temporary directory, but not the correct one, which is the cache directory. Changing the code to this resolved the issue:

Database db = new Database();
db.open(mDatabasePath + "/" + mName,
        jsqlite.Constants.SQLITE_OPEN_READWRITE);
// set parameters
db.exec("PRAGMA journal_mode = PERSIST;", null);
db.exec("PRAGMA temp_store = FILE;", null);
db.get_table("PRAGMA temp_store_directory = \"%q\";", new String[]{mContext.getCacheDir().toString()});

VACUUM results in "Unable to open database file"

1 Answers1