Android: SQLite - insertWithOnConflict

Question

I call insertWithOnConflict using SQLiteDatabase.CONFLICT_IGNORE. However, if a conflict occurs, returns "-1" instead of the identifier of the existing string. How to fix it?

Creating a table:

EDIT:

String CREATE_CATEGORY_TABLE = "CREATE TABLE "+TABLE_CATEGORY+"(" + BaseColumns._ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+ KEY_CATEGORY_NAME+" TEXT UNIQUE" + ")"; db.execSQL(CREATE_CATEGORY_TABLE); 

Insert instruction:

 ContentValues values = new ContentValues(); values.put(KEY_CATEGORY_NAME, name); int catID = (int) db.insertWithOnConflict(TABLE_CATEGORY, null, values, SQLiteDatabase.CONFLICT_IGNORE); 

Answer 1

insertWithOnConflict, using SQLiteDatabase.CONFLICT_IGNORE, does not work as expected, and it should probably be avoided altogether on this issue:
https://code.google.com/p/android/issues/detail?id=13045

Answer 2

Android expects the primary key column to be called _id . May be the reason: since the column does not exist, it cannot return a value and returns -1.

Answer 3

Comment SQLiteDatabase.CONFLICT_IGNORE should function just like you. When trying to insert, if there is no conflicting row, then it will insert a new row with the given values ​​and return the identifier of the newly inserted row. On the other hand, if there is already a conflicting string (with the same unique key), then the incoming values ​​will be ignored and the existing string will be saved, and then the return value will be -1 to indicate a conflicting script. For details and to understand how to handle this return value, read Android / SQLite: Insert-Update columns to save identifier

Answer 4

The workaround I used uses insertOrThrow instead of insertWithOnConflict and explicitly catches SQLiteConstraintException . You need to explicitly catch it because it inherits from RuntimeException .

I had to use this workaround because insert dumped the database schema into logs when there was a conflict, and that was unacceptable.

Answer 5

 try { long id = db.insertWithOnConflict(StatusData.TABLE, null, values, SQLiteDatabase.CONFLICT_IGNORE); if (id == -1){ throw new RuntimeException(String.format("%s: Failed to insert [%s] to [%s] for unkwon reason.",TAG,values,uri)); }else return ContentUris.withAppendedId(uri, id); } 

I can still get the row inserted into the database, CONFLICT_IGNORE is not working properly @Tom is right, but I'm not sure if this problem has been fixed.