Anomoly when storing empty values into SQLite integer fields

Mark Smith Mark_Smith at cpe.umanitoba.ca
Tue Feb 21 20:35:04 EST 2012


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.




More information about the use-livecode mailing list