Anomoly when storing empty values into SQLite integer fields

Pete pete at mollysrevenge.com
Tue Feb 21 22:13:19 EST 2012


Yes, it causes a headache, you're right.  You would have to  build the
UPDATE command in a variable by examining each field for empty and adding
it to the variable list if not empty.  Don't have time to give some example
code right now, but will try to do something tomorrow.
Pete




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


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



More information about the use-livecode mailing list