Dne 14. 12. 19 v 0:09 Keith Medcalf napsal(a): > 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.
I know that SQLite is that flexible, but I am using just a subset of its features and looking for a way how to propagate the types through the queries and result sets. > 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. Yes, I can do: sqlite> create table t (i integer, f float, s text); sqlite> insert into t values (1, 1.0, 'abc'); sqlite> insert into t values (1.5, 1.0, 'abc'); sqlite> insert into t values (1.5, 'xxx', 'abc'); sqlite> select * from t; 1|1.0|abc 1.5|1.0|abc 1.5|xxx|abc sqlite> select typeof(i), typeof(f), typeof(s) from t; integer|real|text real|real|text real|text|text but this would be unsupported scenario and the user will expect, that if he declares an integer column, he could put only integers into it. If the types become into such inconsistent state, then my software could crash, throw exception, refuse working… it is OK (user can recover his data directly through SQLite, but it is not expected that this would happen). In case of my software I can really expect that all values in a column will have the same type or be NULL (and everything else means error). So if I have a table with an integer column and I do e.g. a +1 operation on it, the database has theoretically everything it needs to say that the resulting type will be also integer. Or can the "+" operation result anything than number (or null or error)? Would not it be useful to have optional function to determine the types before executing the query? (yes, it would require that the columns contains only values of declared type… but it is quite obvious and who call such function will expect such constraint… and it might also require adding some optional metadata to existing functions – hint what types they return…). Franta _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
