2

I have a database of information which I am trying to copy from assets into the application's database directory on the device. I have taken code from 1, but moved the copying code into the onCreate method of the DatabaseHelper class (as it calls it). The database copies without error, but then when I try to access data from it, I get an SQLiteException thrown, saying that the table does not exist. When I open the newly created database file up on my computer I can see that it is the correct size, but doesn't contain any table except from 'android_metadata'. If I manually copy the uncorrupted database file from my computer into the app's database directory then it functions properly, but obviously I can't just do this.

Here is the code that I use to open the database:

public ReferenceDatabaseAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getReadableDatabase();
        return this;
    }

Here is the onCreate method:

        public void onCreate( SQLiteDatabase db )
        {
            Log.d("AutoBagger","onCreate called for reference database");
            //Open your local db as the input stream
            InputStream assetsDbFile;
            try {
                assetsDbFile = dHCtx.getAssets().open("reference.sqlite");
                // Path to the just created empty db
                String dbFile = "/data/data/" + dHCtx.getPackageName() + "/databases/"+DATABASE_NAME;

                //Open the empty db as the output stream
                OutputStream outputStream = new FileOutputStream(dbFile);

                //transfer bytes from the assetsDbFile to the dbFile
                byte[] buffer = new byte[1024];
                int length;
                while ((length = assetsDbFile.read(buffer))>0){
                outputStream.write(buffer, 0, length);
                }

                //Close the streams
                outputStream.flush();
                outputStream.close();
                assetsDbFile.close();
                Log.d("AutoBagger","No errors whilst copying database");
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                Log.d("AutoBagger","IOException encountered when copying database from assets");
            }
        }

On the first run I just get the error about the table not existing, butwhen I run it again (when the database has already been copied over) I get an SQLiteDatabaseCorruptionException "database disk image is malformed". Any ideas where I'm going wrong? Thanks.

LogCat output when database not already copied:

>07-08 17:16:41.490: D/AutoBagger(5145): DatabaseHelper constructor called (finished)
>07-08 17:16:41.530: D/AutoBagger(5145): onCreate called for reference database
>07-08 17:16:41.550: D/AutoBagger(5145): No errors whilst copying database
>07-08 17:16:41.550: D/AutoBagger(5145): fetchRows called
>07-08 17:16:41.550: I/Database(5145): sqlite returned: error code = 1, msg = no such table: hills
>07-08 17:16:41.550: D/AndroidRuntime(5145): Shutting down VM
>07-08 17:16:41.550: W/dalvikvm(5145): threadid=1: thread exiting with uncaught exception (group=0x402a7560)
>07-08 17:16:41.570: E/AndroidRuntime(5145): FATAL EXCEPTION: main
>07-08 17:16:41.570: E/AndroidRuntime(5145): android.database.sqlite.SQLiteException: no such table: hills: , while compiling: SELECT classification FROM hills WHERE dobihId = 1
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1356)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1235)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1189)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1271)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at com.s0812532.AutoBagger.ReferenceDatabaseAdapter.fetchRows(ReferenceDatabaseAdapter.java:196)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at com.s0812532.AutoBagger.Hill.isMunro(Hill.java:172)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at com.s0812532.AutoBagger.TestLauncher$12.onClick(TestLauncher.java:157)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.view.View.performClick(View.java:2501)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.view.View$PerformClick.run(View.java:9107)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.os.Handler.handleCallback(Handler.java:587)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.os.Handler.dispatchMessage(Handler.java:92)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.os.Looper.loop(Looper.java:130)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at android.app.ActivityThread.main(ActivityThread.java:3835)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at java.lang.reflect.Method.invokeNative(Native Method)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at java.lang.reflect.Method.invoke(Method.java:507)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:847)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:605)
>07-08 17:16:41.570: E/AndroidRuntime(5145):    at dalvik.system.NativeStart.main(Native Method)
>07-08 17:16:43.181: I/Process(5145): Sending signal. PID: 5145 SIG: 9

LogCat output when database has already been copied:

