Re: [sqlite] When is column type valid?

[Simon Slavin]

On 8 Apr 2013, at 4:16pm, ven...@intouchmi.com wrote:

> I've done a query via a view.
> The column names that I retrieve are good.  But, the column types are not.  
> They are always set to SQLITE_NULL (5)

How are you trying to retrieve the column type ?  What function call are you 
using ?

> whereas the values are either integers, or strings and no null values.
> Is there a way to obtain data type?

SQLite does not have column types.  Instead it has affinities.  Each row of 
each column may have a value of a different type.  So you can get the type of a 
particular value, but not the type of every value of a column.  For more detail 
see here:



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

Re: [sqlite] When is column type valid?

[veneff]

I am using sqlite3_column_type.

I'l try sqlite3_column_dectype tomorrow.  I'm hoping that the column type 
follows the intermediate result set from joins and views.  It looks like I have 
to be sure SQLITE_ENABLE_COLUMN_METADATA is set.

Vance


on Apr 09, 2013, Simon Slavin  wrote:
>
>
>On 8 Apr 2013, at 4:16pm, ven...@intouchmi.com wrote:
>
>> I've done a query via a view.
>> The column names that I retrieve are good.  But, the column types are not.  
>> They
>are always set to SQLITE_NULL (5)
>
>How are you trying to retrieve the column type ?  What function call are you 
>using
>?
>
>> whereas the values are either integers, or strings and no null values.
>> Is there a way to obtain data type?
>
>SQLite does not have column types.  Instead it has affinities.  Each row of 
>each column
>may have a value of a different type.  So you can get the type of a particular 
>value,
>but not the type of every value of a column.  For more detail see here:
>
>
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is column type valid?

[Igor Tandetnik]

On 4/9/2013 8:01 PM, ven...@intouchmi.com wrote:

I am using sqlite3_column_type.


Have you called sqlite3_step before calling sqlite3_column_type? 
sqlite3_column_type doesn't report the type of the column (which is a 
concept that doesn't really exist in SQLite), but the type of the value 
in the given column and the *current row*. For it to work, the statement 
has to actually be positioned over some row.

--
Igor Tandetnik

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

Re: [sqlite] When is column type valid?

[veneff]

Igor,

Yes, I called sqlite3_column_type before and after each sqlite3_step in order 
to determine when it might work and it always came back as SQLITE_NULL (5).

Incidentally, the SQLite version is "3.7.16.1".

Vance

on Apr 09, 2013, Igor Tandetnik  wrote:
>
>On 4/9/2013 8:01 PM, ven...@intouchmi.com wrote:
>> I am using sqlite3_column_type.
>
>Have you called sqlite3_step before calling sqlite3_column_type? 
>sqlite3_column_type doesn't report the type of the column (which is a 
>concept that doesn't really exist in SQLite), but the type of the value 
>in the given column and the *current row*. For it to work, the statement 
>has to actually be positioned over some row.
>-- 
>Igor Tandetnik
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is column type valid?

[Simon Slavin]

On 10 Apr 2013, at 1:01am, ven...@intouchmi.com wrote:

> I am using sqlite3_column_type.

Okay.  Note that that returns the type of a particular value:



"information about a single column of the current result row of a query"

In other words, as I wrote earlier it is information about one row.  It does 
not necessarily apply to any other rows returned by the same query.  And you 
can't just execute it on SELECT statement, you have to actually get a row of 
results and execute it on that row.

> I'l try sqlite3_column_dectype tomorrow.

Erm ... no such function.  Unless you're getting it from somewhere I'm not 
looking.  Can you show a URL ?

> I'm hoping that the column type follows the intermediate result set from 
> joins and views.  It looks like I have to be sure 
> SQLITE_ENABLE_COLUMN_METADATA is set.

The result of calling 'sqlite3_column_type' should be the type of whatever 
value is stored or calculated.  If the software putting values into the tables 
is consistent then the types you get out are consistent, but it's perfectly 
valid to do this:

INSERT INTO myTable VALUES (NULL);
INSERT INTO myTable VALUES (1);
INSERT INTO myTable VALUES ('A');

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

Re: [sqlite] When is column type valid?

[Simon Slavin]

On 10 Apr 2013, at 1:19am, ven...@intouchmi.com wrote:

> Yes, I called sqlite3_column_type before and after each sqlite3_step in order 
> to determine when it might work and it always came back as SQLITE_NULL (5).

Does that column correspond to an actual column in a table ?  Or is it instead 
the result of a calculation ?  The documentation



says "The value returned by sqlite3_column_type() is only meaningful if no type 
conversions have occurred as described below. After a type conversion, the 
value returned by sqlite3_column_type() is undefined."

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

Re: [sqlite] When is column type valid?

[Igor Tandetnik]

On 4/9/2013 9:04 PM, Simon Slavin wrote:

I'l try sqlite3_column_dectype tomorrow.


Erm ... no such function.  Unless you're getting it from somewhere I'm not 
looking.  Can you show a URL ?


I'm pretty sure the OP means 
http://www.sqlite.org/c3ref/column_decltype.html

--
Igor Tandetnik

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

Re: [sqlite] When is column type valid?

[Simon Slavin]

On 10 Apr 2013, at 2:10am, Igor Tandetnik  wrote:

