Anomoly when storing empty values into SQLite integer fields

[Pete]

Here's an illustration of how null works in sqlite, using sqlite3, the
"official" command line tool for sqlite admin and therefore most likely to
work correctly.  You can also go to http://www.sqlite.org/nulls.html for a
write up on how null is treated in various sqlite expressions.

First create a table and put some entries in it:


create table t1(a int, b int, c int);
insert into t1 values(1,0,0);
insert into t1 values(2,0,1);
insert into t1 values(3,1,0);
insert into t1 values(4,1,1);
insert into t1 values(5,null,0);
insert into t1 values(6,null,1);


insert into t1 values(7,null,null);

I've used lowercase null above but uppercase NULL works too.

Now query all the rows

select * from t1;


a           b           c


----------  ----------  ----------


1           0           0
2           0           1
3           1           0
4           1           1
5                       0
6                       1
7

All records are returned, including  rows 5-7 which have a null value in
columns b and c.  Notice they just show no value, not the word null.

Now try this SELECT:

select * from t1 where b is null;
a           b           c
----------  ----------  ----------
5                       0
6                       1
7

All rows with null in column b are correctly returned.  Note you cannot use
the expression "b=null" - you'll get nothing returned.  You must use the
special "is null" or "is not null" operators.

Pete


On Thu, Feb 23, 2012 at 11:01 AM, Mark Schonewille <
m.schonewille at economy-x-talk.com> wrote:

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


-- 
Pete
Molly's Revenge <http://www.mollysrevenge.com>