Anomoly when storing empty values into SQLite integer fields

[Pete]

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_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




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