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