Android Room update query getting stuck on auto generated code

Question

After using Android Room for a few weeks now and getting the hang of basic queries, I've run into an issue with attempting to update a list of custom objects. For some reason when Room tries to create the SQLLite string to insert my new data, it gets stuck with the placeholders: From the debug window:

Caused by: android.database.sqlite.SQLiteException: near "?": syntax error (code 1): , while compiling: UPDATE player_characters SET ability_scores = ?,?,?,?,?,? WHERE playerCharacterID = ? ################################################################# Error Code : 1 (SQLITE_ERROR) Caused By : SQL(query) error or missing database. (near "?": syntax error (code 1): , while compiling: UPDATE player_characters SET ability_scores = ?,?,?,?,?,? WHERE playerCharacterID = ?) ################################################################# at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1005) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:570) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:59) at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1375) at android.arch.persistence.db.framework.FrameworkSQLiteDatabase.compileStatement(FrameworkSQLiteDatabase.java:62) at android.arch.persistence.room.RoomDatabase.compileStatement(RoomDatabase.java:204) at com.pathfinderstattracker.pathfindercharactersheet.database.database_daos.PlayerCharacterDao_Impl.updatePlayerCharacterAbilityScores(PlayerCharacterDao_Impl.java:321)

The DAO that contains the query:

@Dao
@TypeConverters({UUIDConverter.class,
                 AbilityScoreConcreteConverter.class})
public interface PlayerCharacterDao
{       
    @Query("UPDATE player_characters "+
           "SET ability_scores = :playerCharacterAbilityScores "+
           "WHERE playerCharacterID = :characterIDToUpdate")
    void updatePlayerCharacterAbilityScores(UUID characterIDToUpdate, List<AbilityScore> playerCharacterAbilityScores);
}

And the repository command that calls it:

private static class updatePlayerCharacterAbilityScoresAsyncTask extends AsyncTask<Object, Void, Void>
{
    private PlayerCharacterDao asyncPlayerCharacterDao;
    updatePlayerCharacterAbilityScoresAsyncTask(PlayerCharacterDao dao) {asyncPlayerCharacterDao = dao;}
    @Override
    protected Void doInBackground(final Object... params)
    {
        UUID playerCharacterID = (UUID)params[0];
        List<AbilityScore> updatedAbilityScores = (ArrayList<AbilityScore>)params[1];
        asyncPlayerCharacterDao.updatePlayerCharacterAbilityScores(playerCharacterID, updatedAbilityScores);
        return null;
    }
}

I can confirm that the data is getting to the room query properly, and I've tried passing both concrete and interface objects into the query, as well as had a converter for both individual AbilityScore objects and a list of AbilityScore objects. Any help would be greatly appreciated!

EDIT: A few people have requested the entity that's being updated:

@Entity(tableName = "player_characters")
@TypeConverters({AlignmentEnumConverter.class,
                 HitPointsConverter.class,
                 DamageReductionConverter.class,
                 StringListConverter.class,
                 UUIDConverter.class,
                 StringListConverter.class,
                 AbilityScoreListConverter.class,
                 CombatManeuverConverter.class})
public class PlayerCharacterEntity
{
    @PrimaryKey
    @NonNull
    private UUID playerCharacterID;
    @ColumnInfo(name="character_name")
    private String playerCharacterName;
    @ColumnInfo(name="character_level")
    private int characterLevel;
    @ColumnInfo(name="concentration_check")
    private int concentrationCheck;
    @ColumnInfo(name="character_alignment")
    private AlignmentEnum characterAlignment;
    @ColumnInfo(name="total_base_attack_bonus")
    private int totalBaseAttackBonus;
    @ColumnInfo(name="total_hit_points")
    private IHitPoints totalHitPoints;
    @ColumnInfo(name="total_ac")
    private int totalAC;
    @ColumnInfo(name="damage_reduction")
    private IDamageReduction damageReduction;
    @ColumnInfo(name="languages_known")
    private List<String> languagesKnown;
    @ColumnInfo(name="ability_scores")
    private List<IAbilityScore> abilityScores;
    @ColumnInfo(name="combat_Maneuver_stats")
    private ICombatManeuver combatManeuverStats;
    @ColumnInfo(name="spell_resistance")
    private int spellResistance;
    @ColumnInfo(name="initiative")
    private int initiative;
    @ColumnInfo(name="fortitude_save")
    private int fortitudeSave;
    @ColumnInfo(name="reflex_save")
    private int reflexSave;
    @ColumnInfo(name="will_save")
    private int willSave;

