title: SQLite: Read string value from numeric/real column (.NET)
body: <p>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 <code>System.Data.SQLite</code> classes to implement SQLite in my project.</p>
  
  <p>Now I see different ways to solve the problem. A working method is the following: I could create the column with the <code>TEXT</code> type. When I do this every value, even valid double numbers are stored as a string:</p>
  
  <p>Creation of the table:</p>
  
  
  
  <pre class="lang-vb prettyprint-override"><code>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)
  </code></pre>
  
  <p>The double to value conversion method:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Private Function SQLite_DoubleToValue(D As Double) As Object
      If Double.IsNaN(D) Then
          Return "NaN"
      ElseIf D &gt; 1.79769313486E+308 Then 'Some rounding, everything above this is treated as infinity by SQLite
          Return "+Inf"
      ElseIf D &lt; -1.79769313485E+308 Then 'Same here
          Return "-Inf"
      Else
          Return D 'Return the actual double value, boxed in an Object
      End If
  End Function
  </code></pre>
  
  <p>Read back:</p>
  
  <pre class="lang-vb prettyprint-override"><code>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))
  </code></pre>
  
  <p>Second value to double conversion method:</p>
  
  <pre class="lang-vb prettyprint-override"><code>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) '&lt;- I don't like to do this conversion
          Return val
      End If
  ElseIf TypeOf (V) Is Double Then '&lt;- This never happens when the column is created as TEXT
      Return CDbl(V)
  Else
      Return Double.NaN
  End If
  End Function
  </code></pre>
  
  <hr>
  
  <p>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.</p>
  
  <p>If I create my table like this:</p>
  
  <pre class="lang-vb prettyprint-override"><code>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)
  </code></pre>
  
  <p>and check my database in Database Browser for SQLite I see:</p>
  
  <p><img src="https://i.stack.imgur.com/kO6dw.png" alt="DB in Database Browser"></p>
  
  <p>So the text is stored nicely in the numeric column.
  However if I read it back using <code>GetValue</code> in .NET I don't get the string, but I get a value of 0.0 (Double).</p>
  
  <p>It seems like either the SQLite API or the <code>System.Data.SQLite</code> implementation converts every value to double if a real column is encountered automatically, even if <code>GetValue</code> instead of <code>GetDouble</code> is used. It probably is not the API, since the Database Browser correctly reads the string value.</p>
  
  <p><strong>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?</strong></p>
  
  <p>I have read about the <code>BindAndGetAllAsText</code> 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.</p>
  
  <p>Answers in both VB.NET and C# are very much appreciated since it's not a language specific question here.</p>