C# SQLite FTS5 Table and Triger creation

Question

I am creating a virtual table with sqlite fts5 and I am having the following error message: SQL Logic error no such module: FTS5. Below is my code: Using Package manager in VS 2017 I have already download SQLite and SQLite FTS5.

    private static void CreateReport()
    {
        try
        {
            using (SQLiteConnection sqliteConnection = new SQLiteConnection(DataSources.LocalConnectionString()))
            {
                sqliteConnection.Open();
                sqliteConnection.EnableExtensions(true);
                string commandText = "CREATE TABLE IF NOT EXISTS JReport(JRId INTEGER PRIMARY KEY, IDId INTEGER, CaseId INTEGER, BoxName TEXT, JRText TEXT, JRFormatted TEXT)";
                string commandText1 = "CREATE VIRTUAL TABLE IF NOT EXISTS DReport USING FTS5(JRId, CaseId, BoxName, CONTENT = 'JReport', CONTENT_ROWID = 'JRId')";
                string commandText2 = "CREATE TRIGGER DocRepo AFTER INSERT ON JReport BEGIN INSERT INTO DReport(RowId, JRId, CaseId, BoxName) VALUES(NEW.JRId, NEW.CaseId, NEW.BoxName) END";
                using (SQLiteCommand sqliteCommand = new SQLiteCommand(commandText, sqliteConnection))
                {
                    sqliteCommand.ExecuteNonQuery();
                    sqliteCommand.Dispose();
                }
                using (SQLiteCommand sqliteCommand = new SQLiteCommand(commandText1, sqliteConnection))
                {
                    sqliteCommand.ExecuteNonQuery();
                    sqliteCommand.Dispose();
                }
                using (SQLiteCommand sqliteCommand = new SQLiteCommand(commandText2, sqliteConnection))
                {
                    sqliteCommand.ExecuteNonQuery();
                    sqliteCommand.Dispose();
                }
                sqliteConnection.Close();
            }
        }
        catch (Exception ex)
        {
            MessageBoxEx.Show("An error has occurred while creating the Report table, the original error is: " +
                ex.Message, "Report", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

Answer 1

SQL Logic error no such module: FTS5.

Just like the error message says, your sqlite3 library doesn't have the FTS5 module. It probably wasn't configured to include it as a built in one when the library was built, as it's not enabled by default. It might have been made available as a dynamically loadable module by whoever did configure and build the library you're using. Or not.

Personally, I just always include a copy of sqlite3.c in any program I'm using that uses it to avoid relying on an external dependency and so you can make sure you're always using a version with all the features you want to use present. Dunno what you'd have to do in C# to use your own local instance, but I'm sure there's a way.

Instructions for building FTS5 into sqlite3 or as a loadable module.

Answer 2

I solved this error message "SQL Logic error no such module: FTS5." by using "LoadExtension" command:

MyConnection.EnableExtensions(true);
MyConnection.LoadExtension("SQLite.Interop.dll", "sqlite3_fts5_init");