Anomoly when storing empty values into SQLite integer fields

[Mark Smith]

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.