Anomoly when storing empty values into SQLite integer fields

[Bob Sneidar]

For the sake of satisfying my own curiosity, I just used the Firefox sqLite manager to open an sqLite db file. I added a column to a table for testing purposes, called bobnum integer default NULL. When browsing, the column indicates it's value is Null. But when I update a row using update activities set bobnum = "" where id = 41275 and then I browse the contents, it tells me the contents is an empty string! THAT is NOT what I would have expected!! It certainly is NOT what mySQL did. 

So now that my curiosity was piqued, I used a real string. I put "test" into the numerical column, and lo and behold sqLite accepted a text string into a numerical column!!

sqLite should have either thrown an error or else put a 0 in the column, but how can an integer typed column contain a string of any type?? What else will sqLite accept as valid? Apparently sqLite could care less about typing (which is probably why RunRev chose it for it's defacto database). For this reason we cannot depend on errors thrown in sqLite as a means of error checking what a user enters. (Some people actually do that yes). I have to think that sqLite is being VERY forgiving in this regard. If sqLite is going to behave this way, then for the purposes of LC we may as well define all our columns to be text and be done with it. 

Bob