Flex 3 SQLite and the mystery of “text value to numeric value”
Posted on March 14, 2008 by saarni
Filed Under Adobe AIR, Flex 3, sqlite
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…
Other posts by saarni
And it used to work in beta!
Hi,
I could not understand what you got as solution. Can you please elaborate it more?
Thanks,
Naresh
Hi,
Thanks, I got the point what you are saying.
Regards,
Naresh
Hi!
I have the same problem. I want to insert time (like 18:45) and gives me the same error. Can u help me. I don’t understand your description.
Thanks,
Toni
I had the same problem. I added like one line of code and that error started appearing. I took that line of code out and I was still getting the error. I took a bunch of code out and it was still happening. I checked the values and types in the database. The only thing that might have been an issue was that I had 00 in one cell instead of 0. Although it was not taken care of after that I might have blanked out that cell and then set it to zero.
I would even suggest making a new database and trying your program with the new database. In conclusion, it just started working. I put more of the code back hoping it would keep working and it did. I just think there is a problem with this error still.
Hey Sarni, thanks for the post. Would you mind posting an SQL example of how you insert/select dates into sqlite so they are read correctly by Flex? I am pulling out my hair trying to figure out why SQLite contains date fields, but Flex refuses to read them. Thanks!