> Best is to define type on output Yes, looks that way. As you can see the alias can help, eg: select max(integer_date_column) as integer_date_column
what I also added is coding to pick up the intended formatting from the alias, not from the column but by just specifying the output datatype: select max(integer_date) as int_date All this is only needed if sqlite3_column_decltype produces null. Looks kind of sorted now, thanks. RBS On Mon, Jan 8, 2018 at 10:29 AM, R Smith <ryansmit...@gmail.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
