Anomoly when storing empty values into SQLite integer fields

[Mark Smith]

slylabs13 wrote
> 
> 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
> 

Hi Bob, check out #3 in the SQLite FAQ <http://www.sqlite.org/faq.html#q3>

Cheers,

- Mark


--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4415835.html
Sent from the Revolution - User mailing list archive at Nabble.com.