Anomoly when storing empty values into SQLite integer fields

[Pete]

Bob,
The field type is integer as Mark said.  Since he doesn't have a default
specified, the default value is NULL and that should not come back as zero
and doesn't in any tool except LC - tried it with sqlite3 and the Firefox
plugin.  Nor should an empty value he's supplying (which is entirely
different than NULL to SQL) come back as zero and, once again, it doesn't
in any tool except LC.

The only way round this that I've found as in my earlier email is to not
include the integer column in any INSERT or UPDATE statements if you want
it to come back as empty instead of zero.

On Tue, Feb 21, 2012 at 6:42 PM, 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
>
>


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