Anomoly when storing empty values into SQLite integer fields

[Pete]

I absolutely agree that default values should be defined in the database
but it wouldn't help in this case since he wants the default to be either
NULL or empty and LC has taken it upon itself to decide it knows better
than the database and interpret that as zero.
Pete

On Tue, Feb 21, 2012 at 7:01 PM, Bob Sneidar <bobs at twft.com> wrote:

> Right. So in a column defined as integer not null, there must then be a
> default value when you pass empty otherwise there would be an
> inconsistency. What I do ( and how I do it is too much to go into on an
> iPhone) is I preload all the values I intend to insert/update with default
> values, then get input from the user. If the user does not alter the
> defaults, then the SQL table gets the ones I defined. The best way however
> is to define your table columns with some default. I have learned to do
> this at pains to The contrary.
>
> Bob Sneidar
> IT Manager
> Calvary Chapel CM
> Sent from iPhone
>
> On Feb 21, 2012, at 18:42, Mark Smith <Mark_Smith at cpe.umanitoba.ca> wrote:
>
> >
> > slylabs13 wrote
> >>
> >> What is the field type? What is the default? If numeric and not null
> then
> >> it may be that the value defaults to 0
> >>
> >
> > Thanks Bob, field type is integer, no default is defined. Firefox plugin
> has
> > a zero in the Not Null column,(which means?) and a zero in the Primary
> Key
> > column (which I presume means the field is not a primary key).
> >
> > Just to add to my previous comments, I thought I would check to see if it
> > had anything to do with the substitution form of revExecuteSQL. It did
> not.
> > Switching back to
> >
> >
> >  put "UPDATE test SET " & \
> >         merge("one='[[tOne]]', two='[[tTwo]]', three='[[tThree]]'") & \
> >         " WHERE id = " & tID into tCmd  -- old format
> >
> > had the same result. One advantage though was that if I put a "Put tCmd"
> to
> > throw the statement to the msg box I could actually see what the values
> > were. For the first save of the empty fields (UPDATE statement above) it
> > looks like this:
> >
> > UPDATE test SET one='', two='', three='' WHERE id = 10
> >
> > field types are integer, integer and text respectively
> >
> > After reading it back in, it displays 0's in the int fields. If I then
> try
> > saving it again (running the same code as above) I get this:
> >
> > UPDATE test SET one='0', two='0', three='' WHERE id = 10
> >
> > and as you can see the int fields now contain 0's while the text field
> does
> > not. No data entry has occurred on my part, I am just executing a
> repeated
> > read and write on the same record using the same code. Bob may be on to
> > something though.... maybe I have to define a default... but what should
> > that be (I want undefined to remain undefined)?
> >
> > -- Mark
> >
> >
> > --
> > View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4409072.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
>
> _______________________________________________
> 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>