    ~Getters/Setters and Constructors removed for brevity~
}

EDIT: And for good measure I thought I would include the @TypeConverter for AbilityScore (I've reverted this to an earlier form that uses interfaces rather than concrete, since that works elsewhere in the code and difference didn't seem to change anything):

public class AbilityScoreConverter
{
    @TypeConverter
    public IAbilityScore fromString(String value)
    {
        IAbilityScore formattedAbilityScore = new AbilityScore();
        String[] tokens = value.split(" ");
        formattedAbilityScore.setAmount(Integer.parseInt(tokens[0]));
        switch(tokens[1])
        {
            case "STR":
                formattedAbilityScore.setStat(AbilityScoreEnum.STR);
            case "DEX":
                formattedAbilityScore.setStat(AbilityScoreEnum.DEX);
            case "CON":
                formattedAbilityScore.setStat(AbilityScoreEnum.CON);
            case "INT":
                formattedAbilityScore.setStat(AbilityScoreEnum.INT);
            case "WIS":
                formattedAbilityScore.setStat(AbilityScoreEnum.WIS);
            case "CHA":
                formattedAbilityScore.setStat(AbilityScoreEnum.CHA);
            default:
                //This may cause issues down the line if a non existent enum gets in the db somehow, but we don't have any error handling yet
                //Todo: Add error handling
                formattedAbilityScore.setStat(AbilityScoreEnum.STR);
        }

        return formattedAbilityScore;
    }

    @TypeConverter
    public String toString(IAbilityScore value)
    {
        return value.toString();
    }
}

EDIT: I've cleaned up the logcat text to focus just on the Room/SQLLite issues.

I'm not at my computer at the moment, but I'll add the '@'Entity class when I am, as well as the '@'Type Converter in case it's useful. Is there something I should be looking for there? — Snicfn711, Jun 13 '18 at 12:43

score 1 · Accepted Answer · answered Jun 22 '18 at 13:41

1

After some searching I was unfortunately forced to give up on updating my db using a @Query command and instead had to fall back on using Rooms default @Update notation. While this does work and properly updates the data in the database, it doesn't allow for me to only update certain fields.

answered Jun 22 '18 at 13:41

Snicfn711

151
1
7

1

Did you find a solution to update only a few fields then? I'm facing a similar issue. See [this](https://stackoverflow.com/questions/55209502/typeconverter-not-working-for-setstring) for my question. – AnEnigmaticBug Mar 17 '19 at 16:59
Sorry to say that I was never able to solve the issue. – Snicfn711 Mar 18 '19 at 17:18

score 0 · Answer 2 · answered Sep 03 '19 at 09:48

Using ArrayList instead of List in the UPDATE query works for me.

I've followed the solution.

The difference in the Impl build :

MutableList:

@Override
  public void test(int tkID, List<Boolean> test) {
    StringBuilder _stringBuilder = StringUtil.newStringBuilder();
    _stringBuilder.append("UPDATE TasksTable SET test = ");
    final int _inputSize = test.size();
    StringUtil.appendPlaceholders(_stringBuilder, _inputSize);
    _stringBuilder.append(" WHERE taskID = ");
    _stringBuilder.append("?");
    final String _sql = _stringBuilder.toString();
    SupportSQLiteStatement _stmt = __db.compileStatement(_sql);

ArrayList:

@Override
  public void test(int tkID, ArrayList<Boolean> test) {
    final SupportSQLiteStatement _stmt = __preparedStmtOfTest.acquire();
    __db.beginTransaction();
    try {
      int _argIndex = 1;
      final String _tmp;
      _tmp = Converters.listBooleanToString(test);
      if (_tmp == null) {
        _stmt.bindNull(_argIndex);
      } else {
        _stmt.bindString(_argIndex, _tmp);
      }
      _argIndex = 2;
      _stmt.bindLong(_argIndex, tkID);
      _stmt.executeUpdateDelete();
      __db.setTransactionSuccessful();
    } finally {
      __db.endTransaction();
      __preparedStmtOfTest.release(_stmt);
    }
  }

As you can see, the ArrayList uses the converter while the MutableList does not.

Android Room update query getting stuck on auto generated code

2 Answers2

Linked