Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sqlite database unable to delete objects, failing with message "Cannot store type: <type>"

I'm trying to delete records for a type I'm storing in my Sqlite database. It's failing with the following message:

"Cannot store type: MyNamespace.PanelLog"

I have the following method to delete records:

    public static int DeletePanelLog(int id)
    {
        return me.db.DeleteItem<PanelLog>(id);
    }

where DeleteItem is defined as:

    public int DeleteItem<T>(int id) where T : IBusinessEntity, new()
    {
        lock (locker) {
#if NETFX_CORE
            return Delete(new T() { ID = id });
#else
            return Delete<T> (new T () { ID = id });
#endif
        }
    }

PanelLog is defined as:

public class PanelLog : IBusinessEntity
{
    public PanelLog(){}

    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public uint Sequence { get; set; }
    public DateTime Time { get; set; }
    public string Message { get; set; }
    public bool Alarm { get; set; }
}

The following is the Call Stack from SQLite:

    public int Delete<T>(object primaryKey)
    {
        var map = GetMapping(typeof(T));
        var pk = map.PK;
        if (pk == null)
        {
            throw new NotSupportedException("Cannot delete " + map.TableName + ": it has no PK");
        }
        var q = string.Format("delete from \"{0}\" where \"{1}\" = ?", map.TableName, pk.Name);
        return Execute(q, primaryKey); // in this case, primaryKey is a PanelLog object
    }

    public int Execute(string query, params object[] args)
    {
        var cmd = CreateCommand(query, args);

        if (TimeExecution)
        {
            if (_sw == null)
            {
                _sw = new Stopwatch();
            }
            _sw.Reset();
            _sw.Start();
        }

        var r = cmd.ExecuteNonQuery();

        if (TimeExecution)
        {
            _sw.Stop();
            _elapsedMilliseconds += _sw.ElapsedMilliseconds;
            Debug.WriteLine(string.Format("Finished in {0} ms ({1:0.0} s total)", _sw.ElapsedMilliseconds, _elapsedMilliseconds / 1000.0));
        }

        return r;
    }

    public int ExecuteNonQuery()
    {
        if (_conn.Trace)
        {
            Debug.WriteLine("Executing: " + this);
        }

        var r = SQLite3.Result.OK;
        var stmt = Prepare();
        r = SQLite3.Step(stmt);
        Finalize(stmt);
        if (r == SQLite3.Result.Done)
        {
            int rowsAffected = SQLite3.Changes(_conn.Handle);
            return rowsAffected;
        }
        else if (r == SQLite3.Result.Error)
        {
            string msg = SQLite3.GetErrmsg(_conn.Handle);
            throw SQLiteException.New(r, msg);
        }
        else
        {
            throw SQLiteException.New(r, r.ToString());
        }
    }

    Sqlite3Statement Prepare()
    {
        var stmt = SQLite3.Prepare2(_conn.Handle, CommandText);
        BindAll(stmt);
        return stmt;
    }

    void BindAll(Sqlite3Statement stmt)
    {
        int nextIdx = 1;
        foreach (var b in _bindings)
        {
            if (b.Name != null)
            {
                b.Index = SQLite3.BindParameterIndex(stmt, b.Name);
            }
            else
            {
                b.Index = nextIdx++;
            }

            BindParameter(stmt, b.Index, b.Value, _conn.StoreDateTimeAsTicks);
        }
    }

The execution fails at the last line in the following: (line 1051 here)

// line 1027 of http://code.google.com/p/sqlite-net/source/browse/trunk/src/SQLite.cs
internal static void BindParameter(Sqlite3Statement stmt, int index, object value, bool storeDateTimeAsTicks)
{
    if (value == null)
    {
        SQLite3.BindNull(stmt, index);
    }
    else
    {
        if (value is Int32)
        {
            SQLite3.BindInt(stmt, index, (int)value);
        }
        else if (value is String)
        {
            SQLite3.BindText(stmt, index, (string)value, -1, NegativePointer);
        }
        else if (value is Byte || value is UInt16 || value is SByte || value is Int16)
        {
            SQLite3.BindInt(stmt, index, Convert.ToInt32(value));
        }
        else if (value is Boolean)
        {
            SQLite3.BindInt(stmt, index, (bool)value ? 1 : 0);
        }
        else if (value is UInt32 || value is Int64)
        {
            SQLite3.BindInt64(stmt, index, Convert.ToInt64(value));
        }
        else if (value is Single || value is Double || value is Decimal)
        {
            SQLite3.BindDouble(stmt, index, Convert.ToDouble(value));
        }
        else if (value is DateTime)
        {
            if (storeDateTimeAsTicks)
            {
                SQLite3.BindInt64(stmt, index, ((DateTime)value).Ticks);
            }
            else
            {
                SQLite3.BindText(stmt, index, ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"), -1, NegativePointer);
            }
#if !NETFX_CORE
        }
        else if (value.GetType().IsEnum)
        {
#else
} else if (value.GetType().GetTypeInfo().IsEnum) {
#endif
            SQLite3.BindInt(stmt, index, Convert.ToInt32(value));
        }
        else if (value is byte[])
        {
            SQLite3.BindBlob(stmt, index, (byte[])value, ((byte[])value).Length, NegativePointer);
        }
        else if (value is Guid)
        {
            SQLite3.BindText(stmt, index, ((Guid)value).ToString(), 72, NegativePointer);
        }
        else
        {
            throw new NotSupportedException("Cannot store type: " + value.GetType());
        }
    }
}

It seems to be trying to bind the object type, rather than the object's value. Is this correct? Is there anything I can do to fix this?

EDIT:

After some further testing, it seems the following works:

    public int Delete(object objectToDelete)
    {
        var map = GetMapping(objectToDelete.GetType());
        var pk = map.PK;
        if (pk == null)
        {
            throw new NotSupportedException("Cannot delete " + map.TableName + ": it has no PK");
        }
        var q = string.Format("delete from \"{0}\" where \"{1}\" = ?", map.TableName, pk.Name);
        return Execute(q, pk.GetValue(objectToDelete));
    }
like image 365
DaveDev Avatar asked Aug 14 '13 13:08

DaveDev


2 Answers

I also encountered this problem. Actually it comes from the line inside the definition of DeleteItem:

return Delete<T> (new T () { ID = id });

It should be:

return Delete<T> (id);
like image 132
Feng Jiang Avatar answered Sep 23 '22 20:09

Feng Jiang


Rather than use:

   public int DeleteItem<T>(int id) where T : IBusinessEntity, new()
    {
        lock (locker) {
    #if NETFX_CORE
            return Delete(new T() { ID = id });
    #else
            return Delete<T> (new T () { ID = id });
    #endif
        }
    }

Use this instead:

   public int Delete(object objectToDelete)
    {
        var map = GetMapping(objectToDelete.GetType());
        var pk = map.PK;
        if (pk == null)
        {
            throw new NotSupportedException("Cannot delete " + map.TableName + ": it has no PK");
        }
        var q = string.Format("delete from \"{0}\" where \"{1}\" = ?", map.TableName, pk.Name);
        return Execute(q, pk.GetValue(objectToDelete));
    }
like image 29
DaveDev Avatar answered Sep 22 '22 20:09

DaveDev