CODEWITHSUNDEEP
javaandroidandroid-roomandroid-architecture-components
M. Wojcik
M. Wojcik

Reputation: 2599

Android room custom update @Query with String array crash with "sqlite syntax ? error"

So I have a problem with UPDATE query inside @Query that tries to update a column that holds String[].

Converters:

@TypeConverter
public static String[] fromString(String value) {
    Type listType = new TypeToken<String[]>() {
    }.getType();
    return new Gson().fromJson(value, listType);
}

@TypeConverter
public static String fromArrayList(String[] list) {
    //Type listType = new TypeToken<String[]>() {
    //}.getType();
    Gson gson = new Gson();
    String json = gson.toJson(list);
    return json;
}

Dao:

@Dao
public interface CharacterDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    long[] insertCharacters(Character... characters);

    @Query("UPDATE characters SET name = :name, episodes = :episodes, createdDate = :createdDate " +
            "WHERE character_id = :character_id")
    void updateCharacter(String character_id, String name, String[] episodes, String createdDate);
}

So to explain it deeper what im doing now is trying to do in my Repository.class is to update user when insert returns -1 (so there was a conflict). Also I added @TypeConverters for whole database, altought I tried to adding it to dao and entities etc. but it doesnt solve the issue.

So the problem appears when calling characterDao.updateCharacter with proper arguments. When I remove the episodes array from both method call and @query it works perfectly, so the String[] is the problem here.

However, when I use @Update instead of custom @Query it works just fine. But it still bothers me and there is no answer for that anywhere.

FYI inserting works fine. The problem is only with updating

The error I get is "syntax ? error"

trace:

  --------- beginning of crash
2019-05-15 14:29:23.556 20853-21022/pl.com.bubka.characs E/AndroidRuntime: FATAL EXCEPTION: pool-1-thread-1
    Process: pl.com.bubka.characs, PID: 20853
    android.database.sqlite.SQLiteException: near "?": syntax error (Sqlite code 1 SQLITE_ERROR): , while compiling: UPDATE characters SET name = ?, episodes = ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, createdDate = ? WHERE character_id = ?, (OS error - 0:Success)
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:948)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:559)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:603)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
        at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1166)
        at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.compileStatement(FrameworkSQLiteDatabase.java:64)
        at androidx.room.RoomDatabase.compileStatement(RoomDatabase.java:249)
        at pl.com.bubka.characs.database.CharacterDao_Impl.updateCharacter(CharacterDao_Impl.java:465)
        at pl.com.bubka.characs.repositories.CharactersRepository$1.saveCallResult(CharactersRepository.java:50)
        at pl.com.bubka.characs.repositories.CharactersRepository$1.saveCallResult(CharactersRepository.java:39)
        at pl.com.bubka.characs.utils.NetworkBoundResources$3$1.run(NetworkBoundResources.java:69)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
        at java.lang.Thread.run(Thread.java:784)

Upvotes: 7

Views: 1671

Answers (2)

AlexS
AlexS

Reputation: 972

Change your dao method:

@Query("UPDATE characters SET name = :name, episodes = :episodes, createdDate = :createdDate WHERE character_id = :character_id")
void updateCharacter(String character_id, String name, String episodes, String createdDate);

And repository method before using dao:

void update(...) {
  String episodeToInsert = YourConverterClass.fromArrayList(episodes)
  dao.updateCharacter(charactedId, name, episodeToInsert, createDate)
}

Upvotes: 0

p2pdops
p2pdops

Reputation: 516

Modify your DAO file Like this:

@Dao
public interface CharacterDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    long[] insertCharacters(Character... characters);

    @Update
    void update(Character character);

/*By using above Annotation @Update()
  Room Db automatically identifies Entity (in this case Character object)
  from primary key and try to update changed Values for that id*/
}

Usage:

Character character = new Character();
character.setId(character_id);// or whatever primary key (mandatory)
character.setName(name); // add all 

character.set...(yourParams); //any other parameters to update
characterDao.update(character); // reference your character_dao

Reference: Android Room Documentation

Upvotes: 0

Related Questions