TAGS :Viewed: 7 - Published at: a few seconds ago

[ ATTACH sqlite database in Android with SQLiteOpenHelper ]

I'm in the process of creating a database that references keys in another database. Essentially, I have an import of data that I want to keep separate from one that will change, but if possible, I would like to reference the other keys via a FOREIGN KEY. So far as I know, I need to attach the database first to make that happen. Here's teh relevant code so far:

public class LogDatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "log.db";
    private static final int DATABASE_VERSION = 1;
    private String namesDb;

    public LogDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        namesDb=getNamesDbPath();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("ATTACH DATABASE ? AS names",new String[]{namesDb});
        StringBuilder query=new StringBuilder();
        query.append("CREATE TABLE log (");
    }
}

However, this doesn't seem to work, as I'm getting the following error:

11-21 17:35:58.176: E/SQLiteLog(9984): (1) statement aborts at 5: [ATTACH DATABASE ? AS names] cannot ATTACH database within transaction
11-21 17:35:58.176: D/AndroidRuntime(9984): Shutting down VM
11-21 17:35:58.176: W/dalvikvm(9984): threadid=1: thread exiting with uncaught exception (group=0x41a21700)
11-21 17:35:58.191: E/AndroidRuntime(9984): FATAL EXCEPTION: main
11-21 17:35:58.191: E/AndroidRuntime(9984): android.database.sqlite.SQLiteException: cannot ATTACH database within transaction (code 1)
11-21 17:35:58.191: E/AndroidRuntime(9984):     at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
11-21 17:35:58.191: E/AndroidRuntime(9984):     at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:734)
11-21 17:35:58.191: E/AndroidRuntime(9984):     at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
11-21 17:35:58.191: E/AndroidRuntime(9984):     at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
11-21 17:35:58.191: E/AndroidRuntime(9984):     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1674)
11-21 17:35:58.191: E/AndroidRuntime(9984):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1603)
11-21 17:35:58.191: E/AndroidRuntime(9984):     at com.kd7uiy.hamfinder.LogDatabaseHelper.onCreate(LogDatabaseHelper.java:27)
11-21 17:35:58.191: E/AndroidRuntime(9984):     at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
11-21 17:35:58.191: E/AndroidRuntime(9984):     at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)

How can I make this work?

Answer 1


I figured out a way to make it work, that I'm not particularly proud of, but it does the job. If you can't be in a transaction when attaching a database, then end the transaction first.

db.setTransactionSuccessful();
db.endTransaction();
db.execSQL("ATTACH DATABASE ? AS names",new String[]{namesDb});
db.beginTransaction();

Answer 2


I had the same problem when I wanted to attach database while updating database and what I did was just change a static global variable when onUpdate() was called and right after this.getReadableDatabase() or this.getWritableDatabase() (which are actually the functions that call the onCreate/onUpdate method) I check the value of the global variable and run the whole code containing attach function. This way you don't do the attaching inside onCreate() or onUpgrade() method, but just after executing them.

I am not sure if this approach is usable in the case specified in question but I just thought I would mention how I solved it if someone else bumps into this problem as well.