Adobe AIR Development

[EraserX]

I've been racking my brain for hours trying to figure this out and I can't so I'm hoping someone here might be able to help me.

I'll start off by saying that I use an SQLite database in all of my apps so I am familiar with them.  I can't figure out why I am getting an error when I try to update a value from a particular table in my db.

Here is my table structure:

CREATE TABLE Units (unitID INTEGER PRIMARY KEY, categoryID NUMERIC, unitNameString TEXT, unitSymbol TEXT, unitValue NUMERIC, favorite NUMERIC, updateDate NUMERIC);

Here is my update function:

sqlStatement.clearParameters();
var sql:String = "UPDATE Units SET favorite = :favorite " +
		"WHERE unitID = :unitID ";
sqlStatement.parameters[":favorite"] = favorite;
sqlStatement.parameters[":unitID"] = unitID;
sqlStatement.text = sql;
sqlStatement.execute();
sqlResult = sqlStatement.getResult();

favorite and unitID are both integers.  I have confirmed that they are passing properly. I get the following error:

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

I have tried putting in literal values and I get the same error!

"UPDATE Units SET favorite = 1 " +
		"WHERE unitID = 1 ";

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

Even STRANGER I tried updating a String in my Units table, even without a WHERE clause and I get the same error!

"UPDATE Units SET unitNameString = 'test' ";

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

I'll note that I use data from my Units table throughout my app without error.  Anyways, so now convinced something screwy must be going on, I rebuild my database... the error persists.

Has anyone seen something like this?

[JRab]

hmm you're right, i really cant find the error. whats more weird is that i always thought SQLite was ok with datatype mismatches and wasnt so strict when putting in the wrong datatype. this shows the complete opposite.

[EraserX]

I've been trolling the internet for answers, and I think I may know the cause.  Turns out SQLite in AIR is EXTREMELY picky about enforcing data types.   See http://chrisgriffith.wordpress.com/2011/02/03/error-3132-in-air-and-sqlite/  The author of that article had an invalid value somewhere in his db and when he tried to update a column, it was not until then that AIR decided to complain about it.

My db has 600+ rows so I'm working on figuring out if one of the values is the culprit. 

I'll report back.

[EraserX]

I got it!  I imported my data from a CSV.  My "updateDate" column was all just empty.  Apparently AIR wasn't happy about that, because when I inserted a dummy value (-1) into that column, the stars aligned and BAM my db update statement was working again.

So here's the lesson.  If you are getting mismatch errors, take a close look at the data in your columns.  It is probably the culprit.

[JRab]

man i gotta stop making up my own SQLite rules then! good job on hunting down your error though hah

[peter9477]

SQLite itself is totally lax when it comes to enforcing types.  The problem is specifically the AIR API for SQLite in flash.data.  Interesting, and good to know.

[gaveho]

I had the same problem. It was the data on my table. It was empty and needed a dummy value like 0.