On Friday, 13 December, 2019 15:49, František Kučera <konfere...@frantovo.cz>
wrote:
>I know that SQLite uses dynamic types, so it is not easy… But what is the
>best way to determine the column type of a result set?
Result sets do not have "column types". Each result value (the intersection of
row and column) has a type associated with it.
>The sqlite3_column_decltype() works only if I select directly a column,
>but not when I do some other operations (call function, increment etc.).
See above comment. The declared type of a column has no bearing on what type
of data is stored in the column.
>The sqlite3_column_type() works for while iterating over particular rows.
>I can fetch the first row and get type here (expecting that all values in
>that column will have same type), but the problem is a) if the first
>value is NULL or b) if the result set is empty.
Your expectation would be incorrect. You have to call sqlite3_column_type for
each column of each row in order to determine the type of data contained there.
Every intersection of row and column can contain data of any type. Think of
an Excel (or VisiCalc) spreadsheet. Just because you labeled a column as
containing only integers does not mean that someone did not put something else
there. You only know what type of data is contained in a cell by looking and
asking using the sqlite3_column_type interface. Every time.
Although I suppose you could write a check constraint for each column to ensure
that only "approved" datatypes are stored in a column:
sqlite> create table x(x integer check (typeof(x) in ('integer', 'null')));
sqlite> insert into x values (null);
sqlite> insert into x values (1);
sqlite> insert into x values (1.0);
Error: CHECK constraint failed: x
sqlite> insert into x values ('1');
Error: CHECK constraint failed: x
sqlite> insert into x values (X'41');
Error: CHECK constraint failed: x
but most databases/tables will not have done that, so you need to check for
EACH value.
>If I have e.g. "SELECT a + 1 AS x FROM t", is there a way to say that the
>x will be numeric? Or if I have "SELECT 1+1 AS x"?
>I am writing a generic software that should work with any database model
>(tables are defined by the user) and I need to know the types, so I can
>properly present the results. Currently I use sqlite3_column_decltype()
>and will add options so the user could explicitly specify types of
>particular columns, but I am looking for a better way…
You need to call sqlite3_column_type on each column of each row to determine
what type of data is contained therein.
>Or will SQLite4 work differently (for me better) with types?
There is no SQLite4, it was cancelled. SQLite3 is strongly typed, just those
types are dynamic.
https://sqlite.org/datatype3.html
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
