Anomoly when storing empty values into SQLite integer fields

Mark Smith Mark_Smith at cpe.umanitoba.ca
Thu Feb 23 12:42:12 EST 2012


slylabs13 wrote
> 
> It seems that if you use an empty string "" on a numeric column, SQL
> interprets that as zero 0. 
> 

Hi Bob, that basically is the problem right there. 


slylabs13 wrote
> 
> It begs the question however, why in the world you would pass a string
> value to a numeric column?
> 

I'm not, or at least I'm not intending to. I have a form with a bunch of LC
fields on them. Some are going to have text strings in them, some numbers.
When the user leaves the form I want to save everything to the database.
When the user comes back to the form I want to reload everything they have
saved, and redisplay it for further editing/viewing. However, fields in LC
are not typed. LC does not distinguish between a text field and a number
field. I think they are just all text to LC.

I am just copying whats in the field to the database. If nothing is in the
field and I copy it to an SQlite text field, it comes back empty when I
reload it. Which is great. However, if I copy nothing (as in nothing in the
LC field) to an integer field in SQLite when I subsequently reload it, it
comes back as 0. That translation is an error in my opinion. 

One could code missing values as -99 or something (using defaults as you
suggest) and do conversion to nothing before displaying... there are lots of
possible workarounds (I'm just storing everything in text fields in SQLite
for now, because that seems to be the simplest solution and requires no
additional code). If you don't enter anything into a field (number or text)
then after recall I think LC should display nothing. There may still be some
debate about this, but I think that would be the ideal scenario. That's how
Pete has written up the bug report. 

-- Mark


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




More information about the use-livecode mailing list