android, how to exec a sql file in sqlitedatabase

I wrote this one especially for you <3

I used the same filename as you “/raw/food_db.sql” but that lead to Errors instead I had to call it “/raw/food_db”. I guess its because you don’t use filenames in your code, but ResourceIds which are written like “R.raw.food_db” and the dot is confusing the system.

There is a method for within your DbSource… assuming somewhere there is code like this:

private SQLiteDatabase db;
...
DbHelper dbHelper = new DbHelper(context);
this.db = dbHelper.getWritableDatabase();

You put this method in there:

/**
 * This reads a file from the given Resource-Id and calls every line of it as a SQL-Statement
 * 
 * @param context
 *  
 * @param resourceId
 *  e.g. R.raw.food_db
 * 
 * @return Number of SQL-Statements run
 * @throws IOException
 */
public int insertFromFile(Context context, int resourceId) throws IOException {
    // Reseting Counter
    int result = 0;

    // Open the resource
    InputStream insertsStream = context.getResources().openRawResource(resourceId);
    BufferedReader insertReader = new BufferedReader(new InputStreamReader(insertsStream));

    // Iterate through lines (assuming each insert has its own line and theres no other stuff)
    while (insertReader.ready()) {
        String insertStmt = insertReader.readLine();
        db.execSQL(insertStmt);
        result++;
    }
    insertReader.close();

    // returning number of inserted rows
    return result;
}

Call it like this (I tried from an Activity, so that Toasts can output messages). Look closely, the errors are “Toasted” as well.

try {
        int insertCount = database.insertFromFile(this, R.raw.food_db);
        Toast.makeText(this, "Rows loaded from file= " + insertCount, Toast.LENGTH_SHORT).show();
    } catch (IOException e) {
        Toast.makeText(this, e.toString(), Toast.LENGTH_SHORT).show();
        e.printStackTrace();
    }

Enjoy!

Oh.. btw: This code is meant for a file in which each insert-Statement has its own line.