>07-08 17:11:43.669: D/AutoBagger(5136): DatabaseHelper constructor called (finished)
>07-08 17:11:43.669: I/Database(5136): sqlite returned: error code = 11, msg = database corruption at line 46139 of [42537b6056]
>07-08 17:11:43.669: I/Database(5136): sqlite returned: error code = 11, msg = statement aborts at 5: [SELECT locale FROM android_metadata LIMIT 1] 
>07-08 17:11:43.669: E/Database(5136): SELECT locale FROM android_metadata failed
>07-08 17:11:43.679: E/Database(5136): Failed to setLocale() when constructing, closing the database
>07-08 17:11:43.679: E/Database(5136): android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed
>07-08 17:11:43.679: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.native_setLocale(Native Method)
>07-08 17:11:43.679: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.setLocale(SQLiteDatabase.java:1987)
>07-08 17:11:43.679: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1855)
>07-08 17:11:43.679: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:820)
>07-08 17:11:43.679: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:854)
>07-08 17:11:43.679: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:847)
>07-08 17:11:43.679: E/Database(5136):  at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:572)
>07-08 17:11:43.679: E/Database(5136):  at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:203)
>07-08 17:11:43.679: E/Database(5136):  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:118)
>07-08 17:11:43.679: E/Database(5136):  at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
>07-08 17:11:43.679: E/Database(5136):  at com.s0812532.AutoBagger.ReferenceDatabaseAdapter.open(ReferenceDatabaseAdapter.java:145)
>07-08 17:11:43.679: E/Database(5136):  at com.s0812532.AutoBagger.Hill.<init>(Hill.java:55)
>07-08 17:11:43.679: E/Database(5136):  at com.s0812532.AutoBagger.TestLauncher$12.onClick(TestLauncher.java:156)
>07-08 17:11:43.679: E/Database(5136):  at android.view.View.performClick(View.java:2501)
>07-08 17:11:43.679: E/Database(5136):  at android.view.View$PerformClick.run(View.java:9107)
>07-08 17:11:43.679: E/Database(5136):  at android.os.Handler.handleCallback(Handler.java:587)
>07-08 17:11:43.679: E/Database(5136):  at android.os.Handler.dispatchMessage(Handler.java:92)
>07-08 17:11:43.679: E/Database(5136):  at android.os.Looper.loop(Looper.java:130)
>07-08 17:11:43.679: E/Database(5136):  at android.app.ActivityThread.main(ActivityThread.java:3835)
>07-08 17:11:43.679: E/Database(5136):  at java.lang.reflect.Method.invokeNative(Native Method)
>07-08 17:11:43.679: E/Database(5136):  at java.lang.reflect.Method.invoke(Method.java:507)
>07-08 17:11:43.679: E/Database(5136):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:847)
>07-08 17:11:43.679: E/Database(5136):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:605)
>07-08 17:11:43.679: E/Database(5136):  at dalvik.system.NativeStart.main(Native Method)
>07-08 17:11:43.689: E/Database(5136): Deleting and re-creating corrupt database /data/data/com.s0812532.AutoBagger/databases/reference.sqlite
>07-08 17:11:43.689: E/Database(5136): android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed
>07-08 17:11:43.689: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.native_setLocale(Native Method)
>07-08 17:11:43.689: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.setLocale(SQLiteDatabase.java:1987)
>07-08 17:11:43.689: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1855)
>07-08 17:11:43.689: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:820)
>07-08 17:11:43.689: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:854)
>07-08 17:11:43.689: E/Database(5136):  at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:847)
>07-08 17:11:43.689: E/Database(5136):  at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:572)
>07-08 17:11:43.689: E/Database(5136):  at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:203)
>07-08 17:11:43.689: E/Database(5136):  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:118)
>07-08 17:11:43.689: E/Database(5136):  at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
>07-08 17:11:43.689: E/Database(5136):  at com.s0812532.AutoBagger.ReferenceDatabaseAdapter.open(ReferenceDatabaseAdapter.java:145)
>07-08 17:11:43.689: E/Database(5136):  at com.s0812532.AutoBagger.Hill.<init>(Hill.java:55)
>07-08 17:11:43.689: E/Database(5136):  at com.s0812532.AutoBagger.TestLauncher$12.onClick(TestLauncher.java:156)
>07-08 17:11:43.689: E/Database(5136):  at android.view.View.performClick(View.java:2501)
>07-08 17:11:43.689: E/Database(5136):  at android.view.View$PerformClick.run(View.java:9107)
>07-08 17:11:43.689: E/Database(5136):  at android.os.Handler.handleCallback(Handler.java:587)
>07-08 17:11:43.689: E/Database(5136):  at android.os.Handler.dispatchMessage(Handler.java:92)
>07-08 17:11:43.689: E/Database(5136):  at android.os.Looper.loop(Looper.java:130)
>07-08 17:11:43.689: E/Database(5136):  at android.app.ActivityThread.main(ActivityThread.java:3835)
>07-08 17:11:43.689: E/Database(5136):  at java.lang.reflect.Method.invokeNative(Native Method)
>07-08 17:11:43.689: E/Database(5136):  at java.lang.reflect.Method.invoke(Method.java:507)
>07-08 17:11:43.689: E/Database(5136):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:847)
>07-08 17:11:43.689: E/Database(5136):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:605)
>07-08 17:11:43.689: E/Database(5136):  at dalvik.system.NativeStart.main(Native Method)
>07-08 17:11:43.709: D/AutoBagger(5136): onCreate called for reference database
>07-08 17:11:43.719: D/AutoBagger(5136): No errors whilst copying database
>07-08 17:11:43.729: D/AutoBagger(5136): fetchRows called
>07-08 17:11:43.729: I/Database(5136): sqlite returned: error code = 1, msg = no such table: hills
>07-08 17:11:43.729: D/AndroidRuntime(5136): Shutting down VM
>07-08 17:11:43.729: W/dalvikvm(5136): threadid=1: thread exiting with uncaught exception (group=0x402a7560)
>07-08 17:11:43.739: E/AndroidRuntime(5136): FATAL EXCEPTION: main
>07-08 17:11:43.739: E/AndroidRuntime(5136): android.database.sqlite.SQLiteException: no such table: hills: , while compiling: SELECT classification FROM hills WHERE dobihId = 1
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1356)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1235)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1189)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1271)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at com.s0812532.AutoBagger.ReferenceDatabaseAdapter.fetchRows(ReferenceDatabaseAdapter.java:196)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at com.s0812532.AutoBagger.Hill.isMunro(Hill.java:172)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at com.s0812532.AutoBagger.TestLauncher$12.onClick(TestLauncher.java:157)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.view.View.performClick(View.java:2501)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.view.View$PerformClick.run(View.java:9107)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.os.Handler.handleCallback(Handler.java:587)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.os.Handler.dispatchMessage(Handler.java:92)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.os.Looper.loop(Looper.java:130)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at android.app.ActivityThread.main(ActivityThread.java:3835)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at java.lang.reflect.Method.invokeNative(Native Method)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at java.lang.reflect.Method.invoke(Method.java:507)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:847)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:605)
>07-08 17:11:43.739: E/AndroidRuntime(5136):    at dalvik.system.NativeStart.main(Native Method)
>07-08 17:11:45.461: I/Process(5136): Sending signal. PID: 5136 SIG: 9
ablack89
  • 25
  • 1
  • 5
  • try the code here: http://stackoverflow.com/questions/9109438/using-already-created-database-with-android/9109728#9109728 – Yaqub Ahmad Jul 08 '12 at 16:30
  • when you get the error.. then close the app..and run it again.. If it run then I will tell you the issue – Mohsin Naeem Jul 08 '12 at 16:40
  • It doesn't run properly the second time, because when the a corrupted database is detected it deletes it and re-creates as blank, so the correct table is no present and you get the associated errors. – ablack89 Jul 08 '12 at 19:09

