update method in sqlite didn't throw Exception?

Question

I'm getting information about items from the web and I want my app to check if certain rows exist. If they do exist then the app should update those rows, otherwise it should create new rows:

try {
    mDb.execSQL("UPDATE ["
    + ParseReciverAddList.tableName
    + "] SET purchased = " + pCheck
    + " WHERE id =" + (thisId));
    mDb.execSQL("UPDATE ["
    + ParseReciverAddList.tableName
    + "] SET name = '" + (PName)
    + "' WHERE id =" + (thisId));
    mDb.execSQL("UPDATE ["
    + ParseReciverAddList.tableName
    + "] SET quantity = '" + (pQuantity)
    + "' WHERE id =" + (thisId));
    Log.d("p", "e");
} catch (Exception e) {
    Log.d("p", "ne");
    mDb.execSQL("INSERT INTO ["+ParseReciverAddList.tableName+"] VALUES ("+thisId+","+pCheck+", '"+PName+"','"+pQuantity+"');");
}

I figure I'll try to update it and if it throws Exception then it means the rows don't exist and it will create it.

However, I'm running into a problem: even if the row doesn't exist it doesn't throw Exception. Any ideas as to why it's not triggering the catch clause when the rows already exist?

it wont trow exception , thats the problem . i want him to trow exception if row doesnt exist but he wont . — Jesus Dimrix, Mar 28 '13 at 13:54
Even if it did throw an exception, for the record, that's a terrible way to control execution logic (with exceptions). Just check if the data actually exists first so you'll know the state. Or you could look into the concept of "upsert" and "insert or replace" (though the latter can be tricky, make sure you know what you're doing or you'll clobber data. See this: http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace — Charlie Collins, Mar 28 '13 at 14:12

score 1 · Accepted Answer · answered Mar 28 '13 at 14:14

However, I'm running into a problem: even if the row doesn't exist it doesn't throw Exception. Any ideas as to why it's not triggering the catch clause when the rows already exist?

Reason is that execSQL() throws SQLException only in the case if your SQL String is invalid. But your problem is that if row doesn't exist simply UPDATE statement won't be applied but it does not mean that SQL String is incorrect.

Solution is to use for example API method update() that returns number of rows affected and if it will return 0 simply perform INSERT.

Pseudocode:

int rowsAffected = db.update("table", values, "id = ?", 
                   new String[] {String.valueOf(thisId)});
if (rowsAffected == 0) {
   // perform insert
}

update method in sqlite didn't throw Exception?

1 Answers1