0

To upsert a record I check in database wether record already exist. If not INSERT ootherwise UPDATE is performed. But sometimes fetching record goes wrong, and even record exist it does not find any. Why? How can it happen?

DB is modified only in this method.

11-04 13:08:34.542 20181-20248/com.j4nos.moviebuffs W/System.err: android.database.sqlite.SQLiteConstraintException: column _id is not unique (code 19)
11-04 13:08:34.548 20181-20248/com.j4nos.moviebuffs W/System.err:     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
11-04 13:08:34.550 20181-20248/com.j4nos.moviebuffs W/System.err:     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:779)
11-04 13:08:34.551 20181-20248/com.j4nos.moviebuffs W/System.err:     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
11-04 13:08:34.553 20181-20248/com.j4nos.moviebuffs W/System.err:     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
11-04 13:08:34.554 20181-20248/com.j4nos.moviebuffs W/System.err:     at com.j4nos.moviebuffs.Utility$2.run(Utility.java:360)
11-04 13:08:34.556 20181-20248/com.j4nos.moviebuffs W/System.err:     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
11-04 13:08:34.557 20181-20248/com.j4nos.moviebuffs W/System.err:     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
11-04 13:08:34.558 20181-20248/com.j4nos.moviebuffs W/System.err:     at java.lang.Thread.run(Thread.java:838)

public static ExecutorService serialQueue = Executors.newFixedThreadPool(1); 

public static void upsert(final JSONObject record) {

        Runnable runnable = new Runnable() {
            public synchronized void run() {

                try {
                    String recordType = record.getString("recordType");
                    String id = record.getString("_id");

                    Boolean recordExist = true;
                    Cursor cursor3 = Utility.db.rawQuery("SELECT * FROM " + recordType + " WHERE _id = '" + id + "'", null);
                    if (cursor3.getCount() == 0) {
                        recordExist = false;
                    }
                //.....
            }
        };


    Utility.serialQueue.execute(runnable);
}
János
  • 27,206
  • 24
  • 130
  • 270
  • Or you could simply take a look at this http://stackoverflow.com/questions/690632/how-do-i-update-a-row-in-a-table-or-insert-it-if-it-doesnt-exist For your problem, using Runnable might be the problem. Are you reading a JSonArray ? SO multiple inserts ? Meaning multiple thread maybe ? – AxelH Nov 04 '16 at 12:18
  • And did you synchronize anything ? If two threads tried to upsert the same id, you might run into problem if both check if it exist, then try to insert in the same time. – AxelH Nov 04 '16 at 12:28
  • `upsert` can be called from different threads, but code is executed on a serial queue. All DB operation are performed only on `serialQueue`. So theoretically no sync problem could arise. Do you agree @AxelH? – János Nov 04 '16 at 12:29
  • Since you use a fixed thread pool of size 1, it should not. But to be sure, I would synchronize the block (from the select query to the insert or update). Just to see if this is the cause – AxelH Nov 04 '16 at 12:58
  • `and even record exist it does not find any` Are you sure that your `_id` column is a **TEXT** field? Because you are trying to compare it to a **String** (`" WHERE _id = '" + id + "'"`). – Phantômaxx Nov 04 '16 at 13:34
  • Hey @Rotwang! `_id` is `VARCHAR`: `CREATE TABLE Rating(_id VARCHAR PRIMARY KEY, item VARCHAR, userSetting VARCHAR, scale BOOLEAN)` – János Nov 04 '16 at 13:39
  • @AxelH I put in `synchronized` still same phenomenon. Tried do a DELETE and then an INSERT. Same phenomenon. – János Nov 04 '16 at 13:44
  • Could you add the insert query ? – AxelH Nov 04 '16 at 13:55
  • I think I have and idea ... what is the length of _id VARCHAR ? Is it possible that you have some truncated values here ? Meaning that you search for `foobar` but only insert `fooba` because the column is only 5 character. – AxelH Nov 04 '16 at 14:19

0 Answers0