> On 4/9/2013 9:04 PM, Simon Slavin wrote:
>>> I'l try sqlite3_column_dectype tomorrow.
>> 
>> Erm ... no such function.  Unless you're getting it from somewhere I'm not 
>> looking.  Can you show a URL ?
> 
> I'm pretty sure the OP means http://www.sqlite.org/c3ref/column_decltype.html

Wow.  I have never seen that before.  Or the VARIANT type used in SQLite.  
Thanks.

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

Re: [sqlite] When is column type valid?

[Igor Tandetnik]

On 4/9/2013 9:09 PM, Simon Slavin wrote:


On 10 Apr 2013, at 1:19am, ven...@intouchmi.com wrote:


Yes, I called sqlite3_column_type before and after each sqlite3_step in order 
to determine when it might work and it always came back as SQLITE_NULL (5).


Does that column correspond to an actual column in a table ?  Or is it instead 
the result of a calculation ?  The documentation



says "The value returned by sqlite3_column_type() is only meaningful if no type 
conversions have occurred as described below. After a type conversion, the value returned 
by sqlite3_column_type() is undefined."


"Type conversion" in that article has nothing to do with whether a 
column is the result of a calculation, and everything to do with 
retrieving the value by means of sqlite3_column_T call where T doesn't 
match the actual type of the value (e.g. using sqlite_column_text on an 
integer value to retrieve the textual representation of the integer).

--
Igor Tandetnik

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

Re: [sqlite] When is column type valid?

[Igor Tandetnik]

On 4/9/2013 9:15 PM, Simon Slavin wrote:

Wow.  I have never seen that before.  Or the VARIANT type used in SQLite.  
Thanks.


VARIANT doesn't have any special meaning to SQLite. You can just as well 
write "CREATE TABLE t1(c1 foo bar);", and sqlite3_column_decltype would 
cheerfully return the string "foo bar". The only way the type is used is 
to determine column affinity, as documented at 
http://www.sqlite.org/datatype3.html . Both VARIANT and "foo bar" would 
result in NUMERIC affinity.

--
Igor Tandetnik

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

Re: [sqlite] When is column type valid?

[veneff]

Sorry, I did mean sqlite3_column_decltype.

I repeated my test with sqlite3_column_type and doing the 
call after a successful call to sqlite3_column_text still always yields 
SQLITE_NULL.
When changing the call to sqlite3_column_decltype, I do get the column 
declarations.
So, all I need to do is convert the declaration verbiage to type values.
It does seem odd that sqlite3_column_type doesn't seem to work.
  I do understand the data type affinities issue and realize that the data type 
does not necessarily have anything to do with the initial column definition.

Vance

on Apr 09, 2013, Igor Tandetnik  wrote:
>
>On 4/9/2013 9:04 PM, Simon Slavin wrote:
>>> I'l try sqlite3_column_dectype tomorrow.
>>
>> Erm ... no such function.  Unless you're getting it from somewhere I'm not 
>> looking.
> Can you show a URL ?
>
>I'm pretty sure the OP means 
>http://www.sqlite.org/c3ref/column_decltype.html
>-- 
>Igor Tandetnik
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is column type valid?

[Igor Tandetnik]

On 4/10/2013 9:28 AM, ven...@intouchmi.com wrote:

I repeated my test with sqlite3_column_type and doing the
call after a successful call to sqlite3_column_text still always yields 
SQLITE_NULL.


Just as the documentation suggests. Call sqlite3_column_type right after 
sqlite3_step and before any other sqlite3_column_* calls.

--
Igor Tandetnik

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

Re: [sqlite] When is column type valid?

[veneff]

Yes, it does work after step and before get column value.
Thanks for your help in this.

A quick follow up:
The call to sqlite3_col_decltype returns a char *
Should I issue a sqlite3_free on it when I am done with the string?

Vance

on Apr 10, 2013, Igor Tandetnik  wrote:
>
>On 4/10/2013 9:28 AM, ven...@intouchmi.com wrote:
>> I repeated my test with sqlite3_column_type and doing the
>> call after a successful call to sqlite3_column_text still always yields 
>> SQLITE_NULL.
>
>Just as the documentation suggests. Call sqlite3_column_type right after 
>sqlite3_step and before any other sqlite3_column_* calls.
>-- 
>Igor Tandetnik
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is column type valid?

[Igor Tandetnik]

On 4/10/2013 10:06 AM, ven...@intouchmi.com wrote:

The call to sqlite3_col_decltype returns a char *
Should I issue a sqlite3_free on it when I am done with the string?


No. It gives you a pointer into an internal data structure associated 
with the statement handle. The memory will be released, and the pointer 
will become invalid, when you call sqlite3_finalize

--
Igor Tandetnik

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

Re: [sqlite] When is column type valid?

[Dominique Devienne]

On Wed, Apr 10, 2013 at 5:16 PM, Igor Tandetnik  wrote:

> On 4/10/2013 10:06 AM, ven...@intouchmi.com wrote:
>
>> The call to sqlite3_col_decltype returns a char *
>> Should I issue a sqlite3_free on it when I am done with the string?
>>
>
> No. It gives you a pointer into an internal data structure associated with
> the statement handle. The memory will be released, and the pointer will
> become invalid, when you call sqlite3_finalize
>

That deserves to be noted in
http://www.sqlite.org/c3ref/column_decltype.html rather than on list, no? I
know, I ought to sent a patch instead of resquesting for it :) --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users