Low-level SQLite (load_extension)

[jraichouni]

Hi!

I am trying to load a SQLite Extension without rebuilding sqlite shipped with Qt.

I read "QSqlDriver::handle()":http://qt-project.org/doc/qt-5/qsqldriver.html#handle and followed the following "Qt centre thread":http://www.qtcentre.org/threads/36131-Attempting-to-use-Sqlite-backup-api-from-driver-handle-fails but it does not work.

Does anybody has an idea, what is wrong?

My code:
@

sqlite_load_extension.pro

TEMPLATE = app
QT += sql
SOURCES += main.cpp
sqlite3.c
HEADERS += sqlite3.h
cache()
@

I got a segmentation fault in lines 23 and/ or 24

@
// main.cpp
#include <QGuiApplication>
#include <QSqlQuery>
#include <QString>
#include <QtDebug>
#include <QtSql>
#include <QVariant>
#include "./sqlite3.h"
int main(int argc, char argv[]) {
QGuiApplication app = new QGuiApplication(argc, argv);
QString db_file = QString("%1/xp-apt.sqlite").arg(app->applicationDirPath());
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(db_file);
db.open();
QVariant v = db.driver()->handle();
if (!v.isValid() || qstrcmp(v.typeName(), "sqlite3") != 0) {
qDebug() << "Cannot get a sqlite3 handle to the driver.";
}
sqlite3 handle = static_cast<sqlite3*>(v.data());
QString cmd = "SELECT 1";
QByteArray array = cmd.toLocal8Bit();
const char * c = array.data();

// sqlite3_exec(handle, c, NULL, NULL, NULL);
// sqlite3_enable_load_extension(handle, 1);

db.close();
return app->exec();

}
@

The project directory contains sqlite3.c/h from "SQLite3 Amalgamation Files":http://www.sqlite.org/2013/sqlite-amalgamation-3080200.zip

Hope to get help!

Cheers
jraichouni

[jraichouni]

I tried the other popular approach of rebuilding Qt src for qsqlite, but no success while
loading an extension.

Procedure:

1. Remov SQLITE_OMIT_LOAD_EXTENSION from DEFINES in file $QTDIR/../Src/qtbase/src/3rdparty/sqlite.pri
2. Add the following three lines to the file $QTDIR/../Src/qtbase/src/3rdparty/sqlite/sqlite3.c

@#ifndef SQLITE_ENABLE_LOAD_EXTENSION

define SQLITE_ENABLE_LOAD_EXTENSION 1

#endif@

3. Run:
   @
   cd $QTDIR/../Src/qtbase/src/plugins/sqldrivers/sqlite;
   make clean;
   rm -rf .obj/*;
   qmake -r -spec macx-clang CONFIG+=x86_64 sqlite.pro;
   make -j 4;
   make install
   @

Tried it with following mini project:

@

sqlite_load_extension.pro

TEMPLATE = app
QT += sql
SOURCES += main.cpp
cache()
@

@
// main.cpp
#include <QGuiApplication>
#include <QSqlQuery>
#include <QString>
#include <QtDebug>
#include <QtSql>
int main(int argc, char *argv[]) {
QGuiApplication app = new QGuiApplication(argc, argv);
QString db_file = QString("%1/xp-apt.sqlite").arg(app->applicationDirPath());
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(db_file);
db.open();
QSqlQuery q = new QSqlQuery(db);
if ( q > 0 ) {
q->exec("SELECT load_extension('libspatialite.5.dylib')"); // .dylib is in the dir of the executable
}
db.close();
return app->exec();
}
@

The application crashes in line 15. Does anybody know, what I forgot?

[jimmytaker]

The answer is already there, but not written explicitly. THE call that makes the difference between crash and no crash is sqlite3_open. Apparently having the sqlite3.dll plugin on one side and the sqlite3.c compiled in on the other (to be able to call any of the sqlite3 API directly messes up or misses some init. So basically what is needed is:

1. Include sqlite code (.c and .h files) in your project
2. Use this snippet after m_Database.open():

QVariant v = m_Database.driver()->handle();
if (v.isValid() && strcmp(v.typeName(), "sqlite3*") == 0) {
// v.data() returns a pointer to the handle
sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
if (handle != 0) { // check that it is not NULL
sqlite3 *p; //without this there is a crash.
int result = sqlite3_open( ":memory:", &p );
if (result == SQLITE_OK) {
sqlite3_close(p);
//call any API you need on handle.
} else
qDebug() << "Could not sqlite3_open p" << result;
} else {
qDebug() << "Could not get sqlite handle";
}
} else {
qDebug() << "handle variant returned typename " << v.typeName();
}