Anomoly when storing empty values into SQLite integer fields

[Mark Schonewille]

Hi,

In SQL land, empty doesn't exist. Whenever you want a value to be empty, you must set the value to null. Be careful: in SQLite null is not NULL (I can never remember which one I need; you'll have to try). Note that if you're doing a query on fields that contain null values, those records won't be returned by default. If you really want to be able to query an empty field, you have to look for null values explicitly or use a special code to indicate that the field is empty (e.g. 'empty' for strings; -99 may work for positive integers; null would be best).

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

Download the Installer Maker Plugin 1.7 for LiveCode here http://qery.us/za

On 23 feb 2012, at 18:42, Mark Smith wrote:

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