Could Not Load Spatialite Extension in Qsqlite ( Qt 5.9)

Could not load Spatialite extension in qSqlite ( QT 5.9)

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); 

Use spatialite extension for SQLite on Windows

you probably don't have the folder in which libspatialite-2.dll is placed in your PATH.
Perhaps you can add the folder from within your Python script (I don't know any Python).
Or else you could add it from the Windows properties interface.

BTW you are using a very old version of spatialite: have a look here for newer versions:
https://www.gaia-gis.it/fossil/libspatialite/index

Load spatialite extension in RSQLite crashes R (OS X & Ubuntu)

It appears that recent versions of libspatialite (4.x) are problematic. In addition, loading the homebrewed libspatialite3 extension works, but subsequent queries resulted in the same segfault.

In the end, my (short-term, temporary, hackish) solution was to create an R package that compiles its own spatialite and loads it. RSQLite.spatialite can be found here.

QSqlTableModel and QSQLITE not showing correct columns & header

That is because you are overwriting it with a new QStandardItemModel, therefore every time the program runs all the headers disappears. Try to modify your constructor by commenting out the last three lines in the following way:

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    temporaryFolder = "/home/path/toDesktop/folder/a.db";
    QFile dbRem(temporaryFolder);
    dbRem.remove();

    mNewDatabaseImages = new dataInfo(this);
    mNewDatabaseImages->initDataBase(temporaryFolder);
    mNewDatabaseImages->confDataBase();
    mNewTableImages = new QSqlTableModel(this, mNewDatabaseImages->getDatabase());
    mNewTableImages->setTable("imageTable");
    mNewTableImages->select();
    ui->bookMarkTableView->setModel(mNewTableImages);

    // In this way you don't overwrite your initial headers

    //  model = new QStandardItemModel();
    //  ui->bookMarkTableView->setModel(model);
    //  ui->bookMarkTableView->showColumn(true);
}

---

Related Topics