Hi Mark, I tested this out after emailing you earlier today. It's definitely Livecode that is doing this and it's a serious problem for more reasons than you've found.
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. For anyone interested, here's what I tried. Using LC, I Inserted a record into a table with empty in an integer field. I used sqlite3 (SQLite's command line db admin tool) to select and read it back and it showed no value in the integer field. I then displayed the entry using LC and it showed zero in the integer field, so something somewhere in LC is converting empty to zero for integer columns. That is not good. I also tried supplying a value of NULL for the integer field. When I did this in slqite3, then read the record back, it showed empty in both sqlite3 and Livecode so I thought I had found a way round it. But then I added a record using LC and specifying NULL as the value for the integer field. When I displayed that record in sqlite3, it showed up with a literal value of "NULL", not empty and it still showed up as zero in LC. So LC is treating NULL as a literal value insetad of a keyword on the way in to the database, but converting that literal NULL value to zero on the way back out. That is also not good I think this is a bug. There's no way LC should do any data transformations from how it is stored in a database, and it should recognise all SQL keywords including NULL. Pete On Tue, Feb 21, 2012 at 5:35 PM, Mark Smith <mark_sm...@cpe.umanitoba.ca>wrote: > Hi, I've encountered a bit of an anomaly when storing empty values into > integer fields in SQLite, and wonder if others have observed the same > behavior. If I store an empty value from a LC field into an integer field > in > a database, and then subsequently read it back LC displays it as a 0. Where > this translation occurs is not 100% clear to me, but it certainly is not > desirable (0 and undefined are not the same thing). Has anyone else > encountered this? > > Here's an example: > > put fld "One" into tOne -- One is an empty field, so tOne is empty too > put "UPDATE test SET one=:1 where ID=" & tID into tCmd > revExecuteSQL gConnectID, tCmd, "tOne" > > If the field called one in the database is defined as type int and fld > "one" > on the form is empty then when I execute this: > > put "Select * FROM test where ID = " & tID into tQuery > put revDataFromQuery(,,gConnectID,tQuery) into tRecord > -- display the record > put item 1 of tRecord into fld"ID" > put item 2 of tRecord into fld"one" > > I get a 0 (zero) for the value in fld "one". > > The only way I can see to get around this is to store all numeric values in > text fields because when an "empty" text field is read back its value > remains the same (ie it remains empty). I think LC is doing the translation > on integer type fields but….. I'm not 100% sure of that. > > Your thoughts? (I'm using 5.0.2 for these tests) > > -- Mark > > -- > View this message in context: > http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4408942.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 -- Pete Molly's Revenge <http://www.mollysrevenge.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
