Anomoly when storing empty values into SQLite integer fields

[Bob Sneidar]

Ah ic. I think that in a situation where you are passing an empty string to a numerical field with no default, either SQL must throw an error, or else insert what amounts to nothing in a numeric column. 

I think the nature of sqLite is that Instead of tossing an error it puts 0 in the column. You are right that not including columns you do not intend to update is the way to go. What I do is I have a function called buildRecord which gets the defaults of the table and populates an array accordingly. I then get user input passing that thru validation functions before inserts/updates. Then i update all columns in the table. By this method I avoid any unexpected results/errors in my SQL queries. I suppose that checking for null  in values can be used as a method of detecting an uninitialized value, but there are other ways to go about that IMHO.  

Bob Sneidar
IT Manager
Calvary Chapel CM
Sent from iPhone

On Feb 21, 2012, at 19:05, Pete <pete at mollysrevenge.com> wrote:

> 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>
> _______________________________________________
> 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