ATTACH sqlite database in Android with SQLiteOpenHelper

Question

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?

You may need to either skip SQLiteOpenHelper or fork it. In the latter case, either you would remove the standard transaction that wraps onCreate() (handling it yourself) or otherwise give yourself a chance to run the ATTACH outside of any transaction bounds. — CommonsWare
– CommonsWare, Commented Nov 21, 2013 at 23:53

PearsonArtPhoto · Accepted Answer · 2014-02-01 18:26:13Z

3

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();

edited Feb 1, 2014 at 18:26

answered Nov 22, 2013 at 0:48

PearsonArtPhoto

39.9k17 gold badges116 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Phil Over a year ago

Were you able to then interact with the attached database? I am doing this as well, but when I reference the attached database, I get a no such table error (for example: SELECT * FROM names.mytable.

PearsonArtPhoto Over a year ago

I ended up rejecting this in favor of an entirely different way of managing this. Still, I'm coming back to it, I'll let you know if it works...

Ashish Tiwari Over a year ago

It is not working. I am using this code on "onUpgrade()" and I am getting this error "Cannot perform this operation because there is no current transaction". If I don't use "db.setTransactionSuccessful()", "db.endTransaction()", "db.beginTransaction()" then this error appears "cannot attach database within transaction"

Nimrod Dayan Over a year ago

This solution doesn't work and it confuses people. You should uncheck it as the accepted answer.

user1071762 · Accepted Answer · 2013-12-03 21:50:34Z

0

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.

answered Dec 3, 2013 at 21:50

user1071762

6851 gold badge10 silver badges15 bronze badges

1 Comment

Ashish Tiwari Over a year ago

I have same issue in onUpgrade() method, I have tried many solutions but not succeeded. Please elaborate.

Collectives™ on Stack Overflow

ATTACH sqlite database in Android with SQLiteOpenHelper

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related