execSQL not working with insert

Question

i know that documentation says that it's better in this case to use insert instead of execSQL, but i have a very big sql-file that consist of 6000 records and it's easier for me to use execSQL, but it's not working

public class CitiesHandler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "cities";

    // Login table name
    private static final String TABLE_REGION= "region";
    private static final String TABLE_CITY= "city";
    // Login Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_REGION_ID = "region_id";
    private static final String KEY_PHONE = "phone_code";

    public CitiesHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String strRegion="CREATE TABLE region (id INTEGER PRIMARY KEY,name TEXT)";
        db.execSQL(strRegion);
        String insertRegion="INSERT INTO region Values   (1, 'АР Крым') , (2, 'Винницкая область') , (3, 'Волынская область') , (4, 'Днепропетровская область') , (5, 'Донецкая область') , (6, 'Житомирская область') , (7, 'Закарпатская область') , (8, 'Запорожская область') , (9, 'Ивано-Франковская область') , (10, 'Киевская область') , (11, 'Кировоградская область') , (12, 'Луганская область') , (13, 'Львовская область') , (14, 'Николаевская область') , (15, 'Одесская область') , (16, 'Полтавская область') , (17, 'Ровенская область') , (18, 'Сумская область') , (19, 'Тернопольская область') , (20, 'Харьковская область') , (21, 'Херсонская область') , (22, 'Хмельницкая область') , (23, 'Черкасская область') , (24, 'Черниговская область') , (25, 'Черновицкая область')";
        db.execSQL(insertRegion);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CITY);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_REGION);
        // Create tables again
        onCreate(db);
    }

    public String[] getRegions(){
        HashMap<String,String> user = new HashMap<String,String>();
        String selectQuery = "SELECT  * FROM " + TABLE_REGION;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if(cursor.getCount() > 0){
            user.put("name", cursor.getString(1));
        }
        cursor.close();
        db.close();
        String [] regions;
        regions=new String[user.values().size()];
        for (Entry<String, String> entry : user.entrySet()) {
            int i=0;
            regions[i] = entry.getValue();      
            i++;
        }
        // return user
        return regions;
    }

    public int getRegionID(String name){
        HashMap<String,String> user = new HashMap<String,String>();
        String selectQuery = "SELECT  * FROM " + TABLE_REGION+" WHERE name="+name;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if(cursor.getCount() > 0){
            user.put("id", cursor.getString(0));
        }
        cursor.close();
        db.close();
        // return user
        return Integer.valueOf(user.get("id"));
    }

    public String[] getCities(int region_id){
        HashMap<String,String> user = new HashMap<String,String>();
        String selectQuery = "SELECT  * FROM " + TABLE_CITY+" WHERE region_id="+String.valueOf(region_id);

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if(cursor.getCount() > 0){
            user.put("name", cursor.getString(1));
        }
        cursor.close();
        db.close();
        String [] cities;
        cities=new String[user.values().size()];
        for (Entry<String, String> entry : user.entrySet()) {
            int i=0;
            cities[i] = entry.getValue();       
            i++;
        }
        // return user
        return cities;
    }

}

thanks for any help

it's not working - it means everytime it's empty. insert statement is not fulfilled.

Why not use the SQliteDatabase basic query methods... instead of using rawQuery since you aren't doing very complicated queries... — JoxTraex, Dec 08 '13 at 22:09
possible duplicate of [Is it possible to insert multiple rows at a time in an SQLite database?](http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database) — CL., Dec 09 '13 at 08:55

score 3 · Answer 1 · answered Dec 08 '13 at 21:33

3

I would still use Android's provided insert(). It seems from your code that you are not passing arguments correctly to the VALUES SQLite command. See here for an INSERT INTO explanation.

answered Dec 08 '13 at 21:33

Emmanuel

13,083
4
39
53

Agree. Here's an [example using SQLite insert within DB transaction](http://android.codota.com/scenarios/528e1e21da0ae9404ad3ab53/android.database.sqlite.SQLiteDatabase?tag=bumblebee) – drorw Dec 08 '13 at 21:58
i would do that but i have big sql-statement and don't want to parse it – Jenya Kirmiza Dec 09 '13 at 09:16
It is not a statement that is that long. I do not know what you mean by parsing, but using the `insert()` is a better choice still. – Emmanuel Dec 09 '13 at 14:34

score 2 · Accepted Answer · answered Dec 08 '13 at 22:07

2

I always use execSQL between beginTransaction and EndTransaction, you tried?

Here is an excerpt of code:

String cmd = "QUERY FOR EXECUTE";
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.beginTransaction();

if (cmd.trim().length() > 0) {
    db.execSQL(cmd);
}

db.setTransactionSuccessful();
db.endTransaction();
db.close();

Make sure you have write permissions too.

answered Dec 08 '13 at 22:07

Ramon Vicente

761
8
12

`onCreate()` is already inside a transaction. – laalto Dec 09 '13 at 09:15

execSQL not working with insert

2 Answers2