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

Are you using a TypeConverter to handle storing of List<String> values in your DB? If so, please update you post to include the code for it. — Bob Snyder
– Bob Snyder, Commented May 27, 2020 at 1:30
It's end of the day for me. I'll look at this more tomorrow. I am able to reproduce the error. It appears that Room doesn't support your desired behavior. If you look at the generated code for the query (in file ParcelDao_impl.java), you will see that the number of '?'s is the number of strings in the list argument. The converter is not used. The implementation logic was probably intended for the case of a list argument in the WHERE clause. — Bob Snyder
– Bob Snyder, Commented May 27, 2020 at 5:36

sergiy tykhonov · Accepted Answer · 2020-05-27 20:23:08Z

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

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);

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);

Bob Snyder · Accepted Answer · 2020-05-27 18:20:41Z

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);

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related