On 12/13/19 5:49 PM, František Kučera wrote: > Hello, > > 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? > > The sqlite3_column_decltype() works only if I select directly a column, but > not when I do some other operations (call function, increment etc.). > > 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. > > 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… > > Or will SQLite4 work differently (for me better) with types? > > Thanks, > > Franta > My guess is that your statement '(expecting that all values in that column will have same type)' is where your problems arise. The SQLite model doesn't assume that. A column in a result set doesn't necessarily have *A* type. What do you want you software to do if the data in the database has differing types in a given column?
If you are going to enforce a uniform type (excepting allowing NULL as a value), then you could check your recorded column type for each column for each row, and if you have it currently recorded as NULL, check the type in this row and update if needed, otherwise use the recorded type. Note that you need to be prepared for different queries of the same set of columns (or the same query at different times) may give you changing types for a given column at different times. -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
