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

user_13104026 0
Viewed 1321

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 ...

android-room android-studio android-sqlite sqlite android
2 Answers

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);
  1. 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);
user_127184140

The documentation for the Query annotation explains a feature of Room argument binding:

As an extension over SQLite bind arguments, Room supports binding a list of parameters to the query. At runtime, Room will build the correct query to have matching number of bind arguments depending on the number of items in the method parameter.

This feature was likely intended to be used in the where-clause (as shown in the documentation example), but appears to be applied everywhere in the query statement.

In your case, the desired behavior is to have Room apply your type converter, but instead Room is ignoring the type converter and generating the special list binding.

I think you are going to have to work around this limitation of the current Room implementation. You might want to write a Room bug-report to get confirmation that the explanation provided here is correct.

One option for workaround that will use the type converters is to define this class:

public class DeliveryPersonsUpdate {
    public int id;
    public List<String> deliveryPersons;

    public DeliveryPersonsUpdate(int id, List<String> deliveryPersons) {
        this.id = id;
        this.deliveryPersons = deliveryPersons;
    }
}

then add this method to your Dao:

@Update(entity = Parcel.class)
void update(DeliveryPersonsUpdate update);

Example invocation:

db.ParcelDao().update(new DeliveryPersonsUpdate(id, personsList);
user_48157180
Related