sqlite - attach database

android, database, sqlite

Solution

@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.

Problem

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

Original source