Anomoly when storing empty values into SQLite integer fields

[Bob Sneidar]

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