Executing multiple statements with SQLiteDatabase.

Question

I've followed a standard tutorial for building a database with Android. I've created a class called DbHelper which extends SQLiteOpenHelper. I've Overridden the create handler to execute a string.

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(DbDefinitions.DB_CREATE);
}

DbDefinitions.DB_CREATE is a static String I've created.

public static final String TABLE_MESSAGES = "messages";
public static final String TABLE_FRIENDS = "friends";

public static final String STATE_OK = "STATE_OK";

public static final String DB_CREATE = 
    "create table " + TABLE_MESSAGES + " (_id integer primary key, user_id integer not null, created_on integer, subject text not null, summary text not null, messagetext text null, read integer not null, status text not null default '" + STATE_OK + "'); " +
    "create table " + TABLE_FRIENDS + " (_id integer primary key, user_id integer not null, friend_id integer not null, created_on integer, status text not null default '" + STATE_OK + "');";

I'd like to use 1 String to execute multiple SQL statements. How can I do this as SQLiteDatabase.execSQL only allows 1 statement?

Answer 1

That's not possible to do using the standard methods which comes with Android. So, if you want to execute batch of multiple SQL statements, you will have to create your own utility to do so. For instance, you can have something like this:

public void executeBatchSql(String sql){
    // use something like StringTokenizer to separate sql statements
    for each sql statement{
        database.execSQL(oneStatement);
    }
}

Though, what I'd do is something like this:

String sql1 = "create bla bla bla;";
String sql2 = "create foo bar;";
String[] statements = new String[]{sql1, sql2};

// then
for(String sql : statements){
    database.execSQL(sql);
}

Answer 2

we have many greate answers here. and here is my solution for multiple insert statements, however I used a file in assets not in row, each line in this file is an insert statement.

    try {
        in = getAssets().open("InsertStatemets.SQL");
        BufferedReader reader = new BufferedReader(new InputStreamReader(in));
        line = reader.readLine();

        while (line != null){
            db.execSQL(line);
            Log.e("Insert Statement",  line); 
        }

    } catch (Exception e) {

    }

Answer 3

Try something like this:

    try {
        InputStream is = this.context.getAssets().open("script.sql");
        BufferedReader reader = new BufferedReader(new InputStreamReader(is));
        String line;
        while ((line = reader.readLine()) != null) {
            Log.i("SQL Script", line);
            if (!line.isEmpty() && !line.trim().startsWith("--"))
                db.execSQL(line);
        }
    } catch (IOException e) {
        Log.e("SQL Script", e.getMessage());
    }
    Log.i("SQL Script", "script executed");

Answer 4

From the documentation of SQLiteDatabase and my experience in the past I think that it's not possible. But why don't you just split it up in single statements? It really is not a problem in your example. Or do you need it for a different use case?

Answer 5

Well, in my case, I am excuting queries from a file which I saved as an asset This is the solution I used+-

String script = readAsset(CREATE_SCRIPT);//readAsset is a method i use to get the file contents
try {
     String[] queries = script.split(";");
 for(String query : queries){
        db.execSQL(query);
     }
 } catch (Exception e) {
    .....

EDIT

In my case, the Queries were simple insert queries which I had full control over. However, the issue has been raised concerning queries with ";" inside them.

@TWiStErRob suggests using

script.split(";$");// $ meaning end of line. You will need to use RegexOption.MULTILINE for this to work

or

script.split(";\n");// but you will need to ensure that each query is on a different line