sqlite - attach database

Question

I followed some of the information I found on web about attaching a sqlite database for the purpose of copying a table from one sqlite db to another but I can't seem to get it working. I try to attach the database with this code:

DB_PATH = context.getDatabasePath("WineDB.sqlite").toString();
SQLiteDatabase backupDatabase = backupDBHandler.getWritableDatabase();
backupDatabase.execSQL("ATTACH '" + DB_PATH + "' AS 'tempDb'");

Up until now it runs without an error. Then I try to create a new table in the backup database by copying it from the tempDb:

sqlDB.execSQL("CREATE TABLE my_Producent AS SELECT * FROM tempDb.my_Producent");

And it crashes with an error "no such table "tempDb.my_Producent" However I am sure the table exists in the database "WineDB.sqlite". I create it in onCreate method which is called before I attach the database to backupDatabase.

Thanks for any help Cheers user2302510

Answer 1

@Phil. Yes I've got it working. However I don't really remember what the real issue was any more, so I'll just write a simplified sequence of steps I found in my code regarding copying from firstDB to secondDB. I execute the following operations in a SQLiteOpenHelper for the firstDB.

public class firstDBHandler extends SQLiteOpenHelper {

    SQLiteDatabase firstDB;
    //FIRST_DB_NAME is something like 'xxx1.sqlite'
    private static String FIRST_DB_PATH = context.getDatabasePath(FIRST_DB_NAME).toString();

    public void copyFromFirstToSecond(String secondDBName, int secondDBVersion) {
        //use the same context as for the firstDBHandler
        secondDBHandler = new SecondDBHandler(myContext, secondDBName, secondDBVersion);
        //here you create the secondDB if it does not exist yet
        try {
            secondDBHandler.createDataBase();
        } catch (IOException e) {
            e.printStackTrace();
        }
        SQLiteDatabase secondDB = secondDBHandler.getWritableDatabase();

        //note there are single quotation marks around FIRST_DB_PATH but none around tempDb
        secondDB.execSQL("ATTACH DATABASE '" + FIRST_DB_PATH + "' AS tempDb");

        //here you start to copy the tables from firstDB first by checking if the table exists in secondDB (secondDB is now the 'main' one, tempDB is the attached firstDB
        secondDB.execSQL("DROP TABLE IF EXISTS main." + SECOND_DB_TABLE_NAME);

        //here you create a table as a copy of
        secondDB.execSQL("CREATE TABLE main." + SECOND_DB_TABLE_NAME + " AS SELECT * FROM tempDb." + FIRST_DB_TABLE_NAME);

        //you can run the last two lines of code as many times as you need to copy all of the tables

        //after you have copied all of them, you need to detach the tempDB
        secondDB.execSQL("DETACH tempDb"); 
    }

}

public class SecondDBHandler extends SQLiteOpenHelper {

     //SECOND_DB_NAME is something like 'xxx2.sqlite'
    private static String SECOND_DB_PATH = context.getDatabasePath(SECOND_DB_NAME).toString();
     public void createDataBase() throws IOException {

          boolean dbExist = checkDataBase();
          if (dbExist) {
              // do nothing - database already exist
          } else {
              //This calls onCreate method of this SecondDBHandler, where you 
              //create tables that you need initially (but not those which you
              //intent to copy from firstDB)
              this.getReadableDatabase();
              this.close();
          }
    }

    public boolean checkDataBase() {
        File file = new File(SECOND_DB_PATH);
        return file.exists();
    }    
}

then you just call:

FirstDBHandler firstDBHandler = new FirstDBHandler(getBaseContext(), FIRST_DB_NAME, FIRST_DB_VERSION);
firstDBHandler.getReadableDatabase();
firstDBHandler.copyFromFirstToSecond(SECOND_DB_NAME, SECOND_DB_VERSION);

Of course there are onCreate methods missing for both SQLiteOpenHandler's but it is up to an application what is done there.

Hopefully the answer contains no errors. I have not tested it myself, just extracted a simplified version from my more complex code. If it has a few upvotes I will mark it as an answer.

Answer 2

I figure out 2 ways to attach database.

1. do NOT "attach database" in onCreate and onUpgrade. It will fail because of "Cannot attach database within transaction". But you can attach database when you init database, then call getReadableDatabase() to make sure database will be created and upgraded. Then you can run the attach command.

   sqLiteHelper = new SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION); // new SQLiteOpenHelper
   SQLiteDatabase db = sqLiteHelper.getReadableDatabase(); // make sure the database will be created
   db.execSQL("ATTACH DATABASE '/databases/xx.db' AS xx;"); // do attach database
   

2. But if you want to do attach before onUpgrade(), try this way:

endTransaction in onCreate(), then do attach, then beginTransaction

    public void onCreate(SQLiteDatabase db) {
        this.createTables(db); // do the normal onCreate() stuff

        db.setTransactionSuccessful();
        db.endTransaction();  // end transaction, so that you can do attach

        db.execSQL("ATTACH DATABASE '/databases/xx.db' AS xx;"); // do attach database
        db.execSQL("DETACH DATABASE xx;"); // detach database

        db.beginTransaction(); // begin transation again
    }

Reason:

read the function of SQLiteOpenHelper.java, I find out that onCreate() and onUpgrade() are called together during transaction.