CODEWITHSUNDEEP
c#sqliteservicestackormlite-servicestack
icube
icube

Reputation: 2798

OrmLite: SQLiteExceptionSQL logic error or missing database near ")": syntax error

Hi i'm trying to test out if I delete a parent object, the child will automatically be deleted too using OrmLite and the in-memory database Sqlite

this is my test code but it throws me System.Data.SQLite.SQLiteExceptionSQL logic error or missing database near ")": syntax error at the db.Save() line.

What might be wrong?

[Fact]
    public void DeleteById_AlsoDeleteChild_Test()
    {
        var _dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
        using (var db = _dbFactory.OpenDbConnection())
        {
            // arrange
            db.CreateTableIfNotExists<Foo>();
            db.CreateTableIfNotExists<Bar>();
            var foo = new Foo
            {
                Bar = new Bar
                {
                    Name = "Hello"
                }
            };


            db.Save(foo);
            db.SaveReferences(foo, foo.Bar);


            var saved = db.Select<Foo>();


            // act
            db.DeleteById<Foo>(saved.First().Id);

            // assert
            Assert.False(db.Exists<Bar>(c => c.FooId == saved.First().Id));
        }


    }

    public class Foo : IHasIntId
    {
        [AutoIncrement]
        public int Id { get; set; }
        [Reference]
        public Bar Bar { get; set; }
    }

    public class Bar : IHasIntId
    {
        [AutoIncrement]
        public int Id { get; set; }
        [ForeignKey(typeof(Foo), OnDelete = "CASCADE")]
        public int FooId { get; set; }
        public string Name { get; set; }
    }

System.Data.SQLite.SQLiteExceptionSQL logic error or missing database

near ")": syntax error at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, ref String strRemain) at System.Data.SQLite.SQLiteCommand.BuildNextCommand() at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteScalar(CommandBehavior behavior) at ServiceStack.OrmLite.OrmLiteReadCommandExtensions.LongScalar(IDbCommand dbCmd) at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.Save(IDbCommand dbCmd, T obj) at ServiceStack.OrmLite.OrmLiteWriteApi.<>c__DisplayClass39' 1. b__38(IDbCommand dbCmd) at ServiceStack.OrmLite.OrmLiteExecFilter.Exec(IDbConnection dbConn, Func`2 filter) at ClassLibrary2.Class1.DeleteById_AlsoDeleteChild_Test() in Class1.cs: line 35

Upvotes: 1

Views: 2443

Answers (1)

mythz
mythz

Reputation: 143284

This issue is because you Foo doesn't have any columns to INSERT since Id is a autoincrementing primary key and Bar is a [Reference] property so no columns are saved so the INSERT SQL ends up looking like:

INSERT INTO "Foo" () VALUES ();

This would work if you Foo had a column, e.g:

public class Foo : IHasIntId
{
    [AutoIncrement]
    public int Id { get; set; }

    [Reference]
    public Bar Bar { get; set; }

    public string Name { get; set; }
}

Note Foreign key support is not enabled in SQLite by default. You need to enable it manually each time you connect to the database using the pragma:

PRAGMA foreign_keys = ON

So a working example would look like:

using (var db = OpenDbConnection())
{
    db.DropAndCreateTable<Foo>();
    db.DropAndCreateTable<Bar>();
    db.ExecuteNonQuery("PRAGMA foreign_keys = ON");

    var foo = new Foo
    {
        Bar = new Bar
        {
            Name = "Hello"
        }
    };

    db.Save(foo);
    db.SaveReferences(foo, foo.Bar);

    var saved = db.Select<Foo>();

    db.DeleteById<Foo>(saved.First().Id);

    Assert.False(db.Exists<Bar>(c => c.FooId == saved.First().Id));
}

Upvotes: 1

Related Questions