[Mono-dev] Problem with sqlite in mono 1.1.13

[Aaron Bockover]

> ...A local fix for you is to use the NUMBER column type instead of
> INTEGER...

I should have said "NUMERIC" not "NUMBER" ... however, there are some
things to note, from http://sqlite.org/datatype3.html :

"Each value stored in an SQLite database (or manipulated by the database
engine) has one of the following storage classes:
        ...
	* INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
bytes depending on the magnitude of the value."

Unless some detection is done in the bindings to see if the value in a
row for an INTEGER column can fit inside of an Int32, the value should
always be returned as an Int64, otherwise we risk backwards
incompatibility, and do not conform to the sqlite3 spec, which clearly
states a number in an INTEGER column can occupy up to 8 bytes.

Cheers,
Aaron



On Sun, 2006-01-22 at 18:03 -0500, Aaron Bockover wrote:
> Wow. I'm not sure why this change was made, but in
> Mono.Data.SqliteClient/SqliteDataReader.cs, this happens now for
> columns
> declared as INT/INTEGER:
> 
> ...
> data_row[i] = (int)Sqlite.sqlite3_column_int64 (pVm, i);
> ...
> 
> An explicit cast from an Int64 to an Int32. So that change completely
> breaks compatibility with older databases.
> 
> A local fix for you is to use the NUMBER column type instead of
> INTEGER
> if you need to store an Int64, though I do not agree with this change,
> and I bet this breaks my application too:
> 
> 2006-01-02  Joshua Tauberer  <xxxxx>
> ...
>     * SqliteDataReader.cs:
>         * Use object[] in place of ArrayList for each row.
>         * For Sqlite3, which remembers whether a value was
>           an integer, text, real, or blob, actually return
>           longs, strings, doubles, and byte[]s.
>         * GetDataTypeName() works where possible (Sqlite3).
>         * INT/INTEGER columns are now returned as ints,
>           rather than longs, for Sqlite3.  Similarly for
>           DATE and DATETIME columns, now returning DateTimes.
> 
> I'll do some more probing of this issue.
> 
> Cheers,
> Aaron Bockover
> 
> 
> On Sun, 2006-01-22 at 22:49 +0100, Fredrik Nilsson wrote:
> > Sorry, forgot to attach output of testcase:
> > 
> > Value to store: 127824400782036290
> > Value in database: -2109895358
> > 
> > /Fredrik Nilsson
> > 
> > 
> > Fredrik Nilsson wrote:
> > > Hi,
> > >
> > > I'm having problem with sqlite since mono version 1.1.13.
> > > Getting a long (Int64) value doesn't work anymore.
> > > I didn't have any problems with this in version 1.1.12.
> > >
> > > Small test case will follow.
> > >
> > > /Fredrik Nilsson
> > >
> > >
> > > using System;
> > > using Mono.Data.SqliteClient;
> > >
> > > class MainClass
> > > {
> > >    public static void Main(string[] args)
> > >    {
> > >        long filetime = DateTime.Now.ToFileTime ();
> > >
> > >        Console.WriteLine ("Value to store: " + filetime);
> > >               SqliteCommand cmd = new SqliteCommand();
> > >        SqliteConnection connection = new 
> > > SqliteConnection(@"version=3,URI=file:test.db");
> > >        connection.Open();
> > >        cmd.Connection = connection;
> > >        cmd.CommandText = "CREATE TABLE Test (Date INTEGER NOT
> NULL);";
> > >        cmd.ExecuteNonQuery ();
> > >
> > >        cmd = new SqliteCommand ();
> > >        cmd.Connection = connection;
> > >        cmd.CommandText = "INSERT INTO Test (Date) VALUES ('" + 
> > > filetime + "');";
> > >        cmd.ExecuteNonQuery ();
> > >
> > >        cmd = new SqliteCommand ();
> > >        cmd.Connection = connection;
> > >        cmd.CommandText = "SELECT * FROM Test;";
> > >        SqliteDataReader reader = cmd.ExecuteReader ();
> > >               while (reader.Read ()) {
> > >            Console.WriteLine ("Value in database: " + reader[0]);
> > >        }
> > >               connection.Close();
> > >    }
> > > }
> > > _______________________________________________
> > > Mono-devel-list mailing list
> > > Mono-devel-list at lists.ximian.com
> > > http://lists.ximian.com/mailman/listinfo/mono-devel-list
> > >
> > 
> > _______________________________________________
> > Mono-devel-list mailing list
> > Mono-devel-list at lists.ximian.com
> > http://lists.ximian.com/mailman/listinfo/mono-devel-list
> 
> _______________________________________________
> Mono-devel-list mailing list
> Mono-devel-list at lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-devel-list