When you declare a column with no affinity (that is with blob or none
affinity), the data is stored precisely and exactly as presented with no
conversions performed by SQLite3. You give it a character string, it stores a
character string. You give it an integer, it stores an integer. You give it a
real it stores it as a real. You give it a bag-o-bytes, it is stored as a
bag-o-bytes. If you give it something that can be converted (ie, a text
representation of an integer) the data presented in stored (the text string).
It is not converted.
sqlite> create table x(x primary key);
sqlite> insert into x values ('1234567890');
sqlite> insert into x values ('12345678901234567890');
sqlite> insert into x values ('1');
sqlite> insert into x values ('1.0');
sqlite> insert into x values (1);
sqlite> insert into x values (2.0);
sqlite> select x, typeof(x) from x;
1234567890|text
12345678901234567890|text
1|text
1.0|text
1|integer
2.0|real
You were being "helped" by something other than SQLite3 because SQLite3 does
not behave in the manner you described.
See
https://www.sqlite.org/datatype3.html#determination_of_column_affinity
in particular rule #3
See also the sentence immediately preceding that section:
"A column with affinity BLOB does not prefer one storage class over another and
no attempt is made to coerce data from one storage class into another."
Nor is there an SQLite3 API which will permit you to retrieve the data by
"magical types". You ask for the datatype you want to receive and SQLite3 will
carry out the conversions necessary to meet your request, or; if you do not
want any conversions, then you first ask what the data storage format of the
item is, and then ask for the data to be returned in that format.
Since you are not interacting directly with the SQLite3 C API, whomever wrote
whatever it is that you are using included a bunch-o-magic which either is not
documented, or that you did not read and therefore you assumed the issue you
saw was SQLite3 when in fact it was the third-party interface wrapper.
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens
>Sent: Saturday, 30 June, 2018 11:44
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>story time:
>
>I was storing rows with text id's and never bothered setting a type,
>it was
>"id PRIMARY KEY" and I always assumed that it gave me back what I
>stored in
>it via the nodejs binding.
>
>One day I was storing a string of numbers which happened to fit in a
>64 bit
>int, and so sqlite stored them as integers, and when retrieving them,
>they
>became floats (since JS doesn't have 64 bit ints (yet)).
>
>That was a headscratcher. Now I explicitly set the type, always.
>
>
>On Sat, Jun 30, 2018, 11:19 AM Olivier Mascia <o...@integral.be> wrote:
>
>> > Le 30 juin 2018 à 09:04, Thomas Kurz <sqlite.2...@t-net.ruhr> a
>écrit :
>> >
>> > CREATE TABLE a (col1 STRING);
>> > INSERT INTO a (col1) VALUES ("3.0");
>> > SELECT * from a;
>> > ---> 3 // this should never happen!!
>>
>> SQLite type affinity rules clearly do not recognise STRING as TEXT:
>it
>> does so only when the type contains the words CHAR, TEXT or CLOB.
>STRING,
>> which you use for your example, is even specifically warned about
>(being of
>> NUMERIC affinity).
>>
>>
>https://www.sqlite.org/datatype3.html#determination_of_column_affinit
>y
>>
>> --
>> Best Regards, Meilleures salutations, Met vriendelijke groeten,
>> Olivier Mascia
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
