Int64 throws "Number overflow" using SQLite

Question

I've created an SQLite database (in windows) with a Int64 column. I copied the database to my MonoTouch program.

When I try to read the column in MonoTouch (Mono.Data.Sqlite), it throws a "Number overflow"...

System.OverflowException: Number overflow.
  at System.Convert.ToInt32 (Int64 value) [0x00000] in /Developer/MonoTouch/Source/mono/mcs/class/corlib/System/Convert.cs:1109 
  at System.Int64.System.IConvertible.ToInt32 (IFormatProvider provider) [0x00000] in /Developer/MonoTouch/Source/mono/mcs/class/corlib/System/Int64.cs:553 
  at System.Convert.ToType (System.Object value, System.Type conversionType, IFormatProvider provider, Boolean try_target_to_type) [0x00139] in /Developer/MonoTouch/Source/mono/mcs/class/corlib/System/Convert.cs:2596 
  at System.Convert.ChangeType (System.Object value, System.Type conversionType, IFormatProvider provider) [0x00017] in /Developer/MonoTouch/Source/mono/mcs/class/corlib/System/Convert.cs:2204 
  at Mono.Data.Sqlite.SQLite3.GetValue (Mono.Data.Sqlite.SqliteStatement stmt, Int32 index, Mono.Data.Sqlite.SQLiteType typ) [0x0011e] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLite3.cs:990 
  at Mono.Data.Sqlite.SqliteDataReader.GetValue (Int32 i) [0x00033] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteDataReader.cs:796 
  at Mono.Data.Sqlite.SqliteDataReader.get_Item (Int32 i) [0x00000] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteDataReader.cs:1023 
  at System.Data.Common.DbDataAdapter.FillFromReader (System.Data.DataTable table, IDataReader reader, Int32 start, Int32 length, System.Int32[] mapping, LoadOption loadOption) [0x0003e] in /Developer/MonoTouch/Source/mono/mcs/class/System.Data/System.Data.Common/DbDataAdapter.cs:365 
  at System.Data.DataTable.Load (IDataReader reader, LoadOption loadOption) [0x0002f] in /Developer/MonoTouch/Source/mono/mcs/class/System.Data/System.Data/DataTable.cs:2857 
  at System.Data.DataTable.Load (IDataReader reader) [0x00011] in /Developer/MonoTouch/Source/mono/mcs/class/System.Data/System.Data/DataTable.cs:2838 

Any idea why and how I could fix it?

Answer 1

It seems you are reading Int64 value from SQLLite and converting to Int32 while serializing to mongodb, which will result in outside the range and throwing error. Here is link for JIRA raised for mongo and possible workaround. Mongodb Number overflow error

Answer 2

The exception is due to a number that cannot be converted to an int (i.e. it would work with small long values). Somehow the code at

at Mono.Data.Sqlite.SQLite3.GetValue (Mono.Data.Sqlite.SqliteStatement stmt, Int32 index, Mono.Data.Sqlite.SQLiteType typ) [0x0011e] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLite3.cs:990

decide the type is an System.Int32 instead of an System.Int64. This means something, above that in the stack, is making a bad decision or the datatable structure is misread.

Sadly I'm not sure how you can workaround this. The best way to resolve this would be to open a bug report at http://bugzilla.xamarin.com and attach a simple test case, with the database, that shows the issue. That will allow us to see where exactly the issue is and provide you with a fix.

Answer 3

I encountered the same problem in SQLite in Microsoft Visual Studio. A select on any Int64 Column results in an overflow. I looks like a bug in System.Data.DataTable. The method that has the error is dtTable.Load(aSQLDataReader);

My work around is to cast all the Int64 columns to TEXT in the query.

Instead of SELECT columName FROM tableName

I did SELECT CAST(columnName as TEXT) columName FROM tableName

Sqlite then returns value as text field.

Answer 4

I had the same problem, and after doing an exhaustive debug, I found that changing the database field data type from "INT" to "INTEGER" solved it.

It seems that the client misuses the database data type to make the conversion, even though it would be the same for the database.