> 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
