flutter: sqflite insert error, "datatype mismatch"

Question

The CREATE successfully works, but INSERT doesn't work. I think arguments of INSERT are correct... but why?

(09.09 modify) The problem is the category problem with string input argument.

Docker) INSERT INTO Quest(category) VALUES(?) args[walking(X) / ‘walking’(O) / “walking”(O)]

Flutter) INSERT INTO Quest(category) VALUES(?) args[walking(X) / ‘walking’(X) / “walking”(X)]

flutter: CREATE TABLE Quest ( 
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    category TEXT, 
    level INTEGER, 
    need_token INTEGER, 
    reward_token INTEGER,
    start_date TIMESTAMP, 
    finish_date TIMESTAMP, 
    achieve_date TIMESTAMP, 
    goal INTEGER, 
    need_times INTEGER, 
    achievement INTEGER
)

flutter: DatabaseException(
    Error Domain=FMDatabase Code=20 "datatype mismatch" 
    UserInfo={NSLocalizedDescription=datatype mismatch}
) sql 'INSERT OR REPLACE INTO Quest (
    category, 
    level, 
    need_token, 
    reward_token, 
    start_date, 
    finish_date, 
    achieve_date, 
    goal, need_times, 
    achievement
) VALUES (?, ?, ?, ?, ?, ?, NULL, ?, ?, ?)' 
args ['walking', 0, 0, 1, 1662658696748, 1662745096748, 100, 1, 0]

However, the INSERT SQL works in sqlite of docker.

sqlite in docker

In docker, the SQL successfully works but not with sqflite, Flutter. What should I do?

The class of Quest is here.

class Quest {
  String category;
  int level;
  int needToken;
  int rewardToken;
  DateTime startDate;
  DateTime finishDate;
  DateTime? achieveDate;
  int goal;
  int needTimes;
  int achievement;

  ...

  Map toDBData() => {
    'category': "'$category'",
    'level': level,
    'need_token': needToken,
    'reward_token': rewardToken,
    'start_date': startDate.millisecondsSinceEpoch,
    'finish_date': finishDate.millisecondsSinceEpoch,
    'achieve_date': achieveDate?.millisecondsSinceEpoch,
    'goal': goal,
    'need_times': needTimes,
    'achievement': achievement
  };
}

Future insertQuest(Quest quest) async =>
      await dbHelper.insert(_tableName, quest.toDBData());

Future insert(String table, Map data) async => await db!
            .insert(table, data, conflictAlgorithm: ConflictAlgorithm.replace);

Please help me... I struggled this problem for days but I couldn't solve it.

장성호 · Answer

Solve the problem by

db = await openDatabase(path, version: 1,
          onCreate: (Database db, int version) async {
        await db
            .execute(statement..trim().replaceAll(RegExp(r'[\s]{2,}'), ' '));
      }, 
          // add 'CREATE TABLE IF NOT EXISTS TABLE' SQL to onOpen function.
          onOpen: (Database db) async {
        // await db.execute("DROP TABLE $tableName);
        await db
            .execute(statement..trim().replaceAll(RegExp(r'[\s]{2,}'), ' '));
      });

The problem is the Quest table is already created, but attributes isn't same as what I wanna create. So I drop and recreate it.

flutter: sqflite insert error, "datatype mismatch"

1 Answers