0

I am trying to ship an SQL file with my Android application and have the app use it. I have the file in the assets folder and I check for it and copy it if necessary when the app is created. I followed this blog post: http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

The sql file is in the assets directory when it is installed and it does perform write operations that seem correct, but the file doesn't appear anywhere on the filesystem.

Here's the code for my database helper:

package com.example.myapp;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper {
private static String DB_PATH;
private static String DB_NAME = "myapp.sql";
private SQLiteDatabase myDataBase;
private final Context myContext;

public DataBaseHelper(Context context){
    super(context, DB_NAME, null, 1);
    this.myContext = context;
    DB_PATH = myContext.getFilesDir().getPath();
}

public void createDatabase() throws IOException {
    boolean dbExist = checkDataBase();
    if (dbExist) {
        // do nothing, db exists
    } else {
        this.getReadableDatabase();

        try {
            copyDataBase();
        } catch (IOException e) {
            throw new Error("Error copying database");
        }
    }
}

private boolean checkDataBase() {
    SQLiteDatabase checkDB = null;

    try {
        String myPath = DB_PATH + "/" + DB_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    } catch (SQLiteException e) {
        // database doesn't exist yet
    }

    if (checkDB != null) {
        checkDB.close();
    }

    if (checkDB != null) {
        Log.e("myapp", "Database exists");
    }

    return checkDB != null ? true : false;
}

private void copyDataBase() throws IOException {

    //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;
    Log.e("myapp", outFileName);

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);
    BufferedOutputStream os = new BufferedOutputStream(myOutput);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
        Log.e("myapp", "writing buffer");
        os.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

}

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();

}

@Override
public void onCreate(SQLiteDatabase db) {

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}
}

And for my main activity:

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    DataBaseHelper dbHelper = new DataBaseHelper(this);

    try {
        dbHelper.createDatabase();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        //e.printStackTrace();
        Log.e("myapp", "ERROR in creating database");
    }

    try {
        dbHelper.openDataBase();
    } catch (SQLException sqle) {
        Log.e("myapp", "ERROR in opening database");
        throw sqle;
    }

    SQLiteDatabase db = dbHelper.getReadableDatabase();

    String sortOrder =
            MyApp.COLUMN_NAME_NAME + " DESC";

    Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
    if (c.moveToFirst()) {
        while (!c.isAfterLast()) {
            Log.e("myapp", c.getString(c.getColumnIndex("name")));
            c.moveToNext();
        }
    }
}
Charles
  • 50,943
  • 13
  • 104
  • 142
Cody Frazer
  • 23
  • 6
  • 1
    Please explain exactly what you mean by "but the file doesn't appear anywhere on the filesystem" How/where are you looking? Are you aware that you are not normally able to access internal storage files of an app, unless you have a device such as an emulator where adb runs as root, you use the run-as mechanism with a debug APK, or your app sets the file's mode to world readable? – Chris Stratton Jun 24 '13 at 18:03
  • I'm looking in /data/data/com.example.myapp/files/myapp.sql, as that is the path that `myContext.getFilesDir().getPath()` returns. However, those folders don't even exist. I see folders from other apps in /data/data, but no com.example.myapp folder. Also, the query that returns the table names only returns `android_metadata`. EDIT: I'm looking through my phone using ASTRO. I haven't rooted it, so would that be why I can't see it? I figured the missing tables confirmed that it's not there, but I'm super new and I really have no idea. Should I try running the app in the emulator? – Cody Frazer Jun 24 '13 at 18:23
  • Oh, I just tried running it in the emulator and that's so much more helpful. Maybe I can figure it out. – Cody Frazer Jun 24 '13 at 18:30
  • 1
    The emulator would change what ADB/DDMS can see, but not what Astro can see. Generally a browsing solution will have trouble finding an application's folders since /data itself is not browsable on a secured device. However, you can make individual files/folders owned by your app readable and listable. – Chris Stratton Jun 24 '13 at 18:36
  • That's very helpful. Thank you. I looked, and it appears the file is being created, but it's corrupt. My copy stuff must be buggy. I tried using DDMS to just copy the correct SQL file, and I'm still not getting my actual table. That's a different problem then. – Cody Frazer Jun 24 '13 at 18:43
  • 1
    It seems kind of odd that you are calling this.getReadableDatabase(); *before* you copy it from assets to "disk". Is that what you really want to do? – Chris Stratton Jun 24 '13 at 18:56
  • Oh, I got it!!! I just had to overwrite `getReadableDatabase()`. That makes so much sense now. It had no idea to return `myDatabase`. Now I just have to debug my file writing problem. Thanks so much for the assistance! – Cody Frazer Jun 24 '13 at 18:57
  • I probably don't really want to do that. I saw that and wondered why it was there (form the blog post), but I just chalked it up to me being so new to Android. Now that I'm a little more familiar, it seems completely unnecessary. – Cody Frazer Jun 24 '13 at 19:07

1 Answers1

0

I had to override the getReadableDatabase function for the helper class because it has no idea to return myDatabase. The helper loads it up properly and everything, but it never actually returns it.

@Override
public SQLiteDatabase getReadableDatabase(){
    return myDataBase;
}
Cody Frazer
  • 23
  • 6