4

My query is showing a syntax error in the DAO_Impl build for some reason. I tried a rebuild but it still errors when conducting the following query:

Query:

@Query("UPDATE TasksTable SET daysOfWeek = :days WHERE taskID = :tkID")
    fun updateDays(tkID: Int, days: MutableList<Boolean>)

EntityTask:

data class EntityTask(
    var taskID: Int = 0,
    var personID : Int = 0,
    var name : String = "",
    var frequency: Int = 1,
    var interval: Int = 0,
    var haveSchedule: Boolean = false,
    var schedule: Int = 0,
    var scheduleString: String = "",
    var description: String = "",
    var showProgressLayout: Boolean = true,
    var daysOfWeek: MutableList<Boolean> =  mutableListOf(),
    var daysOfMonth: MutableList<Boolean> = mutableListOf(),
    var currentScores: ScoresCurrent = ScoresCurrent(),
    @Ignore
    var scores : MutableList<EntityScore> = mutableListOf()
)

DaoTasks_Impl:

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

Error message:

03-01 07:05:37.504 26855-27552/com.samuelriesterer.taskprogress E/SQLiteLog: (1) near "?": syntax error
03-01 07:05:37.514 26855-27552/com.samuelriesterer.taskprogress E/AndroidRuntime: FATAL EXCEPTION: Thread-23439
    Process: com.samuelriesterer.taskprogress, PID: 26855
    android.database.sqlite.SQLiteException: near "?": syntax error (code 1): , while compiling: UPDATE TasksTable SET daysOfWeek = ?,?,?,?,?,?,? WHERE taskID = ?
    #################################################################
    Error Code : 1 (SQLITE_ERROR)
    Caused By : SQL(query) error or missing database.
        (near "?": syntax error (code 1): , while compiling: UPDATE TasksTable SET daysOfWeek = ?,?,?,?,?,?,? WHERE taskID = ?)
    #################################################################
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1058)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:623)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
        at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1132)
        at android.arch.persistence.db.framework.FrameworkSQLiteDatabase.compileStatement(FrameworkSQLiteDatabase.java:64)
        at android.arch.persistence.room.RoomDatabase.compileStatement(RoomDatabase.java:244)
        at com.samuelriesterer.taskprogress.data.daos.DAOTasks_Impl.updateDays(DAOTasks_Impl.java:310)
        at com.samuelriesterer.taskprogress.data.Data$Companion$editTask$thread$1.run(Data.kt:569)
        at java.lang.Thread.run(Thread.java:818)

I have a type converter for this so I don't know why it won't let me update a list. I know my type converter works because it works when adding the list to the database. (It is added to my database). It is just not working when used in an UPDATE query.

My converter:

@TypeConverter fun stringToListBoolean(value: String): MutableList<Boolean>
    {
        val list = mutableListOf<Boolean>()
        value.forEach { c ->
            if(c == 'F') list.add(false)
            else list.add(true)
        }
        return list
    }
@TypeConverter fun listBooleanToString(list: MutableList<Boolean>): String
{
    var value = ""
    for(i in list)
    {
        if(i) value += "T"
        else value += "F"
    }
    return value
}
Samuel
  • 395
  • 2
  • 5
  • 20
  • You can't provide your list of variables to store it in to database. You'll need `TypeConverter` for that scenario. Your error here is due to `daysOfWeek` & `daysOfMonth` – Jeel Vankhede Mar 01 '19 at 05:16
  • But I do have a typeConverter – Samuel Mar 01 '19 at 05:19
  • Can you provide code of your converter, it'll better help find the issue. – Jeel Vankhede Mar 01 '19 at 05:20
  • I edited answer, see edit – Samuel Mar 01 '19 at 05:21
  • Have you provided that `TypeConverter` to your **Database class**? – Jeel Vankhede Mar 01 '19 at 05:32
  • yes: @Database(entities = [EntityPerson::class, EntityTask::class, EntityScore::class, EntityWeek::class, EntityMonth::class], version = 1, exportSchema = false) @TypeConverters(Converters::class) abstract class DatabasePersons : RoomDatabase() – Samuel Mar 01 '19 at 05:34
  • The converter works in all other regards in every other part of the app – Samuel Mar 01 '19 at 05:42
  • @Jeel When I look in the DAO_Impl, it never uses the converter on this query. It simply chops the list down using placeholders (e.g. ?,?,?,?,?,?,?). How do I get it to use the converter in an UPDATE query? – Samuel Mar 01 '19 at 15:52
  • Okay, so I think, I found your issue. Add `@JvmStatic` to your type converter methods. I think ROOM requires static methods on complex type conversion methods & methods you're providing are non-static right now. Hopefully this will resolve your problem. – Jeel Vankhede Mar 01 '19 at 16:17
  • Nope. I changed the Converter class to an object and added @JvmStatic to all my methods and it still is generating the same Impl code and the same error message. :( – Samuel Mar 01 '19 at 16:31
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/189288/discussion-between-jeel-vankhede-and-samuel). – Jeel Vankhede Mar 02 '19 at 04:11

2 Answers2

3

So by the suggestion from Jeel Vankhede, I changed the MutableList to an ArrayList and the UPDATE query works. 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. Not sure why this is??

Samuel
  • 395
  • 2
  • 5
  • 20
0

Not too happy with this fix but I eventually served my purpose by creating a separate class that holds the value of the lists daysOfWeek and daysOfMonth:

Interval:

class Interval(_daysOfWeek: MutableList<Boolean> = mutableListOf(false, false, false, false, false, false, false),
               _daysOfMonth: MutableList<Boolean> = mutableListOf(false, false, false, false, false, false, false, false, false,
                   false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false,
                   false, false, false, false, false, false)
)
{
    val daysOfWeek = _daysOfWeek
    val daysOfMonth = _daysOfMonth
}

and then just added a custom converter for this. Now I can query an update:

@Query("UPDATE TasksTable SET name = :name, frequency = :frequency, interval = :interval, haveSchedule = :haveSchedule, schedule = :schedule, scheduleString = :scheduleString, description = :description, showProgressLayout = :showProgressLayout, intervals = :intervals WHERE taskID = :taskID")
fun updateTask(taskID: Int, name: String, frequency: Int, interval: Int, haveSchedule: Boolean, schedule: Int, scheduleString: String, description: String, showProgressLayout: Boolean, intervals: Interval)
Samuel
  • 395
  • 2
  • 5
  • 20