Anomoly when storing empty values into SQLite integer fields

Pete pete at mollysrevenge.com
Thu Feb 23 15:23:26 EST 2012


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>



More information about the use-livecode mailing list