Anomoly when storing empty values into SQLite integer fields

[Bob Sneidar]

Well tickle me pink and call me Porky! Whoda thunk it? I like the part: "SQLite does use the declared type of a column as a hint that you prefer values in that format." That as good as saying that apart from the unique key nothing is typed because nothing is enforced! I think I am going down to the bank tonight after hours and engage in some "legal withdrawal affinity". 

Bob


On Feb 23, 2012, at 5:34 PM, Mark Smith wrote:

> 
> 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.
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode