> As you can see

Should read:
As you said

RBS

On Mon, Jan 8, 2018 at 11:17 AM, Bart Smissaert <bart.smissa...@gmail.com>
wrote:

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

Reply via email to