1 Answers1

0

Try this pattern:

public class  DatabaseHelper extends SQLiteOpenHelper{
    private static String PKG_NAME = "name_of_package_for_apk";
    private static String DB_PATH = "/data/data/" + PKG_NAME + "/databases/";
    private static String DB_NAME = "db.db3";

    private SQLiteDatabase myDataBase; 
    private final Context myContext;
    DatabaseHelper(Context context) 
    {
       super(context, DB_NAME, null, 1);
       this.myContext = context;
    }
    @Override
    public void onCreate(SQLiteDatabase db) { }
    public void createDataBase() throws IOException{
       boolean dbExist = checkDataBase();
       if (!dbExist){
          getReadableDatabase();
          copyDatabase();
       }else{
          this.close();
       }
    }

    private boolean checkDataBase(){
       SQLiteDatabase checkDB = null;
       try{
           String myPath = DB_PATH + DB_NAME;
           checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY|SQLiteDatabase.NO_LOCALIZED_COLLATORS);
       }catch(SQLiteException e){
          //database does't exist yet.
          Log.v(TAG, "[checkDataBase] - Database does not exist... YET!");
       }finally{
          if(checkDB != null) checkDB.close();
       }
       return (checkDB != null) ? true : false;
    }
    private void copyDataBase() throws IOException{
        boolean bCopyOk = false;
        try{
            //Open your local db as the input stream
            InputStream myInput = myContext.getAssets().open(DB_NAME);
            // Path to the just created empty db
            String outFileName = DB_PATH + DB_NAME;
            //Open the empty db as the output stream
            OutputStream myOutput = new FileOutputStream(outFileName);
            //transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[1024];
            int length;
            while ((length = myInput.read(buffer))>0) myOutput.write(buffer, 0, length);
            //Close the streams
            myOutput.flush();
            myOutput.close();
            myInput.close();
            bCopyOk = true;
        }catch(Exception ex){
        }finally{
            if (bCopyOk) Log.v(TAG, "[copyDataBase] - Database copied OK!");
        }
    }
    @SuppressWarnings("unused")
    public void openDataBase() throws SQLException{
        //Open the database
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }
    @Override
    public synchronized void close() {
        if(myDataBase != null) myDataBase.close();
        super.close();
    }
}

