Sqlite - attach database

Question

I was following some information I found on the Internet about sqlite database binding in order to copy a table from one sqlite db to another, but I can't get it to work. I am trying to attach a database using this code:

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

Until now, it has been working without errors. Then I try to create a new table in the backup database by copying it from tempDb:

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

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

Thanks for any help cheers user2302510

Answer 1

@Phil. Yes, it works for me. However, I don’t remember what the real problem was, so I’ll just write a simplified sequence of steps that I found in my code regarding copying from firstDB to secondDB . I perform the following operations in SQLiteOpenHelper for 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 that are missing for both SQLiteOpenHandler and the application, which is done there.

I hope the answer does not contain errors. I did not test it myself, I just extracted a simplified version from my more complex code. If he has several upvotes, I mark this as an answer.

Answer 2

I find two ways to connect the database.

• DO NOT "attach database" in onCreate and onUpgrade . It fails due to "Cannot bind database to transaction." But you can attach the database when initializing the database, and then call getReadableDatabase() to make sure the database is created and updated. 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 

• But if you want to connect before onUpgrade () , try as follows:

endTransaction in onCreate (), then 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 } 

Cause:

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