Re: [sqlite] Checking if an "integer" column is set to NULL

[John Machin]

On 25/05/2009 10:15 PM, chandan wrote:
> Hi,
> I have used sqlite3_bind_null() API to bind an integer column with 
> NULL. When I read the value of that integer column I get the value as 0 
> (zero). Is there any way I can check if the column is set to NULL?

You do realise that calling it "that integer column" is more hopeful 
than meaningful, don't you?

How are you reading "the value of that integer column"?

Here are some ways you can display it and test it using SQL:

sqlite> create table t (i integer);
sqlite> insert into t values(1);
sqlite> insert into t values(0);
sqlite> insert into t values(-1);
sqlite> insert into t values(null);
sqlite> insert into t values(123.456);
sqlite> insert into t values('abcdef');
sqlite> insert into t values(x'f000baaa');
sqlite> select rowid, i, quote(i), typeof(i) from t;
1|1|1|integer
2|0|0|integer
3|-1|-1|integer
4||NULL|null
5|123.456|123.456|real
6|abcdef|'abcdef'|text
7|­|X'F000BAAA'|blob
sqlite> select rowid, i, quote(i), typeof(i) from t where i is null;
4||NULL|null

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

Re: [sqlite] Checking if an "integer" column is set to NULL

[chandan]

Thanks a lot!. The solution works :-)

Hamish Allan wrote:
> On Mon, May 25, 2009 at 1:15 PM, chandan
>  wrote:
>
>   
>>I have used sqlite3_bind_null() API to bind an integer column with
>> NULL. When I read the value of that integer column I get the value as 0
>> (zero). Is there any way I can check if the column is set to NULL?
>> 
>
> This was something that confused me at first, so perhaps it's not
> crystal clear in the documentation:
>
> http://www.sqlite.org/capi3ref.html#sqlite3_column_blob
>
> [Annotations mine] "The sqlite3_column_type() routine returns the
> datatype code for the initial data type of the result column [NB I
> initially read this as "the initial data type of the column" rather
> than "the initial data type of the result", assuming that it would
> return the column affinity rather than the stored type]. The returned
> value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT,
> SQLITE_BLOB, or SQLITE_NULL. 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. Future versions of
> SQLite may change the behavior of sqlite3_column_type() following a
> type conversion."
>
> In other words, if you call sqlite3_column_type() before you call
> sqlite3_column_int(), you can differentiate the two cases.
>
> Best wishes,
> Hamish
> ___
> 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] Checking if an "integer" column is set to NULL

[Hamish Allan]

On Mon, May 25, 2009 at 1:15 PM, chandan
 wrote:

>    I have used sqlite3_bind_null() API to bind an integer column with
> NULL. When I read the value of that integer column I get the value as 0
> (zero). Is there any way I can check if the column is set to NULL?

This was something that confused me at first, so perhaps it's not
crystal clear in the documentation:

http://www.sqlite.org/capi3ref.html#sqlite3_column_blob

[Annotations mine] "The sqlite3_column_type() routine returns the
datatype code for the initial data type of the result column [NB I
initially read this as "the initial data type of the column" rather
than "the initial data type of the result", assuming that it would
return the column affinity rather than the stored type]. The returned
value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT,
SQLITE_BLOB, or SQLITE_NULL. 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. Future versions of
SQLite may change the behavior of sqlite3_column_type() following a
type conversion."

In other words, if you call sqlite3_column_type() before you call
sqlite3_column_int(), you can differentiate the two cases.

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