Anomoly when storing empty values into SQLite integer fields

[Bob Sneidar]

What is the field type? What is the default? If numeric and not null then it may be that the value defaults to 0

Bob Sneidar
IT Manager
Calvary Chapel CM
Sent from iPhone

On Feb 21, 2012, at 17:35, Mark Smith <Mark_Smith at 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 at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode