android.database.sqlite.SQLiteException: near "?": syntax error (code 1):

Question

I'm using Room library (MVVM Pattern), and one of the Dao functions returns this error message:

android.database.sqlite.SQLiteException: near "?": syntax error (code 1): , while compiling: UPDATE parcel_table SET possibleDeliveryPersonsList = ?,? WHERE id = ?

This is the Dao code:

@Dao
public interface ParcelDao {
    @Insert
    void insert(Parcel parcel);

    @Delete
    void delete(Parcel parcel);

    @Query("UPDATE parcel_table SET shippingDate=:shippingDate WHERE id = :id")
    void updateShippingDate(String shippingDate, int id);

    @Query("UPDATE parcel_table SET parcelStatus=:status WHERE id = :id")
    void updatePackageStatus(Enums.ParcelStatus status, int id);

    @Query("UPDATE parcel_table SET deliveryPersonName=:deliveryPersonName WHERE id = :id")
    void updateDeliveryPersonName(String deliveryPersonName, int id);

    @Query("UPDATE parcel_table SET possibleDeliveryPersonsList = :possibleList WHERE id = :tid")
    void updatePossibleDeliveryPersonsList(List<String> possibleList, int tid);

    @Query("DELETE FROM parcel_table")
    void deleteAllParcels();

    @Query("SELECT * from parcel_table")
    LiveData<List<Parcel>> getParcels();
}

And this is part of the Parcel class:

@Entity(tableName = "parcel_table")
public class Parcel {
    private Enums.ParcelType parcelType;
    private boolean isFragile;
    private Enums.ParcelWeight parcelWeight;
    private LatLng warehouseLocation;
    private String recipientName;
    private LatLng recipientAddress;
    private String recipientEmail;
    private String recipientPhone;
    private String dateReceived;
    private String shippingDate;
    private Enums.ParcelStatus parcelStatus;
    private String deliveryPersonName;
    private String fireBasePushId;
    private List<String> possibleDeliveryPersonsList;
@PrimaryKey(autoGenerate = true)
    @NonNull
    private int id;
        //and more...
}

The List<String> Type Converter:

@TypeConverter
    public String listToString(List<String> list) {
        String joined = TextUtils.join(", ", list);
        return joined;
    }

    @TypeConverter
    public List<String> stringToList(String string) {
        List<String> myList = new ArrayList<String>(Arrays.asList(string.split(",")));
        return myList;
    }

I have no idea what to do, because the SQLite code is supposedly automatically generated by the Dao and I have no effect on it ...

Answer 1

Two more workarounds in addition to Bob Snyder' answer (but they need to be thoroughfully tested):

1. To "imitate" TypeConverter (from List to String) by yourself (it's a tricky thing, I've not tried it in practice!):

In DAO change the type of possibleList to String:

@Query("UPDATE parcel_table SET possibleDeliveryPersonsList = :possibleList WHERE id = :tid")
    void updatePossibleDeliveryPersonsList(String possibleList, int tid);

add auxiliary method for conversion (you can place it at DAO as well):

void updatePossibleDeliveryPersonsList(List<String> possibleList, int tid) {
   String listToString = TextUtils.join(", ", possibleList); 
   // copied from your converter, it could be put in some common function to follow DRY
   updatePossibleDeliveryPersonsList(listToString, tid); 
}

and call it from Repository/ViewModel:

db.ParcelDao().updatePossibleDeliveryPersonsList(possibleList, tid);

2. To replace your multiple updateXXX methods in DAO with single update (you have a lot of fields in your table, may be it would be better to try some universal way to update any combinations of them?):

@Update
void update(Parcel parcel);

Add to your DAO method for searching parcel by id:

@Query("SELECT * from parcel_table where id = :id")
Parcel getParcel(int id);

And in your Repository/ViewModel at first get Parcel, then change it (status, name whatever) and then update database:

Parcel parcel = db.ParcelDao().getParcel(id); // let's say it can't be null
parcel.shippingDate = yourShippingDate; // or change here any of your other fields, including list
db.ParcelDao().update(parcel);