Thanks! this helped me a lot in understanding the red flags. I think I have read the 'type affinity' documentation pages quite a few times (including this morning).
Il giorno mer 31 lug 2019 alle ore 12:05 Olivier Mascia <[email protected]> ha scritto: > > Le 31 juil. 2019 à 10:03, Barone Ashura <[email protected]> a > écrit : > > > > CREATE TABLE "VALUES" ( > > id INTEGER PRIMARY KEY ASC, > > value_int INTEGER, > > value_double REAL, > > value_text TEXT > > ); > > > > and that I want to execute the following simple query: > > > > SELECT * FROM "VALUES"; > > > > For this very specific query, are there circumstances where the call > > to sqlite3_column_type, for column 'value_int', returns a result > different > > from > > SQLITE_INTEGER, > > Yes. > Nothing stops any piece of your own programming or anyone using the CLI to > do: > > INSERT INTO "VALUES"(value_int) VALUES('something'); > > > The sqlite3_column_type for the column value_int will tell you SQLITE_TEXT > because: > > See: https://www.sqlite.org/c3ref/column_blob.html > > "The sqlite3_column_type() routine returns the datatype code for the > initial data type of the result column." > > The "initial data type" is NOT the one declared in the schema. It is the > type of the actual stored value in that particular column of that > particular row. SQLite is mostly type agnostic regarding storage. The > declared type in the schema only serves as a guideline for some automatic > conversion where applicable. SQLite calls this: 'affinity'. > > See: https://www.sqlite.org/datatype3.html#type_affinity > > INSERT INTO "VALUES"(value_int) VALUES(123); > and > INSERT INTO "VALUES"(value_int) VALUES('123'); > > will both actually stores a SQLITE_INTEGER because the column has a type > whose name starts with INT and the string '123' can safely be converted > from ascii to integer and reciprocal. > > But INSERT INTO "VALUES"(value_int) VALUES('something'); > will actually store the string 'something' because it can't be converted > back and forth between text and integer. > > — > Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten > Grüßen, > Olivier Mascia > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
