Could not load Spatialite extension in qSqlite ( QT 5.9)

Question

I am trying to load Spatialite as extension in qSqlite ( Qt 5.9), I have done that before with Qt4.8, but I failed with the QT5.9. I changed the sqlite.pri by removing "SQLITE_OMIT_LOAD_EXTENSION", and I did some change on the sqlite.c by removing the "#define SQLITE_OMIT_LOAD_EXTENSION 1", and adding "#define SQLITE_ENABLE_LOAD_EXTENSION 1". I also add the following lines to openDatabase(....)

#if defined(SQLITE_ENABLE_LOAD_EXTENSION)
| SQLITE_LoadExtension|SQLITE_LoadExtFunc
#endif

Now the "requet.setQuery("SELECT load_extension('spatialite')", dbProject);" function is recognized, but I got this message: error "The specified procedure could not be found.\r\nUnable to fetch row" If I take a look on my debug output in MSVC14, I can see that the spatialite.dll and all its dependencies have been loaded.

Notice: I tested this with my Spatialite and also the mod_spatialite that I download form their website.

Any ideas about this problem? Thanks in advance.

Answer 1

Thanks for eyllanesc, I found the solution thanks to your explanations and suggestions.

The problem was with my compiled library (still i don't know why?), and also with the mod_spatialite that I downloaded. This last one when we use it with the visual studio need a replacement for the libstdc++_64-6.dll because it will cause crash.

My problem was here, the one that I used was not the good one, and was causing the The specified procedure could not be found, so I downloaded the x86_64-5.3.0-release-win32-seh-rt_v4-rev0 and I used the libstdc++-6.dll (I changed the name to libstdc++_64-6.dll) with the libgcc_s_seh-1.dll. I also changed the libxml2-2.dll with another one that I compiled before.

Then I used one of the following solutions:

Solution 1: I used the code before with modification because the ret = sqlite3_exec (db_handle, sql, NULL, NULL, &err_msg); was causing crash, and also I sent the db via parameters because it does not work like it was in the previous code.

so my working code now is:

#include <QtCore/QCoreApplication>
#include <QtSql/QtSql>
#include "sqlite3.h"

int enable_spatialite(QSqlDatabase db) {
    QVariant v = db.driver()->handle();
    if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0)
    {
        sqlite3_initialize();
        sqlite3 *db_handle = *static_cast<sqlite3 **>(v.data());

        if (db_handle != 0) {

            sqlite3_enable_load_extension(db_handle, 1);

            QSqlQueryModel sql;
            sql.setQuery("SELECT load_extension('mod_spatialite')", db);
            if (sql.lastError().isValid())
            {
                qDebug() << "Error: cannot load the Spatialite extension (" << sql.lastError().text()<<")";
                return 0;
            }
            else    return 1;
        }
    }
    return 0;
}

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "Project");
    db.setDatabaseName("dbTest.db");
    if (!db.open())
    {
        qDebug()<<"Critical"<< "Impossible to intialize the database !\n" + db.lastError().text();
        return 0;
    }
    qDebug()<<enable_spatialite(db);

    //just a test
    QSqlQueryModel sql;
    sql.setQuery("SELECT HEX(GeomFromText('POINT(10 20)'));", db);
    qDebug() << sql.index(0, 0).data().toString();

    return a.exec();
}

one important things also, is that I recompiled the qsqlite driver to remove the SQLITE_OMIT_LOAD_EXTENSION

Solution 2: (Direct solution)

open the "Qt5.9.0\5.9\Src\qtbase\src\3rdparty\sqlite\" folder and change the sqlite3.c as following:

1. comment or remove the #define SQLITE_OMIT_LOAD_EXTENSION 1

2. add:

---

#ifndef SQLITE_ENABLE_LOAD_EXTENSION
#define SQLITE_ENABLE_LOAD_EXTENSION 1
#endif 

3. go the static int openDatabase( const char *zFilename,sqlite3 **ppDb, unsigned int flags, const char *zVfs) function and add | SQLITE_LoadExtFunc to SQLITE_ENABLE_LOAD_EXTENSION as following:

---

#ifdef SQLITE_ENABLE_LOAD_EXTENSION
                 | SQLITE_LoadExtension | SQLITE_LoadExtFunc
#endif 

4. compile your plugin again using your compiler ( nmake in my case) in Qt5.9.0\5.9\Src\qtbase\src\plugins\sqldrivers\sqlite\sqlite.pro

5. call the following code to load your spatialite:

---

QSqlQueryModel sql;
sql.setQuery("SELECT load_extension('mod_spatialite')", db);