Determine SQLite data type after UDF conversion

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

Determine SQLite data type after UDF conversion

Bart Smissaert
Say I have a query like this:

Select tbl, BlobAsText(sample) from sqlite_stat4
Where BlobAsText is a UDF that takes a blob and converts it to a string.
Now I need to know that the second column of the output needs to be dealt
with as text.
Is there any SQLite API that can help me with this?

sqlite3_column_type and sqlite3_column_decltype can't tell me this
information.
I could work it out by parsing the SQL or by looking at
sqlite3_column_name, but having the UDF name in the column_name doesn't
necessarily mean that the output of that column is text.
One way to solve this would be to put this information in the field alias,
eg:

Select tbl, BlobAsText(sample) as BLOB_IS_TEXT from sqlite_stat4, but that
is quite restrictive.
Any suggestions what the best way is to solve this problem?
I do have full control of the application code (the SQLite wrapper).


RBS
_______________________________________________
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: Determine SQLite data type after UDF conversion

R Smith
Not 100% sure I follow what you mean to achieve, but would:

Select tbl, CAST(BlobAsText(sample) AS TEXT) AS SampleText from sqlite_stat4

work for you?

On 2017/08/30 1:20 PM, Bart Smissaert wrote:

> Say I have a query like this:
>
> Select tbl, BlobAsText(sample) from sqlite_stat4
> Where BlobAsText is a UDF that takes a blob and converts it to a string.
> Now I need to know that the second column of the output needs to be dealt
> with as text.
> Is there any SQLite API that can help me with this?
>
> sqlite3_column_type and sqlite3_column_decltype can't tell me this
> information.
> I could work it out by parsing the SQL or by looking at
> sqlite3_column_name, but having the UDF name in the column_name doesn't
> necessarily mean that the output of that column is text.
> One way to solve this would be to put this information in the field alias,
> eg:
>
> Select tbl, BlobAsText(sample) as BLOB_IS_TEXT from sqlite_stat4, but that
> is quite restrictive.
> Any suggestions what the best way is to solve this problem?
> I do have full control of the application code (the SQLite wrapper).
>
>
> RBS
> _______________________________________________
> 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: Determine SQLite data type after UDF conversion

Richard Hipp-3
In reply to this post by Bart Smissaert
On 8/30/17, Bart Smissaert <[hidden email]> wrote:

> Say I have a query like this:
>
> Select tbl, BlobAsText(sample) from sqlite_stat4
> Where BlobAsText is a UDF that takes a blob and converts it to a string.
> Now I need to know that the second column of the output needs to be dealt
> with as text.
> Is there any SQLite API that can help me with this?
>
> sqlite3_column_type and sqlite3_column_decltype can't tell me this
> information.

sqlite3_column_type() should be returning SQLITE_TEXT, unless there is
something wrong with your UDF.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Determine SQLite data type after UDF conversion

Bart Smissaert
OK, that is very helpful.
I must have misunderstood the documentation about this:

The sqlite3_column_type() routine returns the datatype code for the initial
data type of the result column.  etc>
I will just check my application code and there must be something wrong
there then.
I am sure my UDF is OK.

RBS






On Wed, Aug 30, 2017 at 12:27 PM, Richard Hipp <[hidden email]> wrote:

> On 8/30/17, Bart Smissaert <[hidden email]> wrote:
> > Say I have a query like this:
> >
> > Select tbl, BlobAsText(sample) from sqlite_stat4
> > Where BlobAsText is a UDF that takes a blob and converts it to a string.
> > Now I need to know that the second column of the output needs to be dealt
> > with as text.
> > Is there any SQLite API that can help me with this?
> >
> > sqlite3_column_type and sqlite3_column_decltype can't tell me this
> > information.
>
> sqlite3_column_type() should be returning SQLITE_TEXT, unless there is
> something wrong with your UDF.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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