Casue of SQLite Error: 'Error #3132: Data type mismatch.' in Adobe AIR

Question

"SELECT * FROM locations 
 JOIN section_has_location ON locations.location_id = section_has_location.location_id 
 WHERE section_has_location.chapter_id =2 
   AND section_has_location.section_id=2"

I get the error:

SQLError: 'Error #3132: Data type mismatch.', details:'could not convert text value to numeric value.', operation:'execute', detailID:'2300'

These are the tables:

CREATE TABLE locations ( 
    location_id INTEGER,
    name        TEXT,
    mask_id     TEXT,
    x           REAL,
    y           REAL,
    content     TEXT,
    image_url   TEXT,
    type        TEXT 
);

CREATE TABLE section_has_location ( 
    chapter_id  INTEGER,
    section_id  INTEGER,
    location_id INTEGER 
);

How do I fix the query as to not cause the error?

update: I exported all the data, and imported into a new clean database. This seems to have solved that error.

Solution

I just took your code, created the tables and ran the query and it works fine.

Im guessing what you have done is maybe edited the type of one of the columns but the table hasn't been updated, try sp_help tablenamehere or look in object explorer if you are using management studio and check what the datatype is of your columns, or if you can it may be easier to just drop both tables and recreate them

Comments

[davivid]

Thanks, this is really strange. Looking at the DB, the column types are definitely correct. Sorry I forgot to mention that I am using this in Adobe AIR, but that shouldn't make any difference I would like to think.

[Alex K]

@davivid Look at this: opensourcehacker.com/2008/03/14/a-short-story-about-why-to-r‌​tfm. May be you get the same situation

[Purplegoldfish]

AIR shouldnt affect it in that way :S its really strange though. Perhaps try convert this to a stored proc and call that rather than using the querystring?

[Alex K]

@davivid see also: stackoverflow.com/questions/4092630/…

[davivid]

@Purplegoldfish I'm not sure but I dont think you can have stored proc in sqlite? see: stackoverflow.com/questions/3335162/…

[Purplegoldfish]

@davivid ah sorry i didnt notice the sqlite tag!

[davivid]

Well exporting the data and importing into a clean empty DB seemed to solve the problem. Cheers.