Hmm, very odd. I'll need to check what happened again. I use node-sqlite3,
but it also doesn't seem to convert (
https://github.com/mapbox/node-sqlite3/blob/master/src/statement.cc#L274)
On Sat, Jun 30, 2018, 9:44 PM Keith Medcalf <kmedc...@dessus.com> wrote:
>
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