Simply, do this:

dbHelper = new DatabaseHelper(getApplicationContext());
dbHelper.createDatabase();

Edit: It transpires that the metadata is missing this vital entry, by using the Sqliteman's GUI front end, and using this script 

CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US')

The clue was in the logcat itself:

>07-08 17:11:43.669: E/Database(5136): SELECT locale FROM android_metadata failed
>07-08 17:11:43.679: E/Database(5136): Failed to setLocale() when constructing, closing the database
>07-08 17:11:43.679: E/Database(5136): android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed
t0mm13b
  • 34,087
  • 8
  • 78
  • 110
  • Thanks for the answer, but I have changed my code to fit the pattern you've provided, but the database is still being corrupted, and no errors are given during the copying of the database itself. Is there anything else that it could be? Thanks. – ablack89 Jul 08 '12 at 19:07
  • The database that I am copying already has an 'android_metadata' table in it (and works fine when copied across manually), so I assumed that this part of the log was just because the first thing the system tried to query from the database was the metadata, and that was the only reason it was mentioned in the error? – ablack89 Jul 08 '12 at 19:40
  • no... read that error `setLocale()` in the answer in which I extracted the portion of your logcat from, have you it set to `en_US`? – t0mm13b Jul 08 '12 at 19:41
  • Can you describe your metadata table and its values please? use sqliteman GUI or the sqlite3 command line tool to do this. – t0mm13b Jul 08 '12 at 19:42
  • the android_metadata table has one column "locale", with one value in it "en_GB". – ablack89 Jul 08 '12 at 19:44
  • I've tried putting in a breakpoint at the end of the copyDatabase method, and even at that point, before I have tried to open the database again on the device I have copied the database file to my computer and opened it with SQLiteMan and there is only the android_metadata table in it, despite it being the same size as the original database with all the data in it (27kb). After I try to open the database again through the app the file then shrinks to 3kb. – ablack89 Jul 08 '12 at 19:49
  • its locale-related, default locale is en_US which is what I've used even though my android code is running on handset with en_GB set. Have you tried changing it to en_US? think its en_GB conflicting with `SQLiteDatabase.NO_LOCALIZED_COLLATORS` in the `SQLiteDatabase.openDatabase` method. AFAIK, Android's database handler shrinks databases each time its used iirc, vacummed/packed. – t0mm13b Jul 08 '12 at 20:00
  • Infuriating. The problem wasn't with the code at all - somehow the database in the assets folder was corrupted itself when I opened it up from the APK using SQLiteMan, I have re-added the file and now it works. Thanks very much for your help. – ablack89 Jul 08 '12 at 20:13