Re: [sqlite] 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  wrote:

> On 1 Apr 2018, at 8:19am, Eric Grange  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
> 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

Re: [sqlite] sqlite3_column_decltype and affinity

[Simon Slavin]

On 1 Apr 2018, at 8:19am, Eric Grange  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> On 30 Mar 2018, at 11:22am, Eric Grange  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
> 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

Re: [sqlite] sqlite3_column_decltype and affinity

[Simon Slavin]

On 30 Mar 2018, at 11:22am, Eric Grange  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_column_decltype and affinity

[Eric Grange]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users