On 12/13/19 7:16 PM, František Kučera wrote: > 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
As I said in my reply, you can keep asking for the type of columns that have returned NULL previously to get the type. Part of the issue is that you ARE allowing multiple types (since NULL is its own type), so you need to be prepared for differing types. One big thing to watch out is that columns of NUMERIC type can easily return values of either INTEGER or REAL type. Your single type expectation is easily broken here. I also don't know if 9223372036854775807 (the biggest integer value) from an INTEGER field + 1 gives a REAL result, or some incorrect INTEGER value. -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
