Юрій Мазуревич Юрій Мазуревич - 4 months ago 14
Android Question

Syntax exception while trying to delete a list of objects from the database in Android

Error

android.database.sqlite.SQLiteException: near ",": syntax error (code 1): , while compiling: DELETE FROM messages_table WHERE (time,recepient_id,sender_id) IN (("1469945224502","8c79c7d4bf2c11e38867e83935244df8","0a0897ceed5a11e38867e83935244df8"),("1466672473543","8c79c7d4bf2c11e38867e83935244df8","0a0897ceed5a11e38867e83935244df8"))
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
at android.database.sqlite.SQLiteDatabase.delete(SQLiteDatabase.java:1496)
at $JUST_MY_CLAS_NAME$6.run(BasicDAO.java:136)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1113)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:588)
at java.lang.Thread.run(Thread.java:818)


What can be the cause of it?

It's executed without errors on the server database but fails on Android.

Problem: I need to remove a lot of data from data base. I'm getting data in a form of POJOs list. So I need to create query dynamically. This is just simple case to demonstrate problem.

Probably this isn't the most optimal way, any suggestions and especially documentation will be appreciated.

CL. CL.
Answer

In SQLite,

the IN and NOT IN operators take a single scalar operand on the left and a vector operand on the right.

So you have to write out the comparisons:

DELETE FROM messages_table
WHERE (time         = '1469945224502'                    AND
       recepient_id = '8c79c7d4bf2c11e38867e83935244df8' AND
       sender_id    = '0a0897ceed5a11e38867e83935244df8')
      OR
      (time         = '1466672473543'                    AND
       recepient_id = '8c79c7d4bf2c11e38867e83935244df8' AND
       sender_id    = '0a0897ceed5a11e38867e83935244df8');