System.Data.SQLite

I downloaded 1.0.45.0 this morning and tested it with our application. Unfortunately, it appears to be incompatible with our code (which was working with 1.0.44.0).

We create a table with the SQL command: create table Users (UserId integer primary key autoincrement, Name text);

We then execute the command "select UserId from Users where Name = @Name;" using ExecuteScalar(). In 1.0.44.0 (and earlier), this returned a boxed long. In 1.0.45.0, it returns a string.

From my investigation, it appears that this is due to a change in SQLite3.GetValue. v1.0.44.0 contained the line of code
    if (typ.Affinity == 0) typ = SQLiteConvert.ColumnToType(stmt, index);
which would try to determine the type of a column by (eventually) calling sqlite3_column_decltype_interop. v1.0.45.0 removes this line of code. If typ.Affinity is not set (which is always the case when GetValue is called by ExecuteScalar()), the default behavior is now to return a string from GetText(), which is not what our code expects.

I notice that one change in 1.0.45.0 is "Fixed SQLiteDataReader to better handle type-less usage scenarios, which also fixes problems with null values and datetimes"; is this change in ExecuteScalar a side-effect of that?

I was able to work around the problem by adding the following line of code to SQLiteCommand.ExecuteScalar (before calling _cnn._sql.GetValue):
    SQLiteConvert.ColumnToType(stmt, 0, typ);
However, I don't know if this is the best solution.

I should add the important note that we are not using the 1.0.45.0 binary distribution. I downloaded the source code and merged it with some changes we have made locally (e.g., adding a SQLite3.LastInsertRowId property for convenience). It's quite possible that I made an error while merging in the new code and have only found a bug that I created myself. If so, please let me know. As time permits, I will try to write a test app that reproduces this problem against the official binary, just to make sure it's not my error.

Thanks,
Bradley
 

I have written a small test app that reproduces the problem against the official 1.0.45.0 binary. Running the code below prints:
o = 1; o.GetType() = System.String

This is a change from 1.0.44.0, which printed:
o = 1; o.GetType() = System.Int64

Thanks,
Bradley

// ---------- 

using System;
using System.Data;
using System.Data.SQLite;
using System.IO;

namespace ExecuteScalar
{
    class Program
    {
        static void Main(string[] args)
        {
            string strPath = @"C:\Temp\test.db";
            File.Delete(strPath);
            SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
            builder.DataSource = strPath;
            using (SQLiteConnection conn = new SQLiteConnection(builder.ConnectionString))
            {
                conn.Open();

                using (SQLiteCommand cmdCreate = new SQLiteCommand("create table Users (UserId integer primary key autoincrement, Name text)", conn))
                    cmdCreate.ExecuteNonQuery();
                using (SQLiteCommand cmdInsert = new SQLiteCommand("insert into Users(Name) values ('Bradley')", conn))
                    cmdInsert.ExecuteNonQuery();
                using (SQLiteCommand cmdSelect = new SQLiteCommand("select UserId from Users where Name = @Name", conn))
                {
                    cmdSelect.Parameters.Add("Name", DbType.String).Value = "Bradley";
                    object o = cmdSelect.ExecuteScalar();
                    Console.WriteLine("o = {0}; o.GetType() = {1}", o, o.GetType());
                }
            }
        }
    }
}

I've been able to reproduce the bug ... guess I'll be keeping up with tradtion and putting out a .1 release shortly after a .0 release.

Robert

It's not just numeric values. I have a database with BLOB field that has images stored in it. 1.0.44 also worked fine but this one fetches the blob field as String.

I am using the SQLiteDataReader to fetch my data.