Re: [sqlite] Map a field type to a best suited sqlite3_result_* call

[J Decker]

On Sat, May 5, 2018 at 4:08 AM, Max Vlasov  wrote:

> On Sat, May 5, 2018 at 1:19 PM, J Decker  wrote:
>
> > https://www.sqlite.org/c3ref/column_blob.html
> >
> > The sqlite3_column_type() routine returns the datatype code
> >
>
>
> Column api is not suitable for me since it relies on the actual data
> provided, but I'm the one who calls sqlite3_result_* to provide this.
>

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

sqlite_result_value the valuer type itself has the type.  This is the type
that would be returned by sqlite_column_types().

If you're serving the data, then you know what the data type is.  if you
don't you need to store it so you do; and this is outside of sqlite.



>
>
> >
> > PRAGMA table_info(table1);
> >
>
> The pragma just returns the type part as it was provided by the virtual
> table module with schema data. No conversion or mapping is made. But it
> will help be to extract the type part correctly if the provided list
> comments for example .
>   [FieldA] /*This is fieldA */ TEXT, [FieldB] FLOAT
>
>
> Something also came up as a solution during the reading of this post. The
> query
>   Select typeof(cast('' as ))
> doesn't require any table so I just can replace  with a provided
> column type and get the best affinity.
> But for any unrecognized it will return "integer", but I'd rather have
> "text".
>
> Max
> ___
> 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] Map a field type to a best suited sqlite3_result_* call

[Max Vlasov]

On Sat, May 5, 2018 at 1:19 PM, J Decker  wrote:

> https://www.sqlite.org/c3ref/column_blob.html
>
> The sqlite3_column_type() routine returns the datatype code
>


Column api is not suitable for me since it relies on the actual data
provided, but I'm the one who calls sqlite3_result_* to provide this.


>
> PRAGMA table_info(table1);
>

The pragma just returns the type part as it was provided by the virtual
table module with schema data. No conversion or mapping is made. But it
will help be to extract the type part correctly if the provided list
comments for example .
  [FieldA] /*This is fieldA */ TEXT, [FieldB] FLOAT


Something also came up as a solution during the reading of this post. The
query
  Select typeof(cast('' as ))
doesn't require any table so I just can replace  with a provided
column type and get the best affinity.
But for any unrecognized it will return "integer", but I'd rather have
"text".

Max
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Map a field type to a best suited sqlite3_result_* call

[J Decker]

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

The sqlite3_column_type() routine returns the datatype code for the initial
data type of the result column. The returned value is one of
SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The
return value of sqlite3_column_type() can be used to decide which of the
first six interface should be used to extract the column value. The value
returned by sqlite3_column_type() is only meaningful if no automatic type
conversions have occurred for the value in question. After a type
conversion, the result of calling sqlite3_column_type() is undefined,
though harmless. Future versions of SQLite may change the behavior of
sqlite3_column_type() following a type conversion.

-
First search...
https://stackoverflow.com/questions/11753871/getting-the-type-of-a-column-in-sqlite

PRAGMA table_info(table1);

will dump the table information, e.g.

cid|name |type|notnull |dflt_value |pk0
|id_fields_starring   |INTEGER |0   |   |11
|fields_descriptor_id |INTEGER |1   |   |02  |starring_id
|INTEGER |1   |   |03  |form_mandatory
|INTEGER |1   |1  |04  |form_visible |INTEGER |1
|1  |0

http://www.sqlite.org/datatype3.html

NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes
depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE
floating point number.
TEXT. The value is a text string, stored using the database encoding
(UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.



On Sat, May 5, 2018 at 2:59 AM, Max Vlasov  wrote:

> Hi,
>
> What is the best way to map a field type as it is provided in create table
> statement to sqlite3_result_* function call?
>
> More specifically, I have a virtual table implementation where a raw table
> data provided and the corresponding field definition part ([FieldA] TEXT,
> [FieldB] FLOAT). When asked by sqlite to fetch the column data I'd like
> to convert the text representation of a column to the best "type" and make
> the best sqlite3_result_* call. So I need probably the same machinery as
> sqlite itself when it handles type affinity.
>
> I found a function in the sources, sqlite3AffinityType, that probably
> serves the goal, but it's private. Also, a suggestion to implement a
> similar functionality was made by Jay A. Kreibich is in the mail list
> (mentioning  sqlite3AffinityType)
>
> On Thu, Jul 8, 2010 at 12:23 PM, Jay A. Kreibich  wrote:
> >> Other suggestions?
> >
> >  This has nothing to do with WAL, but it might be nice to expose
> >  the logic that does SQL-type => SQLite-affinity mappings
> >  (i.e. sqlite3AffinityType()):
> >
> >int sqlite3_get_affinity( const char *type );
> >
> >  Takes a string that contains an SQL type.  Returns one of:
> >
> >  SQLITE_AFF_TEXT, SQLITE_AFF_NONE, SQLITE_AFF_NUMERIC,
> >  SQLITE_AFF_INTEGER, SQLITE_AFF_REAL.
>
> But I suppose sqlite3_get_affinity or similar function was never
> implemented.
> I know that I probably might mimic the sqlite machinery knowing it's not
> that complex, but it is always better to rely on the approved logic of
> sqlite itself.
>
> Thanks,
>
> Max
> ___
> 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] Map a field type to a best suited sqlite3_result_* call

[Max Vlasov]

Hi,

What is the best way to map a field type as it is provided in create table
statement to sqlite3_result_* function call?

More specifically, I have a virtual table implementation where a raw table
data provided and the corresponding field definition part ([FieldA] TEXT,
[FieldB] FLOAT). When asked by sqlite to fetch the column data I'd like
to convert the text representation of a column to the best "type" and make
the best sqlite3_result_* call. So I need probably the same machinery as
sqlite itself when it handles type affinity.

I found a function in the sources, sqlite3AffinityType, that probably
serves the goal, but it's private. Also, a suggestion to implement a
similar functionality was made by Jay A. Kreibich is in the mail list
(mentioning  sqlite3AffinityType)

On Thu, Jul 8, 2010 at 12:23 PM, Jay A. Kreibich  wrote:
>> Other suggestions?
>
>  This has nothing to do with WAL, but it might be nice to expose
>  the logic that does SQL-type => SQLite-affinity mappings
>  (i.e. sqlite3AffinityType()):
>
>int sqlite3_get_affinity( const char *type );
>
>  Takes a string that contains an SQL type.  Returns one of:
>
>  SQLITE_AFF_TEXT, SQLITE_AFF_NONE, SQLITE_AFF_NUMERIC,
>  SQLITE_AFF_INTEGER, SQLITE_AFF_REAL.

But I suppose sqlite3_get_affinity or similar function was never
implemented.
I know that I probably might mimic the sqlite machinery knowing it's not
that complex, but it is always better to rely on the approved logic of
sqlite itself.

Thanks,

Max
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users