Android Custom SQLite Build - cannot open database

Question

My goal is to build a custom version of SQLite (specifically with R-Tree enabled) to include in my Android project. The motivation stems from:

Android SQLite R-Tree - How to install module?

SQLite has some instructions in how to do this:

http://www.sqlite.org/android/doc/trunk/www/index.wiki

I have compiled SQLite and successfully included the shared library in my project. The problem I am having is that as soon as I call an operation such as getReadableDatabase() or getWriteableDatabase() I receive a SQLiteCantOpenDatabaseException.

It is important to note that I am using:

import org.sqlite.database.sqlite.SQLiteDatabase;

import org.sqlite.database.sqlite.SQLiteOpenHelper;

in place of:

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

In order to use the custom SQLite build through the NDK and JNI.

MySQLiteHelper:

public class MySQLiteHelper extends SQLiteOpenHelper {

    static {
        System.loadLibrary("sqliteX");
    }

    private static final String DATABASE_NAME = "mydata.db";
    private static final int DATABASE_VERSION = 1;
    Context myContext;

    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        myContext = context;
    }

    public void getEntry(){
        SQLiteDatabase db = this.getReadableDatabase();
        db.close();
    }

    public void createEntry(){
        // 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();
        //...
        //...
        db.close();
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
         String create_rtree = "CREATE VIRTUAL TABLE demo_index USING rtree(\n" +
                "   id,              -- Integer primary key\n" +
                "   minX, maxX,      -- Minimum and maximum X coordinate\n" +
                "   minY, maxY       -- Minimum and maximum Y coordinate\n" +
                ");";

        database.execSQL(create_rtree);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(MySQLiteHelper.class.getName(),
            "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + "demo_index");
        onCreate(db);
    }

}

Main Activity:

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    MySQLiteHelper helper = new MySQLiteHelper(this);
    helper.createEntry();
    setContentView(R.layout.activity_my);
}

Stack Trace:

10-29 13:45:38.356    2360-2360/com.example.nathanielwendt.lstrtree E/SQLiteLog﹕ (14)   os_unix.c:30589: (2) open(//arrrr.db) -
10-29 13:45:38.376    2360-2360/com.example.nathanielwendt.lstrtree E/SQLiteDatabase﹕ Failed to open database 'arrrr.db'.
    org.sqlite.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
        at org.sqlite.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
        at org.sqlite.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:217)
        at org.sqlite.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:201)
        at     org.sqlite.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:467)
        at org.sqlite.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:189)
        at org.sqlite.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:181)
        at org.sqlite.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:809)
        at org.sqlite.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:794)
        at org.sqlite.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:699)
        at org.sqlite.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:722)
        at org.sqlite.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:228)
        at org.sqlite.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:168)
        at com.example.nathanielwendt.lstrtree.MySQLiteHelper.createEntry(MySQLiteHelper.java:65)
        at com.example.nathanielwendt.lstrtree.MyActivity.onCreate(MyActivity.java:25)

I have seen several posts here on SO about this same exception, but they all seem to be related to importing the database from somewhere else on disk in which they do not set the file path correctly. This seems to be the most related post: Android SQLiteOpenHelper cannot open database file, but none of the suggestions worked.

I have tried this with 2 different phones and with the emulator and I receive the same error. Furthermore, I have replaced the imports discussed above with the default android sqlite imports and it works fine (given I don't try to create an R-Tree since it is not included in the default Android SQLite install). Changing permissions of database dir and .db file to 777 did not fix the issue, neither did adding WRITE_PERMISSION to my manifest.

What path are you actually trying to access? – Chris Stratton Oct 30 '14 at 02:22 — Chris Stratton, Oct 30 '14 at 02:22

score 8 · Accepted Answer · answered Oct 30 '14 at 09:53

This is an incompatibility in the SQLite Android bindings.

The original Android code opens the database like this:

db = mContext.openOrCreateDatabase(mName, mEnableWriteAheadLogging ?
        Context.MODE_ENABLE_WRITE_AHEAD_LOGGING : 0,
        mFactory, mErrorHandler);

while org.sqlite.database.sqlite.SQLiteOpenHelper does not use the context:

db = SQLiteDatabase.openOrCreateDatabase(
        mName, mFactory, mErrorHandler
);

This means that the database path is not prepended to the database name.

Use something like this to get the complete path of the database:

String path = context.getDatabasePath(DATABASE_NAME).getPath();

and use that as the database name.

Android Custom SQLite Build - cannot open database

1 Answers1

Linked