SQLiteDatabase.execSQL(query) - Multiple SQL commands inside the onCreate() method not working

Question

I am new to android. I haven't worked with SQLite DBs before.
I think this is a very basic question, but I am unable to find the solution.

The code is here (assume declarations)

public void onCreate(SQLiteDatabase db) {

    String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + FORMULA + "("
            + CAT_CD + " TEXT ," + S_CAT_CD + " TEXT, PRIMARY 
 KEY(CAT_CD,S_CAT_CD))";
    db.execSQL(CREATE_CATEGORIES_TABLE);

    String CREATE_CAT_DESC_TABLE="CREATE TABLE "+ FORMULA_CAT_DESC + "
("+CAT_CD+" TEXT PRIMARY KEY, "+ DESC +" TEXT ) ";
    db.execSQL(CREATE_CAT_DESC_TABLE);

    String CREATE_CURRENCY_TABLE="CREATE TABLE "+ VI_CURRENCY + "("+ 
CURRENCY_CD +" TEXT PRIMARY KEY, "+ CURRENCY_SIGN +" TEXT ) ";
    db.execSQL(CREATE_CURRENCY_TABLE);

   String query=  "INSERT INTO "+ VI_CURRENCY +" ("+CURRENCY_CD +", 
"+CURRENCY_SIGN+ ") VALUES " +
            "('INR', '₹'), " +
            " ('USD','$') " +
            "('JPY','¥') ";
    db.execSQL(query);
 }

The first three commands execute successfully, while by executing the insert command, SQLite throws an exeption.

Use LogCat to examine the Java stack trace associated with your crash: https://stackoverflow.com/questions/23353173/unfortunately-myapp-has-stopped-how-can-i-solve-this — CommonsWare, Jun 05 '17 at 17:21
You are trying to insert multiple rows by running a single SQL command. — Phantômaxx, Jun 05 '17 at 17:26

fdelafuente · Accepted Answer · 2017-06-05T17:44:59.627

1

You could create a sql statement for each record

String query1 =  "INSERT INTO " + VI_CURRENCY + " (" + CURRENCY_CD + ", " + CURRENCY_SIGN + ") VALUES " + "('INR', '₹')" ;

String query2 =  "INSERT INTO " + VI_CURRENCY + " (" + CURRENCY_CD + ", " + CURRENCY_SIGN + ") VALUES " + "('USD','$')" ;

String query3 =  "INSERT INTO " + VI_CURRENCY + " (" + CURRENCY_CD + ", " + CURRENCY_SIGN + ") VALUES " + "('JPY','¥')" ;

db.execSQL(query1);
db.execSQL(query2);
db.execSQL(query3);

Or insert multiple values in one statement, with the correct sintax it would be like this:

String query = "INSERT INTO " + VI_CURRENCY + " (" + CURRENCY_CD + ", " + CURRENCY_SIGN + ") VALUES " +
             "('INR', '₹'), " +
             "('USD','$'), " +
             "('JPY','¥')";
db.execSQL(query);

For more information visit SqlLite

edited Jun 05 '17 at 17:44

answered Jun 05 '17 at 17:29

fdelafuente

1,114
1
13
24

I want to insert hundreds of records So should I write hundreds of queries? I think this is not a good solution. – vinod wakade Jun 05 '17 at 17:34
I updated the answer, you had a syntax error – fdelafuente Jun 05 '17 at 17:45
Ohh..thank you. Now Query is working successfully. – vinod wakade Jun 05 '17 at 17:57

score 1 · Answer 2 · answered Jun 05 '17 at 18:02

In Android, writing operation is slow, So in your case, if you have hundreds of data, you can use transactions in SQLite database.

db.beginTransaction();
try {
    for(/*your loop*/) {
        String query =  /*your query*/

        db.execSQL(query);
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

For more detail, Please refer this, official doc.

score 0 · Answer 3 · answered Jun 05 '17 at 17:51

If you want to insert multiple records, then you should take data in hashmap object in key value pair. Use iterator on the same and insert data in table.

        Map<String,String> myMap = new HashMap<>();
        myMap.put("INR","₹");
        myMap.put("USD","$");
        myMap.put("JPY","¥");


 Iterator it = myMap.entrySet().iterator();
    while (it.hasNext()) {
      Map.Entry pair = (Map.Entry)it.next();
      String query1 =  "INSERT INTO " + VI_CURRENCY + " (" + CURRENCY_CD + ", " + CURRENCY_SIGN + ") VALUES " + "("+ pair.getKey()+ "," + pair.getvalue()+)" ;
    db.execSQL(query1);
    it.remove(); // avoids a ConcurrentModificationException
}

SQLiteDatabase.execSQL(query) - Multiple SQL commands inside the onCreate() method not working

3 Answers3