On 2018/01/08 12:00 AM, Bart Smissaert wrote:
OK, I can see your point and I am sure you are right.
All this has to do with the question (discussed not long ago) how one
should know how the output from a statement should be handled by
an application receiving the data. In my case that application is Excel.
Take for example an integer. Excel need to know if this integer should
be formatted as a plain integer or as an Excel date. When the user moves
data to SQLite (from the sheet or from eg a text file) he/she will tell how
this data should be stored. In this case the data type will be INTEGER or
INT_DATE. This information will be stored in SQLite_master. The question
is now how to match up the columns of the output with these data types in
SQLite_master. I have a simple system for this with sqlite3_column_type
and sqlite3_column_decltype etc. but this is just not fool proof. It looks
I will need to think up some more elaborate system. One option is that the
user
just tells (along with the statement) what the output formatting should be,
but that would be cumbersome.
Yeah, that is a tough one.
Even in other databases you won't be able to achieve this. "Type" is
used mostly as an internal specification and sometimes output
formatter. Preserving the original column type through query
abstraction to the output is simply not possible, especially if that
type is homemade. You are interested in an output type, it should be
specified within the output.
Best is to define type on output. You might decrease cumbersomeness a
lot if the list of types is small, using perhaps just a one or two
character type identifier, like this:
SELECT foo AS Col1_i, bar AS Col2_d.... etc.
(Where i denotes INT and d is INT-DATE etc.)
If you expect the list of types to grow in future, pick slightly longer
identifiers.
This of course would not be feasible if you (as in your collective devs)
do not control the resulting queries, like if users can make queries on
the fly....
Another method is keeping a table with names and types that the
application can read. Add to it all the names used for output and the
types implied. Say it contains the name Col1 with Type INT then whenever
your application reads a query where the column name is Col1 (via origin
or alias) then it knows that is an INT. This is the saddest method and
should be avoided if possible.
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
