On Tue, Jul 6, 2010 at 8:45 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>>> The receiving field is defined as CHAR; [snip]
>> SQLite has no such type. Define the fields as TEXT instead:
>
> Simon, please don't confuse poor users. SQLite will work perfectly and
> indistinguishably well with both CHAR and TEXT. Please read the link
> you gave more carefully (hint: bullet number 2 in the section 2.1).
>
>> Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.
>
> And your propose is nothing better than original one. The problem is
> you both are trying to insert into text field a number. Leading zeros
> in the number can never be significant, so they are trimmed before
> this number is converted to text. The solution is to put single quotes
> around anything that supposed to be treated as text.
Well, in defense of Simon, he is partially correct, in that, setting
the right type would help. However, he errs in that he does not advise
the OP to use single quotes to delimit the string. Consider the
following --
sqlite> CREATE TABLE UPCs (UPC VLADIVOSTOK);
sqlite> INSERT INTO UPCs VALUES ('043000205563');
sqlite> SELECT * FROM UPCs;
UPC
-----------
43000205563
sqlite> SELECT Typeof(UPC) FROM UPCs;
Typeof(UPC)
-----------
integer
sqlite> .s
CREATE TABLE UPCs (UPC VLADIVOSTOK);
sqlite> INSERT INTO UPCs VALUES ('foobar');
sqlite> SELECT Typeof(UPC) FROM UPCs;
Typeof(UPC)
-----------
integer
text
sqlite>
So, because type VLADIVOSTOK is not recognized, sqlite tries to
convert any value entered, even if it is delimited with single quotes,
to something recognizable. I guess it starts with INT, and since it is
able to convert '043000205563' to integer, that is what it does. In
the case of 'foobar', it can't convert it to integer, so it converts
it to a text string.
At least, that is how I understand it.
This whole type and affinity thing seems to be the source of much
confusion for many folks. I am sure I would also be confused by it if
I spent more than a moment on it. Since I do all my checks for data in
and out in my application, I really don't ever bother with this stuff,
but I wonder if there were some way to reduce this confusion in the
minds of others.
>
>
> Pavel
>
> On Tue, Jul 6, 2010 at 6:17 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>>
>> On 6 Jul 2010, at 11:10pm, Ted Rolle, Jr. wrote:
>>
>>> The receiving field is defined as CHAR; [snip]
>>
>> SQLite has no such type. Define the fields as TEXT instead:
>>
>> <http://www.sqlite.org/datatype3.html>
>>
>> Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.
>>
>>> Last question: is this an example of SQLite's "typelessness"?
>>
>> SQLite has types. It just doesn't require every value in the same column to
>> be of the same type.
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
