Anomoly when storing empty values into SQLite integer fields

[Pete]

Thanks for pointing that out Mark.

SQLite allows any type of data in any column, it doesn't have strong
typing.  But if you want strong typing, you can use  a CHECK constraint on
any column to limit what type of data is acceptable.

I'll take a leaf out of Ken Ray's book here and start the next section with
the tag:

<soapbox>

Yes, some people do rely on a db's data checks to flag errors, and wise
people they are. That's where data checks belong, not in your application
code.  Why duplicate the work the db already does for you?  It probably
does it more efficiently and most of the time it's easier to define.

If you need to add/delete/change a validation check, you can go into your
schema and do it, no need to create a new version of your application and
send it to all your users.

It's the only way to protect your database from invalid data being inserted
by anyone, either inadvertently or intentionally, who can get their hands
on any one of of the available SQLite admin tools.

You can even specify custom error messages if you choose, at least in
SQLite (see the RAISE function, don't know about other SQL implementations).

I'm a firm believer in getting as much data handling out of my application
code and into the database.  The DEFAULT, UNIQUE, FOREIGN KEY, NOT NULL
constraints are all valuable tools provided by SQL; use them where
appropriate, they will serve you well.


</soapbox>

Pete

On Thu, Feb 23, 2012 at 5:34 PM, Mark Smith <Mark_Smith at cpe.umanitoba.ca>wrote:

>
> slylabs13 wrote
> >
> > For the sake of satisfying my own curiosity, I just used the Firefox
> > sqLite manager to open an sqLite db file. I added a column to a table for
> > testing purposes, called bobnum integer default NULL. When browsing, the
> > column indicates it's value is Null. But when I update a row using update
> > activities set bobnum = "" where id = 41275 and then I browse the
> > contents, it tells me the contents is an empty string! THAT is NOT what I
> > would have expected!! It certainly is NOT what mySQL did.
> >
> > So now that my curiosity was piqued, I used a real string. I put "test"
> > into the numerical column, and lo and behold sqLite accepted a text
> string
> > into a numerical column!!
> >
> > sqLite should have either thrown an error or else put a 0 in the column,
> > but how can an integer typed column contain a string of any type?? What
> > else will sqLite accept as valid? Apparently sqLite could care less about
> > typing (which is probably why RunRev chose it for it's defacto database).
> > For this reason we cannot depend on errors thrown in sqLite as a means of
> > error checking what a user enters. (Some people actually do that yes). I
> > have to think that sqLite is being VERY forgiving in this regard. If
> > sqLite is going to behave this way, then for the purposes of LC we may as
> > well define all our columns to be text and be done with it.
> >
> > Bob
> >
>
> Hi Bob, check out #3 in the SQLite FAQ <http://www.sqlite.org/faq.html#q3>
>
> Cheers,
>
> - Mark
>
>
> --
> View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4415835.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> 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>