sqlite3_column_decltype and affinity

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3_column_decltype and affinity

Eric Grange-3
Hi,

Is there a way to have sqlite3_column_decltype return the affinity for an
expression ?

https://www.sqlite.org/datatype3.html#affinity_of_expressions

states that a CAST can be used to specify the affinity of a column, however
sqlite3_column_decltype does not report that affinity, as is documented in

https://www.sqlite.org/c3ref/column_decltype.html

I would like to have the affinity for purposes of presenting the results of
an SQL query (for column display width etc.).
Thanks!

Eric
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and affinity

Simon Slavin-3
On 30 Mar 2018, at 11:22am, Eric Grange <[hidden email]> wrote:

> Is there a way to have sqlite3_column_decltype return the affinity for an
> expression ?

You may be referring to

    sqlite3_column_type()

which can be applied to columns returned by a query even if that column is an expression.  But if you want to do it accurately for every row it can't be done at the column level, because an expression has a datatype and not an affinity, and different rows of the same expression might have a different type:

SELECT month,
       CASE weekday
           WHEN 6 THEN 'weekend'
           WHEN 7 THEN 'weekend'
           ELSE weekday
       END
       FROM deliverydates;

To handle that properly I think you'd have to call sqlite3_value_type() for each value returned.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and affinity

Eric Grange
Yes, I know about column_type, but it returns only integer/text/etc.

The decl_type allows to differentiate a datetime, or a "wide" text from a
single char text column, etc. which are all useful for presentation
purposes when the fields have been declared properly.
I was hoping to be able to recover that info whe it is provide through a
cast.

On Fri, Mar 30, 2018 at 6:04 PM, Simon Slavin <[hidden email]> wrote:

> On 30 Mar 2018, at 11:22am, Eric Grange <[hidden email]> wrote:
>
> > Is there a way to have sqlite3_column_decltype return the affinity for an
> > expression ?
>
> You may be referring to
>
>     sqlite3_column_type()
>
> which can be applied to columns returned by a query even if that column is
> an expression.  But if you want to do it accurately for every row it can't
> be done at the column level, because an expression has a datatype and not
> an affinity, and different rows of the same expression might have a
> different type:
>
> SELECT month,
>        CASE weekday
>            WHEN 6 THEN 'weekend'
>            WHEN 7 THEN 'weekend'
>            ELSE weekday
>        END
>        FROM deliverydates;
>
> To handle that properly I think you'd have to call sqlite3_value_type()
> for each value returned.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and affinity

Simon Slavin-3
On 1 Apr 2018, at 8:19am, Eric Grange <[hidden email]> wrote:

> The decl_type allows to differentiate a datetime, or a "wide" text from a
> single char text column

The reason descl_type does not work on expressions is the reason I illustrated in my previous post: the expression can have a different type for different rows of the result and therefore has no affinity throughout the entire set of results.

You could, perhaps, check the types of the first row of results, and assume that all rows will have the same types.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_column_decltype and affinity

Eric Grange
I understand the type can differ between rows, but that's also the case for
a regular table, and
https://www.sqlite.org/datatype3.html#affinity_of_expressions says

   << An expression of the form "CAST(expr AS type)" has an affinity that
is the same as a column with a declared type of "type". >>

so the sql engine already has to keep track of the affinity specified
through "cast()" if I understood the above correctly

On Sun, Apr 1, 2018 at 3:19 PM, Simon Slavin <[hidden email]> wrote:

> On 1 Apr 2018, at 8:19am, Eric Grange <[hidden email]> wrote:
>
> > The decl_type allows to differentiate a datetime, or a "wide" text from a
> > single char text column
>
> The reason descl_type does not work on expressions is the reason I
> illustrated in my previous post: the expression can have a different type
> for different rows of the result and therefore has no affinity throughout
> the entire set of results.
>
> You could, perhaps, check the types of the first row of results, and
> assume that all rows will have the same types.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users