SQLite bugs or features?

INSERT statement with multiple rows does not work, only the last one is inserted.

INSERT INTO Foo (Bar) VALUES(10),(20),(30);

DELETE statement with IN clause does not work.

"DELETE FROM Foo WHERE Id IN (1,2);"

After delete all rows from the table, primary key reset to value 1.

Tested code on RC1:

class Program
{
    static void Main()
    {
        using (var db = new SQLiteDatabase())
        {                
            db.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS Foo (Id INTEGER PRIMARY KEY AUTOINCREMENT, Bar INTEGER);");

            db.ExecuteNonQuery("INSERT INTO Foo (Bar) VALUES(10),(20),(30);");
            Select(db);

            db.ExecuteNonQuery("INSERT INTO Foo (Bar) VALUES(40);");
            db.ExecuteNonQuery("DELETE FROM Foo WHERE Id IN (1,2);");
            Select(db);

            db.ExecuteNonQuery("DELETE FROM Foo WHERE Id = 1;");
            db.ExecuteNonQuery("DELETE FROM Foo WHERE Id = 2;");                
            db.ExecuteNonQuery("INSERT INTO Foo (Bar) VALUES(50);");                
            Select(db);
        }

        while (true) ;
    }

    private static void Select(SQLiteDatabase db)
    {
        var result = db.ExecuteQuery("SELECT Id, Bar FROM Foo;");

        Debug.WriteLine("Rows selected:" + result.RowCount.ToString());

        var str = "";
        foreach (var j in result.ColumnNames) str += j + " ";

        Debug.WriteLine(str);

        foreach (ArrayList i in result.Data)
        {
            str = "";
            foreach (object j in i) str += j.ToString() + " ";

            Debug.WriteLine(str);
        }
    }
}

as far i know instead try this way

insert INTO Foo (Bar) VALUES(10);
insert INTO Foo (Bar) VALUES(20);
insert INTO Foo (Bar) VALUES(30);

because this version of sqlite is very lite for embedded

The same library from GHI with the older backend version of SQLite for NETMF works perfectly.
I’m reworking the project from NETMF to TinyCLR.

We now use a newer sqlite version but the new one is much larger on size with the option to reduce size by eliminating some options. Since this is for embedded devices we have opted in for smaller size (which is still huge!).

What options will or will not work is impossible to tell because the 2 versions are many years apart with many differences.

We would like to revisit this but if you need something working today then you need to make some changes.

Added an issue Sqlite supported statements · Issue #568 · ghi-electronics/Documentation · GitHub

OK I made the changes.

Occasionally, the following exception occurs.

#### Exception GHIElectronics.TinyCLR.Data.SQLite.QueryExecutionException - 0x00000000 (38) ####
#### Message: disk I/O error
#### GHIElectronics.TinyCLR.Data.SQLite.SQLiteDatabase::ExecuteNonQuery [IP: 002d] ####

I will continue to test it on other devices with different SD cards.

Please share a simple test to show the issue and we would investigate for you.

I can’t do a simple test, the problem occurs in a large multithreaded project, but I suspect a problem with the SD card. Calls to SQLite I do thread safe.

https://forums.ghielectronics.com/t/sd-card-help/23769/10?u=majo