Re: [sqlite] Numbers as CHARs.

[Ted Rolle, Jr.]

On Tue, 6 Jul 2010 17:13:44 -0500
P Kishor  wrote:
> I have no idea why you would say that. It works just fine.
> 
> sqlite> CREATE TABLE UPCs (UPC TEXT);
> sqlite> INSERT INTO UPCs VALUES ('043000205563');
> sqlite> SELECT * FROM UPCs;
> UPC
> 
> 043000205563
> sqlite>

Oh, by the way, '043000205563' is Jell-O Chocolate Fudge Pie Filling.
I don't believe that has any bearing on the problem, though. :-)

Ted
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Numbers as CHARs.

[Jay A. Kreibich]

On Tue, Jul 06, 2010 at 09:45:50PM -0400, Pavel Ivanov scratched on the wall:

> Leading zeros in the number can never be significant,

  While that's true for SQL values, it isn't true in the general case.
  C programmers and old-school UNIX folks tend to get very nervous
  about leading zeros.  Consider:
  
int main( ) { printf( "%d\n", 0123 ); }

  (hint: it prints "83").  Unless you know the application that is
  processing your input values very well, it pays not to make too many
  assumptions.

  For example, do you know the value of "i" and "d" in these statements?
  Without looking at the docs?  Do you think most programmers do?
  Do you think they usually get it "right" ?

  int i, d;
  sscanf( "0123", "%i",  );
  sscanf( "0123", "%d",  );

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Numbers as CHARs.

[Pavel Ivanov]

> 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  wrote:
> On Tue, Jul 6, 2010 at 8:45 PM, Pavel Ivanov  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  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:
>>>
>>> 
>>>
>>> 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
> 

Re: [sqlite] Numbers as CHARs.

[P Kishor]

On Tue, Jul 6, 2010 at 8:45 PM, Pavel Ivanov  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  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:
>>
>> 
>>
>> 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

Re: [sqlite] Numbers as CHARs.

[Pavel Ivanov]

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


Pavel

On Tue, Jul 6, 2010 at 6:17 PM, Simon Slavin  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:
>
> 
>
> 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

Re: [sqlite] Numbers as CHARs.

[Ted Rolle, Jr.]

On Tue, 6 Jul 2010 17:13:44 -0500
P Kishor  wrote:

> sqlite> CREATE TABLE UPCs (UPC TEXT);
> sqlite> INSERT INTO UPCs VALUES ('043000205563');
> sqlite> SELECT * FROM UPCs;
> UPC
> 
> 043000205563
> sqlite>

I did as you said with sqlite and it worked perfectly.  Thank you.

This is probably out of the group's purview, but can it be a problem
with SQLite Manager?

Ted
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Numbers as CHARs.

[Simon Slavin]

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:



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

Re: [sqlite] Numbers as CHARs.

[P Kishor]

On Tue, Jul 6, 2010 at 5:10 PM, Ted Rolle, Jr.  wrote:
> I, (or more to the point, SQLite) can't seem to retain leading zeros
> on numbers.
>
> The receiving field is defined as CHAR;
> I'm using the SQLite Manager in Firefox.
> I've also tried sqlite3 from the command line.
> Here's a typical (and minimal) statement:
> UPDATE UPCs SET UPC=043000205563;
> UPDATE UPCs SET UPC=CAST(043000205562 AS CHAR) WHERE rowid=93; didn't
> work either.
>
> SELECT TYPEOF(UPC) FROM UPCs WHERE rowid=93; returns 'text'.
>
> I'm reluctant to put quotes (single or double) around the values as
> they are retained in the field.
>

I have no idea why you would say that. It works just fine.

sqlite> CREATE TABLE UPCs (UPC TEXT);
sqlite> INSERT INTO UPCs VALUES ('043000205563');
sqlite> SELECT * FROM UPCs;
UPC

043000205563
sqlite>



> On other text/numeric fields I've added a textual value; that seems to
> "fix" the problem.  But not really.
>
> Last question: is this an example of SQLite's "typelessness"?
>
> Ted
> --
> __
> 3.14159265358979323846264338327950      Let the spirit of pi
> 2884197169399375105820974944592307   spread all over the world!
> 8164062862089986280348253421170679 http://pi314.at  PI VOBISCUM!
> ==
> ___
> 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] Numbers as CHARs.

[Ted Rolle, Jr.]

I, (or more to the point, SQLite) can't seem to retain leading zeros
on numbers.

The receiving field is defined as CHAR;
I'm using the SQLite Manager in Firefox.
I've also tried sqlite3 from the command line.
Here's a typical (and minimal) statement:
UPDATE UPCs SET UPC=043000205563;
UPDATE UPCs SET UPC=CAST(043000205562 AS CHAR) WHERE rowid=93; didn't
work either.

SELECT TYPEOF(UPC) FROM UPCs WHERE rowid=93; returns 'text'.

I'm reluctant to put quotes (single or double) around the values as
they are retained in the field.

On other text/numeric fields I've added a textual value; that seems to
"fix" the problem.  But not really.

Last question: is this an example of SQLite's "typelessness"?

Ted
-- 
__
3.14159265358979323846264338327950  Let the spirit of pi
2884197169399375105820974944592307   spread all over the world!
8164062862089986280348253421170679 http://pi314.at  PI VOBISCUM!
==
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users