There is sqlite3_column_origin_name( stmt, n ) ; is that also the alias? instead of sqlite3_column_name(stmt,n ); or does there need to be sqlite3_column_original that really returns the not alias?
On Mon, Jan 8, 2018 at 3:21 AM, Bart Smissaert <bart.smissa...@gmail.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
