Stephan,
> Occasionally the sensor has a fault or the values are invalid (like
> negative temperatures). To mark them once and for all as invalid, the
> faulty values are replaced (in a C environment) by NaNs. Then the
data are
> inserted into the database with a C porgram, in a loop with the
> sqlite3_bind_double function. The date are retrieved in a loop with the
> sqlite3_column_double function. It turns out that SQLITE has
quietly turned
> our invalid data (NaNs) into valid 0.0 values! Is this a bug?
>
SQLite converts NaN inputs into NULL. And sqlite3_column_double() is
defined to return 0.0 for any non-numeric value, including NULL.
I recommend that you first check the datatype coming back using
sqlite3_column_type() first, and only use sqlite3_column_double() if the
type is SQLITE_INTEGER or SQLITE_REAL and return a NaN if
sqlite3_column_type() is anything else.
You can also test for NaN before inserting and insert NaNs as BLOBs,
which are guaranteed to be stored and retrieved verbatim. This can be
important to keep the semantic of the NaN payload, should you use it
someday. Convert those BLOBs to C NaNs back after reading, if needed
to use the payload.
This way you have an efficient way to manipulate, select, avoid,
change, compare, sort valid values and NaNs without having to deal with
NULLs.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
