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

• PositiveLogic: Single quotes around tempDb (in ATTACH statement) should not be there. Single quotes in SQLite are primarily for delimiting string literals. Statement should take the form ATTACH ' drive/path/database_name' AS database_alias;
• CL.: What is the value of DB_PATH?
• user1071762: @CL. DB_PATH is path of database generated within the application where the databases are defaultly stored. In my case it is /data/data/com.app.name/databases/WineDB.sqlite
• user1071762: @PositiveLogic I also tried it without the quotes. It does not make any difference.
• CL.: Check if that file exists.

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. enter image description here

• Riccardo Leschiutta: This doesn't work, because calling "getReadableDatabase()", will automatically call "onUpgrade()". So how to execute the attach command BEFORE onUpgrade() been called?
• franny zhao: hmmm... I guess sqlite don't want you to do attach before onUpgrade()...
• franny zhao: I figure out the way to attach before onUpgrade(), see the updated answer~
• shirley: Why you detach the database right after attach the database?
• franny zhao: In my project, the attached database is useless later, so I detach it. You can do whatever suits your project.