Efcore: Microsoft.Data.Sqlite 3.0.0-preview8 Throws SQLite Error 14

[RehanSaeed]

When upgrading to the latest Microsoft.Data.Sqlite 3.0.0-preview8.19405.11 NuGet package, my code now throws when trying to open a connection to an existing database which was created using version 2.2.6 of the same package:

```c#
using (var connection = new SqliteConnection("FileName=Foo.sqlite"))
{
connection.Open();
// ...
}

> Microsoft.Data.Sqlite.SqliteException
>   HResult=0x80004005
>   Message=SQLite Error 14: 'unable to open database file'.
>   Source=Microsoft.Data.Sqlite
>   StackTrace:
>    at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
>    at Microsoft.Data.Sqlite.SqliteConnection.Open()
>    at CortanaApp.Model.SqliteRepository.<.ctor>b__2_0() in C:\Git\CortanaApp\CortanaApp.Model\SqliteRepository.cs:line 33
>    at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

I've gone through the list of [breaking changes](https://www.bricelam.net/2019/08/22/microsoft-data-sqlite-3-0.html) and my code doesn't seem to be affected. I have two simple tables in my database created with the following SQL:

```sql
CREATE TABLE IF NOT EXISTS FeatureSetting(
    Key TEXT PRIMARY KEY, 
    Value TEXT NOT NULL, 
    LocalOverride INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS ClientOptions(
    Key TEXT PRIMARY KEY, 
    Value TEXT);

Is this a known bug? Should I wait for this package to be released?

[ericsink]

From your comment over in ericsink/SQLitePCL.raw#286 I know you are on UWP.

Which means you may be hitting the breaking change described in ericsink/SQLitePCL.raw#270

which is mentioned among other breaking changes for SQLitePCLRaw 2.0 here:

https://github.com/ericsink/SQLitePCL.raw/blob/master/v2.md

[RehanSaeed]

I tried adding this to my App.xaml.cs on app startup:

c# raw.sqlite3_win32_set_directory( 1, // Data directory type Windows.Storage.ApplicationData.Current.LocalFolder.Path);

However, I end up with the following exception:

System.NullReferenceException
HResult=0x80004003
Message=Object reference not set to an instance of an object.
Source=SQLitePCLRaw.core
StackTrace:
at SQLitePCL.raw.sqlite3_win32_set_directory(Int32 typ, String path)
at Foo.App..ctor() in C:\Git\Foo\Foo\App.xaml.cs:line 114
at Foo.Program.<>c.

b__0_0(ApplicationInitializationCallbackParams p) in

Does this call need to be made at a specific time? Also, what is the default path that Microsoft.Data.Sqlite version 2.2.6 used? I've assumed that it's Windows.Storage.ApplicationData.Current.LocalFolder.Path.

[ericsink]

The v1 code was doing two calls, including a temp directory, as shown below.

Also, you need to call these after calling SQLitePCL.Batteries_V2.Init() which M.D.SQLite may be doing for you, I don't recall.

Try putting these lines in App.xaml.cs on app startup:

SQLitePCL.Batteries_V2.Init();
SQLitePCL.raw.sqlite3_win32_set_directory(/*data directory type*/1, Windows.Storage.ApplicationData.Current.LocalFolder.Path);
SQLitePCL.raw.sqlite3_win32_set_directory(/*temp directory type*/2, Windows.Storage.ApplicationData.Current.TemporaryFolder.Path);

[RehanSaeed]

That seems to work, thanks @ericsink! I also upgraded to preview9 since it was out.

Now my only other problem is that my .NET Standard unit tests throw the following exception. Do I need to do anything to setup a .NET Standard test?

System.InvalidOperationException
HResult=0x80131509
Message=No data exists for the row/column.
Source=Microsoft.Data.Sqlite
StackTrace:
at Microsoft.Data.Sqlite.SqliteDataRecord.GetValue(Int32 ordinal)
at Utils.SqliteDataReaderExtensions.ReadStringOrEmpty(SqliteDataReader dataReader, String columnName)
at Utils.SqliteDataReaderExtensions.ReadGuid(SqliteDataReader dataReader, String columnName)
at Utils.Tests.SqliteDataReaderExtensionsTests.ReadGuid()

The test code looks like this:

```c#
[TestClass]
public class SqliteDataReaderExtensionsTests : ProfiledTest
{
private readonly DateTime arbitraryDate = new DateTime(1980, 4, 2, 1, 2, 3, DateTimeKind.Utc);
private readonly Guid arbitraryGuid = Guid.NewGuid();
private readonly long arbitraryDateFTUtc;
private readonly SqliteConnection connection;

    public SqliteDataReaderExtensionsTests()
    {
        this.arbitraryDateFTUtc = this.arbitraryDate.ToFileTimeUtc();

        var connectionString = $"Data Source=:memory:";
        this.connection = new SqliteConnection(connectionString);
        this.connection.Open();
        using (var cmd = this.connection.CreateCommand())
        {
            cmd.CommandText = $@"CREATE TABLE TestTable (Id INTEGER PRIMARY KEY, NumericNotNull NUMERIC NOT NULL, TextMaybeNull TEXT, GuidAsText TEXT NOT NULL);
                                INSERT INTO TestTable (Id, NumericNotNull, TextMaybeNull, GuidAsText) VALUES (1, {this.arbitraryDateFTUtc}, 'Text 1', '{this.arbitraryGuid}');
                                INSERT INTO TestTable (Id, NumericNotNull, TextMaybeNull, GuidAsText) VALUES (2, {this.arbitraryDateFTUtc}, null, '{this.arbitraryGuid}');";
            cmd.ExecuteNonQuery();
        }
    }

    [TestMethod]
    public void ReadStringOrEmptyTest()
    {
        using (var command = this.connection.CreateCommand())
        {
            command.CommandText = $"SELECT * from TestTable where Id = 1;";
            using (var reader = command.ExecuteReader())
            {
                Assert.IsTrue(reader.HasRows);
                Assert.AreEqual(reader.ReadStringOrEmpty("TextMaybeNull"), "Text 1");
                Assert.AreEqual(reader.ReadStringOrEmpty("NumericNotNull"), this.arbitraryDateFTUtc.ToString(CultureInfo.InvariantCulture));
            }
        }

        using (var command = this.connection.CreateCommand())
        {
            command.CommandText = $"SELECT TextMaybeNull from TestTable where Id = 2;";
            using (var reader = command.ExecuteReader())
            {
                Assert.IsTrue(reader.HasRows);
                Assert.AreEqual(reader.ReadStringOrEmpty("TextMaybeNull"), string.Empty);
            }
        }
    }

    [TestMethod]
    public void ReadDateTimeUtcFromLongTest()
    {
        using (var command = this.connection.CreateCommand())
        {
            command.CommandText = $"SELECT * from TestTable where Id = 1;";
            using (var reader = command.ExecuteReader())
            {
                Assert.IsTrue(reader.HasRows);
                var dateTime = reader.ReadDateTimeUtcFromLong("NumericNotNull");
                Assert.AreEqual(dateTime, this.arbitraryDate);
                Assert.ThrowsException<System.FormatException>(() => reader.ReadDateTimeUtcFromLong("TextMaybeNull"));
            }
        }
    }

    [TestMethod]
    public void ReadGuid()
    {
        using (var command = this.connection.CreateCommand())
        {
            command.CommandText = $"SELECT * from TestTable where Id = 1;";
            using (var reader = command.ExecuteReader())
            {
                Assert.IsTrue(reader.HasRows);
                var guid = reader.ReadGuid("GuidAsText");
                Assert.AreEqual(this.arbitraryGuid, guid);
                Assert.ThrowsException<System.FormatException>(() => reader.ReadGuid("TextMaybeNull"));
            }
        }
    }

    [TestCleanup]
    public void TestCleanup() => this.connection.Dispose();
}

```

[ericsink]

I'm not sure what's going wrong with the tests.

Most problems with using SQLitePCLRaw with tests come from initialization issues. We think of our tests as ".NET Standard tests", but actually they are running on a specific runtime, and it is necessary to setup and initialize SQLitePCLRaw for that runtime.

That said, the error message does not look like what I would expect, so the problem might be something else.

[RehanSaeed]

It's not obvious to be what is wrong with the code. I've tried several things. Here is a small repro project that I rustled up to show the problem:

https://github.com/RehanSaeed/SqliteRepro

[ericsink]

OK, I took a quick look at the repro project, and for the moment, I'm stumped.

The reported problem does repro easily. I get the same error shown above.

Changing just the Microsoft.Data.SQLite reference to 2.2.6 makes the tests pass.

Adding a call to SQLitePCL.Batteries_V2.Init() has no effect, so SQLitePCLRaw initialization is probably not the problem here.

On a whim, I changed the TFM from netcoreapp2.1 to netcoreapp3.0, but still no luck.

So for now I don't know if the problem here is related to SQLitePCLRaw or not.

I'm pretty sure this particular problem is unrelated to the where this issue started at the top. Thjis is .NET Core, so UWP is no longer in play.

cc @bricelam

[smitpatel]

Likely missing Read on SqliteDataRecord.

[bricelam]

👍 You need to call SqliteDataReader.Read() before getting values from it. This was a bug (worked by accident) that we fixed in 3.0

[bricelam]

More context:

ADO.NET | SQLite | Notes
--- | --- | ---
DbCommand.Prepare() | sqlite3_prepare()
DbCommand.Execute() | | Calls Prepare() if you didn't, wraps the sqlite3_stmt in a SqliteDataReader
DbDataReader.Read() | sqlite3_step()

It used to work by accident because we primed the reader by calling sqlite3_step() during DbCommand.Execute(). We had to change this to fix #13830 (the fix required a lot of subtle breaks like this)

[RehanSaeed]

I can't believe I hadn't spotted that! Thanks.