I'm using System.Data.SQLite, selecting from a sqlite database table where a column has type 'integer', and when I do something like this:
int x = (int)reader["myColumn"];
it fails. The problem is not that the value is null; the column is not nullable. If I change the data type of the column to 'int' then it works fine. The values in the column are '2', '3', '4', etc.; nothing very big.
Anyone know if this is expected behaviour?
As the other answerer mentioned, SQLite integer is stored in 1, 2, 3, 4, 6, or 8 bytes. However, you won't get overflow or out of range exceptions.
In that context, (int) is a cast, not a conversion. If reader[] didn't return an object of type integer, if it's returning a different numeric type, you will get a cast exception, regardless of the value it contains.
Based on the range of valid values for SQLite integer, I'd guess that it's returning the value as a 64-bit integer, long. To verify, try this:
object x = reader["myColumn"];
Debug.WriteLine(x.GetType().Name);
long x = (long)reader["myColumn"]; works fine. You're absolutely right - it was an Int64 - so just using long variables will work for me. As for why the behaviour works differently for INT and INTEGER it's still odd to me. I can repeat the success/fail of casting from INT/INTEGER, for the same data values in the table, and can replicate the problem in another table too. Presumably just a slight inconsistency in the driver.The exception occurs because datareader.Item[] returns object.
The problem is due to boxing/unboxing. It's nothing to do with the database itself...
long a = 1;
int b = 2;
object objectA = a;
object objectB = b;
Console.WriteLine((int)a);
Console.WriteLine((long)b);
Console.WriteLine((int)objectA);
Console.WriteLine((long)objectB);
Console outputs 2 then 1, but will throw an exception on the cast of objectA, objectB. This is a characteristic of .NET nothing to do with ODBC drivers.
SQLite http://www.sqlite.org/datatype3.html INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
.NET http://msdn.microsoft.com/en-us/library/cs7y5x0x(VS.90).aspx int -2,147,483,648 .. 2,147,483,647
So I would expect an overflow or out of range error for 'big' SQlite integer values.
