Re: [sqlite] string is converted into a number

[Simon Slavin]

On 14 Jul 2009, at 12:49am, John Machin wrote:

> On 14/07/2009 3:04 AM, Simon Slavin wrote:
>>
>> The reference you were pointed to explains what happens:
>>
>> http://www.sqlite.org/datatype3.html#affinity
>>
>> So you want 'TEXT' ... 'char' doesn't mean anything to SQLite.
>
> @Simon: I'm not sure what you mean by that; see below:
>
> From the quoted URL:
> """
> If the datatype of the column contains any of the strings "CHAR",
> "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the
> type VARCHAR contains the string "CHAR" and is thus assigned TEXT  
> affinity.
> """

Wow, I completely missed that.  Sorry: you're right and I was wrong.

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

Re: [sqlite] string is converted into a number

[John Machin]

On 14/07/2009 3:04 AM, Simon Slavin wrote:
> On 13 Jul 2009, at 4:35pm, Wilfried Mestdagh wrote:
> 
>> But the circumstances are not really described (possible I cannot read
>> between the lines as my English is not perfect). So as far as I  
>> understand
>> the page if I want to store / retrieve a string (which can be a  
>> numeric
>> string) I have to create my field as "char", "text" or as "none". Is  
>> this
>> correct?

@Wilfried: in CREATE TABLE, you should use a data type that contains 
"char", "clob", or "text" (uppercase or lowercase doesn't matter) -- 
that way the column has TEXT affinity which biases SQLite towards 
storing data as TEXT instead of as numbers. See example below.


> 
> The reference you were pointed to explains what happens:
> 
> http://www.sqlite.org/datatype3.html#affinity
> 
> So you want 'TEXT' ... 'char' doesn't mean anything to SQLite.  

@Simon: I'm not sure what you mean by that; see below:

 From the quoted URL:
"""
If the datatype of the column contains any of the strings "CHAR", 
"CLOB", or "TEXT" then that column has TEXT affinity. Notice that the 
type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
"""

Perhaps it's case sensitive? A weird definition of "contains"? Doesn't 
seem so:

SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table x (a text, b varchar, c char, d string);
sqlite> insert into x values('1', '2', '3', '4');
sqlite> select quote(a), quote(b), quote(c), quote(d) from x;
'1'|'2'|'3'|4
sqlite> select typeof(a), typeof(b), typeof(c), typeof(d) from x;
text|text|text|integer
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] string is converted into a number

[Simon Slavin]

On 13 Jul 2009, at 4:35pm, Wilfried Mestdagh wrote:

> But the circumstances are not really described (possible I cannot read
> between the lines as my English is not perfect). So as far as I  
> understand
> the page if I want to store / retrieve a string (which can be a  
> numeric
> string) I have to create my field as "char", "text" or as "none". Is  
> this
> correct?

The reference you were pointed to explains what happens:

http://www.sqlite.org/datatype3.html#affinity

So you want 'TEXT' ... 'char' doesn't mean anything to SQLite.  And  
you also need to pass your values as text too.  If you use

(1, 2, 3, 4)

or use numeric binding, you're passing numeric values into it, and  
they may be manipulated as numbers.  If you pass

('1', '2', '3', '4')

or use string binding, then you're passing text, and if it's stored in  
TEXT fields SQLite won't do anything numeric with it.

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

Re: [sqlite] string is converted into a number

[John Elrick]

Wilfried Mestdagh wrote:
> Hi Dan,
>
> Thank you for your reply. I read:
>
>   
>> Under circumstances described below, the database engine may convert
>> values between numeric storage classes (INTEGER > and REAL) and
>> TEXT during query execution
>> 
>
> But the circumstances are not really described (possible I cannot read
> between the lines as my English is not perfect). So as far as I understand
> the page if I want to store / retrieve a string (which can be a numeric
> string) I have to create my field as "char", "text" or as "none". Is this
> correct?
>   

...or varchar, which is SQL for string.


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

Re: [sqlite] string is converted into a number

[Wilfried Mestdagh]

Hi Dan,

Thank you for your reply. I read:

> Under circumstances described below, the database engine may convert
> values between numeric storage classes (INTEGER > and REAL) and
>TEXT during query execution

But the circumstances are not really described (possible I cannot read
between the lines as my English is not perfect). So as far as I understand
the page if I want to store / retrieve a string (which can be a numeric
string) I have to create my field as "char", "text" or as "none". Is this
correct?

thanks, Wilfried

2009/7/13 Dan 

