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_sm...@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@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode