Executing multiple statements with SQLiteDatabase.execSQL

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?

Cristian · Accepted Answer · 2010-09-27 17:03:20Z

45

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

answered Sep 27, 2010 at 17:03

Cristian

200k63 gold badges360 silver badges265 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rui Campos Over a year ago

what if the ';' is within a text field?

Phantômaxx Over a year ago

The ; at the end of the SQL statements is completely useless.

frostymarvelous · Accepted Answer · 2016-10-20 09:10:53Z

20

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

edited Oct 20, 2016 at 9:10

answered Jan 25, 2012 at 10:54

frostymarvelous

2,82534 silver badges43 bronze badges

5 Comments

jon Over a year ago

i'm new to Java and Android development but your solution may fail on the line script.split() if your stored statements contains a semicolon as part of a text value (e.g. on the default value of a column definition)

frostymarvelous Over a year ago

You are definitely right about that. I don't think it is a java or android thing, but rather a regex problem. I am no regex guru so I can't give you the exact thing, but using a regex which checks if the semi-colon is at the end of the statement should work. This works for me as my script is mainly to create the various tables.

Seraphim's Over a year ago

@frostymarvelous Ops.. sorry!

TWiStErRob Over a year ago

@jon @Seraphim @frostymarvelous script.split(";$") would do, $ meaning end of line, or if you're not a regex fan you can use script.split(";\n"), but then you have to make sure it's a file/string with unix line endings! Though parsing SQL strings is not a good idea (some well-known frameworks do it, and sadly got it wrong). Without a real parser (ANTLR) there's always an exception to work around.

madhead Over a year ago

It's necessary to add that in order for ;$ to work, you should use RegexOption.MULTILINE option for your regex.

Douglas Nassif Roma Junior · Accepted Answer · 2014-12-01 16:53:53Z

1

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

edited Dec 1, 2014 at 16:53

answered Dec 1, 2014 at 16:16

Douglas Nassif Roma Junior

2,1223 gold badges28 silver badges55 bronze badges

Comments

mreichelt · Accepted Answer · 2010-09-27 16:58:45Z

0

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?

answered Sep 27, 2010 at 16:58

mreichelt

12.5k7 gold badges60 silver badges72 bronze badges

Comments

Eng. Samer T · Accepted Answer · 2014-09-25 23:38:16Z

0

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) {

    }

answered Sep 25, 2014 at 23:38

Eng. Samer T

6,5666 gold badges39 silver badges45 bronze badges

Collectives™ on Stack Overflow

Executing multiple statements with SQLiteDatabase.execSQL

5 Answers 5

2 Comments

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related