Re: [sqlite] Checking if an "integer" column is set to NULL
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
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
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