>
> On Jul 13, 2009, at 3:38 PM, Wilfried Mestdagh wrote:
>
> > Hi,
> >
> > It seems that if I create the table as a field type 'char' instead of
> > 'string' then the issue is solved. But I thought the field type was
> > of non
> > importance?
>
> Details here:
>
>   http://www.sqlite.org/datatype3.html#affinity
>
>
> >
> >
> > rgds, Wilfried
> >
> > 2009/7/13 Wilfried Mestdagh 
> >
> >> Hello,
> >>
> >> I'm using sqlite3.dll and Delphi 7. In certain cirumstances a
> >> string seems
> >> to be converted to a number. To test I use SQLiteSpy from Ralf
> >> Junker wich
> >> is a nice tool.
> >>
> >> When I do this:
> >> insert into Queue (NetworkID) values ("200907130833123740007")
> >>
> >> Then the result of the field NetworkID is:
> >> 2.00907130833124E20
> >>
> >> To solve this I add a blank in front of the ID, like this:
> >> insert into Queue (NetworkID) values (" 200907130833123740007")
> >>
> >> But is this a good method, and is this a known issue?
> >>
> >> The table is created like this:
> >> 'create table Queue (' +
> >>   '[NetworkID] string, ' +
> >>   '[State] integer, ' +
> >>   // Etc...
> >>
> >> --
> >> mvg, Wilfried
> >> http://www.mestdagh.biz
> >>
> >
> >
> >
> > --
> > mvg, Wilfried
> > http://www.mestdagh.biz
> > ___
> > 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
>



-- 
mvg, Wilfried
http://www.mestdagh.biz
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] string is converted into a number

[Dan]

On Jul 13, 2009, at 3:38 PM, Wilfried Mestdagh wrote:

> Hi,
>
> It seems that if I create the table as a field type 'char' instead of
> 'string' then the issue is solved. But I thought the field type was  
> of non
> importance?

Details here:

   http://www.sqlite.org/datatype3.html#affinity


>
>
> rgds, Wilfried
>
> 2009/7/13 Wilfried Mestdagh 
>
>> Hello,
>>
>> I'm using sqlite3.dll and Delphi 7. In certain cirumstances a  
>> string seems
>> to be converted to a number. To test I use SQLiteSpy from Ralf  
>> Junker wich
>> is a nice tool.
>>
>> When I do this:
>> insert into Queue (NetworkID) values ("200907130833123740007")
>>
>> Then the result of the field NetworkID is:
>> 2.00907130833124E20
>>
>> To solve this I add a blank in front of the ID, like this:
>> insert into Queue (NetworkID) values (" 200907130833123740007")
>>
>> But is this a good method, and is this a known issue?
>>
>> The table is created like this:
>> 'create table Queue (' +
>>   '[NetworkID] string, ' +
>>   '[State] integer, ' +
>>   // Etc...
>>
>> --
>> mvg, Wilfried
>> http://www.mestdagh.biz
>>
>
>
>
> -- 
> mvg, Wilfried
> http://www.mestdagh.biz
> ___
> 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] string is converted into a number

[Wilfried Mestdagh]

Hi,

It seems that if I create the table as a field type 'char' instead of
'string' then the issue is solved. But I thought the field type was of non
importance?

rgds, Wilfried

2009/7/13 Wilfried Mestdagh 

> Hello,
>
> I'm using sqlite3.dll and Delphi 7. In certain cirumstances a string seems
> to be converted to a number. To test I use SQLiteSpy from Ralf Junker wich
> is a nice tool.
>
> When I do this:
> insert into Queue (NetworkID) values ("200907130833123740007")
>
> Then the result of the field NetworkID is:
> 2.00907130833124E20
>
> To solve this I add a blank in front of the ID, like this:
> insert into Queue (NetworkID) values (" 200907130833123740007")
>
> But is this a good method, and is this a known issue?
>
> The table is created like this:
> 'create table Queue (' +
>'[NetworkID] string, ' +
>'[State] integer, ' +
>// Etc...
>
> --
> mvg, Wilfried
> http://www.mestdagh.biz
>



-- 
mvg, Wilfried
http://www.mestdagh.biz
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] string is converted into a number

[Wilfried Mestdagh]

Hello,

I'm using sqlite3.dll and Delphi 7. In certain cirumstances a string seems
to be converted to a number. To test I use SQLiteSpy from Ralf Junker wich
is a nice tool.

When I do this:
insert into Queue (NetworkID) values ("200907130833123740007")

Then the result of the field NetworkID is:
2.00907130833124E20

To solve this I add a blank in front of the ID, like this:
insert into Queue (NetworkID) values (" 200907130833123740007")

But is this a good method, and is this a known issue?

The table is created like this:
'create table Queue (' +
   '[NetworkID] string, ' +
   '[State] integer, ' +
   // Etc...

-- 
mvg, Wilfried
http://www.mestdagh.biz
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users