System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: e06c4caff3c433c80616ae5c6df63fc830825e59
Title: NAN storage problem in columns of type 'double'
Status: Closed Type: Incident
Severity: Minor Priority: Low
Subsystem: Native_Assembly Resolution: Works_As_Designed
Last Modified: 2013-03-22 23:53:17
Version Found In: 1.0.82.0
User Comments:
anonymous added on 2013-03-22 14:04:58:
I'd like to insert NaN values into SQLite database, via System.Data.SQLite. I have Ent table with Id, StringColumn and DoubleColumn (IMPORTANT: DoubleColumn is not nullable) and I try use the following SQL statement:

  INSERT INTO Ent (Id, StringColumn, DoubleColumn) VALUES (1, 'NaN test', NULL)

Unfortunately I get error: "Abort due to constraint violation. Ent.DoubleColumn may not be NULL". According to http://www.sqlite.org/cvstrac/tktview?tn=3060 NULL serves as NaN for not nullable double columns.

I expect that I could write NaN values using the above statement and after reading them back using i.e.: 'SELECT DoubleColumn FROM Ent', I would get double.NaN.

Maybe there's another method of handling NaNs, that I don't know about.

mistachkin added on 2013-03-22 23:45:07:
I've added a new connection flag that can help work around the strict type
affinity checking normally imposed by System.Data.SQLite.

The new connection flag is "GetAllAsText" and it will force all column values
returned from the SQLiteDataReader.GetValue method to be text, even if their
type affinity would suggest otherwise.

Using this new flag along with the "BindAllAsText" flag, attempting to insert
a NaN into the column will actually insert the literal string "NaN" and that
string will be returned upon being read via the SQLiteDataReader.GetValue method.

The check-in is [2cd9814a44].  This feature will appear in the upcoming 1.0.85.0
release.