title: Cannot attach database within transaction
body: <p>I am working on an application in which I have to work with two databases. I want to add records from tables of new database to tables of old database, I have put new database on asset folder of my project and I am able to query with new database as well as old database.</p>
  
  <p>The problem is when I am trying to attach new database to old database on <code>onUpgrade()</code> method, an error is occurred that is <strong>cannot attach database within transaction</strong>.</p>
  
  <p>I found lots of links having this issue and I have tried many of them but did not succeeded. I am new to <code>SQLite</code> and Android, please tell me how can I attach the database with another in code. </p>
  
  <p><strong>Database Helper Class:</strong> DatabaseHelper.java</p>
  
  <pre><code>public class DatabaseHelper extends SQLiteOpenHelper {
  Context context;
  SQLiteDatabase sourceDatabase;
  private String TAG = this.getClass().getSimpleName();
  
  private static final String DATABASE_NAME = "namesdemo_db";
  private static final String SOURCE_DB_NAME = "namesdemo_db_s";
  private static final int DATABASE_VERSION = 23;
  
  private String DATABASE_PATH;
  File dbFile;
  
  // TABLE NAMES
  private static final String TABLE_EMPLOYEE = "employee";
  private static final String TABLE_FAVORITE = "fevorite";
  
  // KEYS of Table Employee
  private static final String KEY_ID = "id";
  private static final String KEY_NAME = "name";
  private static final String KEY_ADDRESS = "address";
  
  // Keys of Table Fevorite
  private static final String KEY_FEV_ID = "fev_id";
  // private static final String KEY_ID = "id";
  
  private static final String CREATE_TBL_EMPLOYEE = "CREATE TABLE " + TABLE_EMPLOYEE + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_NAME
          + " TEXT," + KEY_ADDRESS + " TEXT)";
  private static final String CREATE_TBL_FEVORITE = "CREATE TABLE " + TABLE_FAVORITE + "(" + KEY_FEV_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_ID
          + " INTEGER )";
  
  public DatabaseHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
      // TODO Auto-generated constructor stub
      this.context = context;
      DATABASE_PATH = "/data/data/" + context.getApplicationContext().getPackageName() + "/databases/";
  
      Log.v(TAG, " DB Path " + DATABASE_PATH);
  }
  
  @Override
  public void onCreate(SQLiteDatabase db) {
      // TODO Auto-generated method stub
  
      Log.v(TAG, CREATE_TBL_EMPLOYEE);
  
      db.execSQL(CREATE_TBL_EMPLOYEE);
  
      db.execSQL(CREATE_TBL_FEVORITE);
  
      Log.v(TAG, " OnCreate Executed");
  
  }
  
  private void copyDataBase(String dbPath) {
      try {
          InputStream assestDB = context.getAssets().open(SOURCE_DB_NAME);
  
          OutputStream appDB = new FileOutputStream(dbPath + SOURCE_DB_NAME, false);
  
          byte[] buffer = new byte[1024];
          int length;
          while ((length = assestDB.read(buffer)) &gt; 0) {
              appDB.write(buffer, 0, length);
          }
  
          appDB.flush();
          appDB.close();
          assestDB.close();
      } catch (IOException e) {
          e.printStackTrace();
      }
  
      Log.v(TAG, " copyDataBase() called");
  
  }
  
  public long createName(String name, String address) {
      long count = 0;
      SQLiteDatabase db = getWritableDatabase();
      ContentValues values = new ContentValues();
      values.put(KEY_NAME, name);
      values.put(KEY_ADDRESS, address);
      count = db.insert(TABLE_EMPLOYEE, null, values);
      db.close();
      Log.v(TAG, +count + " row(s) inserted!");
      return count;
  }
  
  public long createFevorite(int id) {
      long count = 0;
      SQLiteDatabase db = getWritableDatabase();
      ContentValues values = new ContentValues();
      values.put(KEY_ID, id);
      count = db.insert(TABLE_FAVORITE, null, values);
      db.close();
      Log.v(TAG, +count + " row(s) inserted in " + TABLE_FAVORITE);
      return count;
  }
  
  public List&lt;Employee&gt; getAllNames() {
      List&lt;Employee&gt; nameList = new ArrayList&lt;Employee&gt;();
      String query = "Select * from " + TABLE_EMPLOYEE;
  
      SQLiteDatabase db = getReadableDatabase();
      Cursor c = db.rawQuery(query, null);
      if (c.moveToFirst()) {
          do {
              Employee e = new Employee();
              e.setID(c.getInt(c.getColumnIndex(KEY_ID)));
              e.setName(c.getString(c.getColumnIndex(KEY_NAME)));
              e.setAddress(c.getString(c.getColumnIndex(KEY_ADDRESS)));
              nameList.add(e);
  
          } while (c.moveToNext());
      }
      return nameList;
  }
  
  public List&lt;Employee&gt; getAllNames(SQLiteDatabase db) {
      List&lt;Employee&gt; nameList = new ArrayList&lt;Employee&gt;();
      String query = "Select * from " + TABLE_EMPLOYEE;
  
      Cursor c = db.rawQuery(query, null);
      if (c.moveToFirst()) {
          do {
              Employee e = new Employee();
              e.setID(c.getInt(c.getColumnIndex(KEY_ID)));
              e.setName(c.getString(c.getColumnIndex(KEY_NAME)));
              e.setAddress(c.getString(c.getColumnIndex(KEY_ADDRESS)));
              nameList.add(e);
  
              Log.v(TAG, "ID: " + e.getID());
              Log.v(TAG, "NAme: " + e.getName());
              Log.v(TAG, "Address" + e.getAddress());
  
          } while (c.moveToNext());
      }
  
      return nameList;
  }
  
  private boolean checkDatabase() {
      // SQLiteDatabase checkdb = null;
      boolean checkdb = false;
      try {
          String myPath = DATABASE_PATH + SOURCE_DB_NAME;
          File dbfile = new File(myPath);
          // checkdb =
          // SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE);
          checkdb = dbfile.exists();
      } catch (SQLiteException e) {
          System.out.println("Database doesn't exist");
      }
      return checkdb;
  }
  
  public void openDatabase() throws SQLException {
      String path = DATABASE_PATH + "namesdemo_db_s";
      sourceDatabase = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);
      Log.v(TAG, " openDatabase() called ");
  }
  
  public synchronized void closeDatabase() {
      if (sourceDatabase != null) {
          sourceDatabase.close();
          Log.v(TAG, " closeDatabase() called ");
      }
      super.close();
  }
  
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      // TODO Auto-generated method stub
  
      String drop_tbl = "DROP TABLE " + TABLE_EMPLOYEE;
      String attach_db = "ATTACH DATABASE '" + SOURCE_DB_NAME + "' AS 'temp_db'";
      String create_table = "CREATE TABLE " + TABLE_EMPLOYEE + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_NAME + " TEXT," + KEY_ADDRESS
              + " TEXT)";
      String insert = "INSERT INTO " + TABLE_EMPLOYEE + "(" + KEY_ID + "," + KEY_NAME + "," + KEY_ADDRESS + ") SELECT * FROM temp_db." + TABLE_EMPLOYEE;
  
      boolean dbExists = checkDatabase();
      if (dbExists) {
  
          Log.v(TAG, " DB  exists");
  
          // getAllNames(sourceDatabase);
      } else {
          Log.v(TAG, " DB does not exists");
          copyDataBase(DATABASE_PATH);
          // openDatabase();
          // getAllNames(sourceDatabase);
      }
      db.execSQL(attach_db);
      openDatabase();
      //db.endTransaction();
      db.execSQL(drop_tbl);
      db.execSQL(create_table);
  
      db.execSQL(insert);
  
      Log.v(TAG, " onUpgrade() Executed");
  
      db.close();
      sourceDatabase.close();
  
  }
  </code></pre>
  
  <p>}</p>