> 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.
Read that document again. :) This case is bullet 5 in section 2.1,
i.e. completely unrecognized type has NUMBER affinity which by
definition tries to convert everything first to integer, then if
conversion to integer fails it tries to convert to double, then if
that fails too it inserts what is given.
> Well, in defense of Simon, he is partially correct, in that, setting
> the right type would help.
You should give the definition of "right" then. My definition is as
follows: "right" type is the one that allows to achieve intended
functionality. With that in mind CHAR and TEXT are both "right", as
well as VARCHAR or VARCHAR(256). Everyone can use what he is used to.
Pavel
On Tue, Jul 6, 2010 at 10:09 PM, P Kishor <punk.k...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
