Microsoft.Data.Sqlite: Can't load extensions that modify existing functions or collations

[krazado]

LoadExtension method from SqliteConnection(Microsoft.Data.Sqlite) throws exception, but same method from SQLiteConnection(System.Data.SQLite) works fine.

I built a ICU extension and trying to load it with efcore, ef uses the SqliteConnection which is root reason of the problem, because I tried to create new SqliteConnection and same error is thrown, but as I stated out exactly same setup works fine with SQLiteConnection(System.Data.SQLite).

here is the code snippets, but I attached complete working project with both examples.

 //this is throwing exception 
        public static void LoadICUWithSqliteConnection(SqliteConnection sqliteConn)
        {
            try
            {                              
                if (sqliteConn.State == System.Data.ConnectionState.Closed)
                    sqliteConn.Open();

                if (sqliteConn != null
                    && File.Exists(ICUPath))
                {
                    sqliteConn.EnableExtensions(true);
                    sqliteConn.LoadExtension(ICUPath, "sqlite3_icu_init");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

//this works like it should
        public static void LoadICUWithSQLiteConnection(SQLiteConnection sqliteConnection)
        {
            try
            {

                if (sqliteConnection != null
                    && sqliteConnection.State == System.Data.ConnectionState.Open
                    && File.Exists(ICUPath))
                {
                    sqliteConnection.EnableExtensions(true);
                    sqliteConnection.LoadExtension(ICUPath, "sqlite3_icu_init");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'error during initialization: '.'

Include version information

Microsoft.Data.Sqlite version:5.0.10.0
Target framework: NET 5.0
Operating system: Windows 10

Unfortunately I could not upload .zip here, it says file is not supported, but here is the public link and you can download sample project: https://drive.google.com/file/d/1o7mXRxnNNFU1CFLokI106YTUQ6z4z5yC/view?usp=sharing

[ajcvickers]

/cc @bricelam

[bricelam]

Two native libraries (icuin69.dll and icuuc69d.dll) seem to be missing from the sample...

[krazado]

I added all missing dlls.
I have had included path from different location in the Path env variable and it was working for me

[bricelam]

The load_extension() SQL function will fail if the extension attempts to modify or delete an SQL function or collating sequence. The extension can add new functions or collating sequences, but cannot modify or delete existing functions or collating sequences because those functions and/or collating sequences might be used elsewhere in the currently running SQL statement. To load an extension that changes or deletes functions or collating sequences, use the sqlite3_load_extension() C-language API.

@ericsink Would it be possible to add sqlite3_load_extension to SQLitePCLRaw?

[bricelam]

Just for fun, here's the horrible C code I had to write to get to the bottom of this. 😬 Don't bother pointing out all the leaked memory; handling all those result codes was painful enough.

#include <cassert>
#include <cstdio>
#include "sqlite3.h"

int main()
{
    sqlite3* db;
    int rc = sqlite3_open(":memory:", &db);
    assert(rc == SQLITE_OK);

    rc = sqlite3_enable_load_extension(db, 1);
    assert(rc == SQLITE_OK);

    sqlite3_stmt* stmt;
    rc = sqlite3_prepare_v2(db, "SELECT load_extension('ICU_2017_V2', 'sqlite3_icu_init')\0", -1, &stmt, NULL);
    assert(rc == SQLITE_OK);

    rc = sqlite3_step(stmt);
    if (rc == SQLITE_ERROR)
    {
        printf(sqlite3_errmsg(db));
    }
}

[bricelam]

Note, you can work around this limitation by writing your own P/Invoke code.

using System.Data;
using System.Runtime.InteropServices;
using System.Text;
using Microsoft.Data.Sqlite;
using SQLitePCL;

using static SQLitePCL.raw;

static class SqliteConnectionExtensions
{
    public static void LoadExtension2(this SqliteConnection connection, string file, string proc)
    {
        if (connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Connection must be open.");

        var rc = sqlite3_load_extension(
            connection.Handle!,
            ToUTF8(file),
            ToUTF8(proc),
            out var errMsg);

        if (rc != SQLITE_OK)
            throw new SqliteException(Marshal.PtrToStringUTF8(errMsg), rc);
    }

    [DllImport("e_sqlite3", CallingConvention = CallingConvention.Cdecl)]
    static extern int sqlite3_load_extension(sqlite3 db, byte[] zFile, byte[] zProc, out IntPtr errMsg);

    static byte[] ToUTF8(string value)
    {
        var length = Encoding.UTF8.GetByteCount(value);
        var result = new byte[length + 1];

        Encoding.UTF8.GetBytes(value, result);
        result[length] = 0;

        return result;
    }
}

sqliteConn.LoadExtension2(ICUPath, "sqlite3_icu_init");