3

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?

PearsonArtPhoto
  • 38,970
  • 17
  • 111
  • 142
  • 1
    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 Nov 21 '13 at 23:53

2 Answers2

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();
PearsonArtPhoto
  • 38,970
  • 17
  • 111
  • 142
  • 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`. – Phil Jun 05 '14 at 04:19
  • 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... – PearsonArtPhoto Jun 05 '14 at 11:11
  • 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" – Ashish Tiwari Jan 28 '15 at 12:59
  • This solution doesn't work and it confuses people. You should uncheck it as the accepted answer. – Nimrod Dayan Jul 29 '16 at 07:57
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.

user1071762
  • 625
  • 1
  • 9
  • 14