Flex 3 SQLite and the mystery of “text value to numeric value”

Learning new development environments and languages can be quite fun, but sometimes it turns out just plain frustrating. Especially so if you are trying to learn something that is still in its beta stage. This time it was Adobe AIR and Flex 3.

The program in question required storing date and time values in an sqlite3 database. Now, as the title of this entry says, I should’ve just read the AIR sqlite handling manual 1 more time (or 2-10 more times) and I could’ve learnt that AIR SQL handling accepts “Date” as column type and handles inserting and getting Date objects from the database transparently. I did not notice this while studying and used “TIMESTAMP” as column type instead. This is where things went wrong… so wrong…

Using “TIMESTAMP” column type and inserting datetime strings worked flawlessly in the betas along with using sqlite date functions. But AIR 1.0 release came and suddenly my application started producing the following SQL errors everywhere:

SQLError: 'Error #3115: SQL Error.', details:'could not convert text value to numeric value.', operation:'execute'

Naturally for a few days I went through my SQL code and tables trying different things on columns that had types like “INTEGER”, “REAL”, “NUMERIC” and so on. After trying and trying I finally decided to read the AIR documentation again. The essential part turned out to be how AIR handles the column affinities in the release version: everything not recognized by AIR is given “NUMERIC” column affinity. This and the fact that AIR is now stricter about types is what changed between the betas and release version and broke the columns with “TIMESTAMP” as type since I was trying to insert text.

So, again, I should’ve just rtfm…