-1

I have tried various solution but not working. And not able to find where it cause.for missmatch datatype i have tries pincode change to int til not working. Please help me I am getting DatabaseException(datatype mismatch (code 20 SQLITE_MISMATCH))

database = await openDatabase(join(await getDatabasesPath(), "abc.db"),
        version: 1, onCreate: (Database db, int version) async {
          
          await db.execute(
            "CREATE TABLE $tableName(${AppConstants.studentId} INTEGER PRIMARY KEY, ${AppConstants.eleID} TEXT, ${AppConstants.firstName} TEXT, ${AppConstants.middleName} TEXT, lastName TEXT, ${AppConstants.dob} TEXT, ${AppConstants.bloodGroup} TEXT, ${AppConstants.gender} TEXT,"
                " ${AppConstants.studentClass} TEXT, ${AppConstants.adharCard} TEXT, ${AppConstants.address1} TEXT, ${AppConstants.address2} TEXT, ${AppConstants.address3} TEXT, ${AppConstants.city} TEXT, ${AppConstants.state} TEXT,"
                " ${AppConstants.pincode} VARCHAR(10), ${AppConstants.images} VARCHAR(100), ${AppConstants.bloodType} TEXT, ${AppConstants.routeId} TEXT,${AppConstants.schoolId} TEXT, ${AppConstants.parents} TEXT, ${AppConstants.emails} TEXT, ${AppConstants.lat} TEXT, ${AppConstants.lng} TEXT)",
          );
        });

`> //error

statement aborts at 29: [INSERT OR REPLACE INTO student (studentId, eleID, firstName, middleName, lastName, dob, bloodGroup, gender, studentClass, adharCard, address1, address2, address3, city, state, pin

[ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: DatabaseException(datatype mismatch (code 20 SQLITE_MISMATCH)) sql 'INSERT OR REPLACE INTO student (studentId, eleID, firstName, middleName, lastName, dob, bloodGroup, gender, studentClass, adharCard, address1, address2, address3, city, state, pincode, images, bloodType, routeId, schoolId, parents, emails, lat, lng) VALUES (?, ?, ?, ?, ?, ?, NULL, ?, ?, NULL, ?, ?, ?, ?, ?, ?, NULL, NULL, NULL, ?, NULL, NULL, NULL, NULL)' args [3a2bef43-784c-433f-82e0-c45f45bdc3ec, ELIDPPS002, abc, S, cdcdcd, Sat Apr 03 00:00:00 EDT 2010, FEMALE, 7, T96502, Amanora Park Town, Neo Towers, Hadapsar, Pune, Maharashtra, 411028, 5ea3ae2f-722a-44ce-9ea8-d2ce3a1706a1]`

response: {studentId: 3a2bef43-784c-433f-82e0-c45f45bdc3ec, eleID: ELIDPPS002, firstName: abc, middleName: S, lastName: cdcdcd, dob: Sat Apr 03 00:00:00 EDT 2010, bloodGroup: null, gender: FEMALE, studentClass: 7, adharCard: null, address1: T96502, Amanora Park Town, address2: Neo Towers, address3: Hadapsar, city: Pune, state: Maharashtra, pincode: 411028, images: null, bloodType: null, parentIds: [87259c0c-4770-4eb9-8e88-2d42ad598954, 8dae86f3-d6a0-4c67-aaa5-40a4a5249576], routeId: null, schoolId: 5ea3ae2f-722a-44ce-9ea8-d2ce3a1706a1, parents: null, emails: null, lat: null, lng: null}

Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = new Map<String, dynamic>();
    data[AppConstants.studentId] = studentId;
    data[AppConstants.eleID] = eleID;
    data[AppConstants.firstName] = firstName;
    data[AppConstants.middleName] = middleName;
    data[AppConstants.lastName] = lastName;
    data[AppConstants.dob] = dob;
    data[AppConstants.bloodGroup] = bloodGroup;
    data[AppConstants.gender] = gender;
    data[AppConstants.studentClass] = studentClass;
    // data[AppConstants.studentClass] = studentClass??-1;
    data[AppConstants.adharCard] = adharCard;
    data[AppConstants.address1] = address1;
    data[AppConstants.address2] = address2;
    data[AppConstants.address3] = address3;
    data[AppConstants.city] = city;
    data[AppConstants.state] = state;
    data[AppConstants.pincode] = pincode!=null?pincode.toString():null;
    data[AppConstants.images] = images;
    data[AppConstants.bloodType] = bloodType;
    // data[AppConstants.parentIds] = null;
    data[AppConstants.routeId] = routeId;
    data[AppConstants.schoolId] = schoolId;
    data[AppConstants.parents] = parents;
    data[AppConstants.emails] = emails;
    data[AppConstants.lat] = lat;
    data[AppConstants.lng] = lng;
    return data;
  }
Nitin
  • 1
  • 2
  • can yu try to make the arguents as String like `['3a2bef43-784c-433f-82e0-c45f45bdc3ec', 'ELIDPPS002', 'abc', S, 'cdcdcd', 'Sat Apr 03 00:00:00 EDT 2010', 'FEMALE'` – nbk Jun 17 '23 at 12:27
  • @nbk this all are format, Its a response received from api and display in log.in log and or in board not visible ‘ ’ format – Nitin Jun 17 '23 at 13:52
  • @nbk i have been tried with id, name, dob, pincode, image and it’s working but if insert all value cought mismatch error – Nitin Jun 17 '23 at 13:56
  • then go column by column and always only 0one till you find it – nbk Jun 17 '23 at 14:27
  • i wonder if it not studentid which is defiend as integer and you put an uuid into it, change the primary key to text and see if that helps – nbk Jun 17 '23 at 14:42

1 Answers1

0

The Issue

If you have a column that is defined as INTEGER PRIMARY KEY (as per the studentId) (also with or without AUTOINCREMENT) then that column is an alias of the rowid column.

Unlike all other column types the rowid column or an alias of the rowid column MUST be an integer value, otherwise you will encounter the MISMATCH

In all cases a column of any type can be any type of value.

The value 3a2bef43-784c-433f-82e0-c45f45bdc3ec is not an integer value and thus cannot be stored in the studentId column as it stands and the SQLITE_MISMATCH is the resultant error.

FIXing the issue

If you were to change the column type to any other value e.g. studentid INT PRIMARY KEY, then the column is not an alias of the rowid and will accept the value.

  • you could even use the ridiculous studentid RUMPLESTILTSKIN PRIMARY KEY and SQLite's flexibility will allow this to work (the column type affinity will be NUMERIC, however each row's type for).

  • for a more comprehensive overview with some examples see How flexible/restricive are SQLite column types?

For a more comprehensive description of the rowid see https://www.sqlite.org/lang_createtable.html#rowid

MikeT
  • 51,415
  • 16
  • 49
  • 68