> Le 31 juil. 2019 à 10:03, Barone Ashura <bar0n3ash...@gmail.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
