> 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.


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

Reply via email to