Anomoly when storing empty values into SQLite integer fields

[Bob Sneidar]

I just tested this with a numeric column in mySQL. The column has NOT NULL unchecked and the default value set to NULL. When I updated from LC setting the column to NULL that is the value that the column was set to. HOWEVER if I updated the column with an empty string, I got 0 just as you say. We good so far? Okay. 

So I wanted to see what would happen if I bypassed LC altogether and did the same updates in a mySQL utility. I got THE EXACT SAME RESULTS!!! When I updated a numeric column with an empty string, I got the number 0. What does this all mean? It means that LC is NOT borking the update. It is just the way SQL reacts when you send an empty string to a numeric field! 

They only real issue left is that LC converts a NULL value to an empty string in it's queries for the reason I explained, that LC abhors an ASCII 0 in a string. I hope I have made this clear now. I have screen snapshots to verify my results if you would like me to send them to you off list. 

Bob


On Feb 22, 2012, at 4:53 PM, Pete wrote:

> Bob,
> Sorry but you're wrong on all counts.  Read my earlier mails for info  In
> particular, LC is not retuning empty for a NULL value in an integer column
> - it's returning zero, that's where this whole mess started!!!  I am quite
> happy for it to return empty for a NULL value but that's not what is
> happening, at least for integer fields.
> 
> On Wed, Feb 22, 2012 at 1:27 PM, Bob Sneidar <bobs at twft.com> wrote:
> 
>> Ok. But if it really were the string value "NULL" that gets saved to the
>> database, wouldn't you get "NULL" in your select statement?? Try using a
>> lowercase null in your update statement, then view the sqLite table with a
>> utility to see what it says the value is. If it's capital NULL it is
>> actually the NULL character.
>> 
>> LC is not going to reinterpret a string value as empty just because the
>> word "NULL" was what the value was. This seems to indicate that the update
>> command actually DID save the NULL properly. As for a select statement
>> returning empty string for a NULL value, this was discussed some time ago
>> when I was first getting into database access from LC. The idea is that LC
>> never wanted to return an ASCII 0 character in ANY string because it tended
>> to wreak havoc with displaying text in fields and other objects. I believe
>> that an empty string is precicely what the RunRev people WANT to return for
>> a NULL value.
>> 
>> Sorry, I don't mean to sound argumentative, but it "seems" to me that it
>> is doing what it was at least designed to do.
>> 
> 
> 
> 
> -- 
> Pete
> Molly's Revenge <http://www.mollysrevenge.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