android, how to exec a sql file in sqlitedatabase

Question

i have "food_db.sql" file stored in /res/raw folder, it has tons of 'insert' in it.

my question is how to i exec the file and get the data into sqlite databse in my android app?

here is the database code of mine. any sugguestions?

private static class DbHelper extends SQLiteOpenHelper{

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_NAME + " TEXT NOT NULL, " + 
                KEY_HOTNESS + " TEXT NOT NULL);");
                    // how do i exec the sql file and get the data into this DB table?
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE);
        db.execSQL("DROP TABLE IF EXISTS" + RECORD_TABLE);
        onCreate(db);
    }

}

Answer 1

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.

Answer 2

@Override
public void onCreate(SQLiteDatabase db) {
    InputStream is = null;
    try {
        is = context.getResources().openRawResource(R.raw.database_init);
        String initSql = IOUtils.toString(is);
        db.execSQL(initSql);
    } catch (IOException e) {
        Log.e(TAG, "Error loading init SQL from raw", e);
    } catch (SQLException e) {
        Log.e(TAG, "Error executing init SQL", e);
    } finally {
        IOUtils.closeQuietly(is);
    }
}

An important callout is, SQLiteDatabase.execSQL(String sql) only execute a single SQL statement, multiple statements separated by semicolons are not supported.

I've tried a hard time to find out why my database_init.sql doesn't work as expected.

Answer 3

Insert all line in one string:

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)
String insertStmt = "";
while (insertReader.ready()) {
    insertStmt += insertReader.readLine();
    result++;
}
insertReader.close();

db.execSQL(insertStmt);

// returning number of inserted rows
return result;
}

Answer 4

The best way is parsing sql file, split file into statement list.

It not hard cause we only need to take care of commit, string and escape, but it not easy too write one in short time.

I found an easy way to archive this by replacing comment string by using regex, from SugarORM source code(https://github.com/chennaione/sugar/blob/master/library/src/main/java/com/orm/util/MigrationFileParser.java)

public MigrationFileParser(String content){
    this.content = content.replaceAll("(\\/\\*([\\s\\S]*?)\\*\\/)|(--(.)*)|(\n)","");
}

public String[] getStatements(){
    return this.content.split(";");
}

This way has limitation(we can't use "/*", "--" in sql string), but will be ok in most case.

hope this help