SQLite: Read string value from numeric/real column (.NET)

Question

I am using a SQLite database to store double precision floating point values in a .NET project. I have encountered a well known problem with NaN and infinity values and I am trying my way around it by storing the strings "NaN", "+Inf", "-Inf" for these values and converting these back to the respective double values when needed. I am using the System.Data.SQLite classes to implement SQLite in my project.

Now I see different ways to solve the problem. A working method is the following: I could create the column with the TEXT type. When I do this every value, even valid double numbers are stored as a string:

Creation of the table:

Using cmd As New SQLiteCommand("CREATE TABLE IF NOT EXISTS Test (vals TEXT);")
...
Using cmd As New SQLiteCommand("INSERT INTO Test (vals) VALUES(@v);")
...
p.Value = SQLite_DoubleToValue(Double.PositiveInfinity)

The double to value conversion method:

Private Function SQLite_DoubleToValue(D As Double) As Object
    If Double.IsNaN(D) Then
        Return "NaN"
    ElseIf D > 1.79769313486E+308 Then 'Some rounding, everything above this is treated as infinity by SQLite
        Return "+Inf"
    ElseIf D < -1.79769313485E+308 Then 'Same here
        Return "-Inf"
    Else
        Return D 'Return the actual double value, boxed in an Object
    End If
End Function

Read back:

Using cmd As New SQLiteCommand("SELECT * FROM Test;")
Dim rdr As SQLiteDataReader = cmd.ExecuteReader
rdr.Read()
Dim val As Double = SQLite_ValueToDouble(rdr.GetValue(0))

Second value to double conversion method:

Private Function SQLite_ValueToDouble(V As Object) As Double
If TypeOf (V) Is String Then
    Dim s As String = CStr(V)
    If s = "NaN" Then
        Return Double.NaN
    ElseIf s = "+Inf" Then
        Return Double.PositiveInfinity
    ElseIf s = "-Inf" Then
        Return Double.NegativeInfinity
    Else 'Value can only be a number representation here
        Dim val As Double = Double.NaN
        Double.TryParse(s, val) '<- I don't like to do this conversion
        Return val
    End If
ElseIf TypeOf (V) Is Double Then '<- This never happens when the column is created as TEXT
    Return CDbl(V)
Else
    Return Double.NaN
End If
End Function

---

As I pointed out in the latter conversion method, I don't like the fact that even valid numbers are stored as strings. The databases grow in size when I declare TEXT columns instead of REAL columns and since SQLite does not really enforce data types I should be able to store the text in numeric columns as well. And in fact I can.

If I create my table like this:

Using cmd As New SQLiteCommand("CREATE TABLE IF NOT EXISTS Test (vals REAL);")
...
Using cmd As New SQLiteCommand("INSERT INTO Test (vals) VALUES(@v);")
...
p.Value = SQLite_DoubleToValue(Double.PositiveInfinity)

and check my database in Database Browser for SQLite I see:

So the text is stored nicely in the numeric column. However if I read it back using GetValue in .NET I don't get the string, but I get a value of 0.0 (Double).

It seems like either the SQLite API or the System.Data.SQLite implementation converts every value to double if a real column is encountered automatically, even if GetValue instead of GetDouble is used. It probably is not the API, since the Database Browser correctly reads the string value.

Do I miss something obvious or is there some bug and maybe a fix for it? The behaviour I would like to see is that GetValue returns a double value (boxed in an object of course) if I wrote a double value into the table, and a string if I wrote a string into the column. Is there any way to achieve this?

I have read about the BindAndGetAllAsText flag, but I see problems there because a) again everything is written as Text into the database and b) the string representations are OS language dependent, which may lead to problems when reading the data back on different computers.

Answers in both VB.NET and C# are very much appreciated since it's not a language specific question here.

Answer 1

I have solved the problem by defining three specific double values as constants that represent the three special states:

Public Const NaNValue As Double = 1.7E308
Public Const PInfValue As Double = 1.71E308
Public Const NInfValue As Double = -1.71E308

I furthermore use similiar functions like the ones I showed in my question to convert the database values to double values and vice versa:

Protected Shared Function DoubleToValue(D As Double) As Double
    If Double.IsNaN(D) Then
        Return NaNValue
    ElseIf D >= PInfValue Then
        Return PInfValue
    ElseIf D <= NInfValue Then
        Return NInfValue
    Else
        Return D
    End If
End Function
Protected Shared Function ValueToDouble(V As Double) As Double
         If V = NaNValue Then
            Return Double.NaN
        ElseIf V = PInfValue Then
            Return Double.PositiveInfinity
        ElseIf V = NInfValue Then
            Return Double.NegativeInfinity
        Else
            Return V
        End If
End Function

This allows me to directly read and write double values from and to the database while maintaining the correct behaviour for the special values. You of course loose the single numbers you define as constants and limit the double value range somewhat. In most use cases you should be ok however and you could even move the limits further out than I did here.