Anomoly when storing empty values into SQLite integer fields

[Bob Sneidar]

 Is it important to discern between null and a value? If so, uncheck the not null in the table definition and don't include the column in inserts/updates

Bob Sneidar
IT Manager
Calvary Chapel CM
Sent from iPhone

On Feb 21, 2012, at 18:58, Mark Smith <Mark_Smith at cpe.umanitoba.ca> wrote:

> 
> Peter Haworth-2 wrote
>> 
>> Long story short, the only way I could find to make this work correctly is
>> to not include the integer column in the UPDATE statement.  Not sure if
>> your update was just a test with that one column or if there;d normally be
>> more columns to update, but you'd have to test your integer fields for
>> empty and leave them off the UPDATE statement if they are.
>> -- 
>> Pete
>> Molly's Revenge <http://www.mollysrevenge.com>
>> 
> 
> Thanks Pete, nice to have confirmation, although its not so much fun to
> contemplate the alternatives :)
> 
> I guess there are 2 really: in the first case, since I am only going to be
> using this app with SQLite and SQLite has pretty relaxed type affinity I
> could store everything in text fields. LC seems to be leaving those alone
> when they are undefined. However, thats not a good solution if you are
> writing an app that needs to work with other SQL databases. In that case
> your suggestion of including or excluding the columns based on whether they
> are defined or not defined seems the better solution. However, my own LC
> skills are not at the level where I would know how to make that work
> (although I am sure it can be done). 
> 
> For example, if one were to use the substitution form of revExecuteSQL how
> would you write it so that if one of the fields was undefined it would
> disappear from the statements:
> 
> 
>   put "UPDATE test SET one=:1, two=:2, three=:3 where ID=" & tID into tCmd
>   revExecuteSQL gConnectID, tCmd, "tOne", "tTwo", "tThree"
> 
> I guess this might be easier to do in the merge() form ??? (since you are
> only dealing with 1 string):
> 
>   put "UPDATE test SET " & \
>         merge("one='[[tOne]]', two='[[tTwo]]', three='[[tThree]]'") & \
>         " WHERE id = " & tID into tCmd  -- old format
> 
> But then you have to worry about escaping characters in text fields... etc
> 
> -- Mark
> 
> 
> --
> View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4409110.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