[QT] Loading spatalite extension

[Артем Круковец]

I'm completely stuck with loading extensions in Qt's SQLite driver. I've done all steps from this 'how to'. Just changed SQLITE_ENABLE_LOAD_EXTENSION to DSQLITE_ENABLE_LOAD_EXTENSION.
After that I'm trying to run following code in my app:

QSqlQuery tSqlQuery;
QString sql_command_text;
sql_command_text = QString("SELECT load_extension(\"libspatialite-4.dll\")");
if(!tSqlQuery.exec(sql_command_text))
    qDebug() << tSqlQuery.lastError().text();

And qDebug gives me this: "not authorized Unable to fetch row". 

I'm using Qt 4.6.3 

What am I doing wrong? I've read tons of articles but couldn't find any solution.

You can be sure in following things:

1. I've done all steps from how-to
2. I've rebuild DLLs and replaced them

[mj10777]

If I remember correctly it is important that QT is compiled with external (native) sqlite3 support - by default it uses its internal version.
- you may have already changed that, but with external support it is better since it then must be done only once and all drivers created after that use those settings
-- also, what you may of missed (I have not looked at your link), this must be done BEFORE a database has been opened.
In our library we need a list of srids, so we create a memory db using 'SELECT InitSpatialMetadata()', afterwich the spatial capabilites are active (BUT only with external support)

The source of this library can be found at:
http://www.mj10777.de/cgi-bin/fossil/qgeomcoll/doc/tip/wiki/index.wiki

The main static class which works as an interface between QT and spatialite (but also gdal, proj4 etc) is call 'qgeomcoll':

http://www.mj10777.de/cgi-bin/fossil/qgeomcoll/artifact/922cea0c3c50d1f29ae303a4c270f1c466179db8

Most of the major functions that need spatial capabilities call 'QGeomColl::on_spatialite_loaded(true);'
- 'true': load if it not loaded
- 'false': clean up everything if active

This calls a function (only when not already active) called: init_sqlite_support
- it call the :memory db and fill our static list of srids

There is also a method to check if the spatial capabilities work after the database has been opened
- here you can see how to extract the native sqlit3 driver from the QT driver (also mysql,progres and mssql)
QGeomColl::driver_spatial_capable(QSqlDatabase sql_db,int i_debug)

But in the end, this MUST be done before the database has been opened.

Fill free to snoop around and use what you need.

Mark Johnson, Berlin Germany

[Артем Круковец]

I've downloaded spatialite-4.1-2.1-DLL-win-x86 and place all of them to debug and release folders, so now it throws unhandled exception from sqlite3.c (QTDIR/src/3rdparty/sqlite/sqlite3.c). 

Here is this line: 

#ifndef SQLITE_OMIT_LOAD_EXTENSION

SQLITE_PRIVATE   void sqlite3CloseExtensions(sqlite3*); 

#else

# define sqlite3CloseExtensions(X)            <----------- this line

#endif

This is what msvc shows:

First-chance exception at 0x00000000 in roadNetwork.exe: 0xC0000005: Access violation reading location 0x00000000.

Unhandled exception at 0x00000000 in roadNetwork.exe: 0xC0000005: Access violation reading location 0x00000000.

Here's my function which connects to db and trying to load spatalite extension: 

connectToDb(){

sdb = QSqlDatabase::addDatabase("QSQLITE");

QString dbName = dirPath->path() + "/roads.sqlite";

sdb->setDatabaseName(dbName);

if (!sdb->open()) {

connection = false;

return false;

}

QSqlQuery tSqlQuery;

QString sql_command_text;

sql_command_text = QString("SELECT load_extension(\"libspatialite.dll\")");

if (!tSqlQuery.exec())

{

qDebug() << tSqlQuery.lastError().text();

connection = false;

return false;

}

connection = true;

return true;

}

I just like to mention that I've rebuild qt sqlite library without defining SQLITE_OMIT_EXTENSION in sqlite.pro file (qt's)  and adding following lines to sqlite3.c (qt's as well):

     #ifndef SQLITE_ENABLE_LOAD_EXTENSION

     # define SQLITE_ENABLE_LOAD_EXTENSION 1

     #endif

 
воскресенье, 1 декабря 2013 г., 9:42:24 UTC+4 пользователь mj10777 написал:

[a.fu...@lqt.it]

> QSqlQuery tSqlQuery;
> QString sql_command_text;
> sql_command_text = QString("SELECT
> load_extension("libspatialite-4.dll")");
> if(!tSqlQuery.exec(sql_command_text))
> qDebug()
>

Hi Artem,

I'm not a QT user, so I'm not really sure about this.
anyway it looks like if you've forgot to call in your
code sqlite3_enable_load_extension() *before* attempting
to load the external extension.

this will then obviously cause a "not authorized" exception
simply because loading any external extension is set to
"off" by default.

please note: by passing the -DSQLITE_ENABLE_LOAD_EXTENSION
flag at build time you'll simply instruct the compiler to
effectively activate the load-extension supporting code;
but you still have to explicitly call at run time
sqlite3_enable_load_extension() before attempting to
effectively load an extension.

please see:
http://www.sqlite.org/c3ref/enable_load_extension.html

bye Sandro

[Артем Круковец]

Ok, as you suggested I tried to implement following code:

        QString dbName = dirPath->path() + "/roads.sqlite";

sdb->setDatabaseName(dbName);

if (!sdb->open()) {

connection = false;

return false;

}

QVariant v = sdb->driver()->handle();

if (v.isValid() && qstrcmp(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_enable_load_extension(handle,1);

}

}

QSqlQuery tSqlQuery("SELECT load_extension(\"libspatialite.dll\")", *sdb);

So, now sqlite's api function throws unhanded exception: 

SQLITE_API void sqlite3_mutex_enter(sqlite3_mutex *p){

  if( p ){

    sqlite3GlobalConfig.mutex.xMutexEnter(p);        <----------- this line

  }

}

It seems to me that reason causes this errors (from my message before) is the same because it throws the same exception:

First-chance exception at 0x00000000 in roadNetwork.exe: 0xC0000005: Access violation reading location 0x00000000.

Unhandled exception at 0x00000000 in roadNetwork.exe: 0xC0000005: Access violation reading location 0x00000000.

воскресенье, 1 декабря 2013 г., 15:13:54 UTC+4 пользователь sandro furieri написал:

[mj10777]

On Sunday, 1 December 2013 12:42:30 UTC+1, Артем Круковец wrote:

Ok, as you suggested I tried to implement following code:

        QString dbName = dirPath->path() + "/roads.sqlite";

sdb->setDatabaseName(dbName);

if (!sdb->open()) {

connection = false;

return false;

}

QVariant v = sdb->driver()->handle();

if (v.isValid() && qstrcmp(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_enable_load_extension(handle,1);

}

}

Why 'SELECT load_extension('
- the next step done in the above code does this:
-- we will never know what or where the dll is [under linux it is .so] called, so 'SELECT load_extension(' is never called.
--- I remember hearing that under windows it was difficult place the dll where sqlite could find it

     // At the lastest here,we ensure that - if needed - sqlite3_enable_load_extension is called.
     if (sqlite3_exec(this_handle_sqlite3,"SELECT InitSpatialMetadata()",NULL,NULL,NULL) == SQLITE_OK)
     { // good,we should now have all the basic information needed
      if (list_srid.count() == 0)
      { // Should only be done once
       list_srid=QGeomColl::list_proj_srid(); // Needs: 'handle_sqlite3_memory'
      }
     }
 

[Артем Круковец]

I couldn't even call  sqlite3_enable_load_extension() it files with unhandled exception:

QString dbName = dirPath->path() + "/roads.sqlite";

sdb->setDatabaseName(dbName);

if (!sdb->open()) {

connection = false;

return false;

}

QVariant v = sdb->driver()->handle();

if (v.isValid() && qstrcmp(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_enable_load_extension(handle,1);

}

}

QSqlQuery tSqlQuery("SELECT load_extension(\"libspatialite.dll\")", *sdb);

So, now sqlite's api function throws unhanded exception: 

SQLITE_API void sqlite3_mutex_enter(sqlite3_mutex *p){

  if( p ){

    sqlite3GlobalConfig.mutex.xMutexEnter(p);        <----------- this line

  }

}

It seems to me that reason causes this errors (from my message before) is the same because it throws the same exception:

First-chance exception at 0x00000000 in roadNetwork.exe: 0xC0000005: Access violation reading location 0x00000000.

Unhandled exception at 0x00000000 in roadNetwork.exe: 0xC0000005: Access violation reading location 0x00000000.

Actually, I'm completely stuck with this, I've read every doc related with sqlite3_enable_load_extension but there is no answer anywhere.

May be I'm using wrong sqlite3 library? (I use that one provided with Qt 4.6.3)

воскресенье, 1 декабря 2013 г., 16:53:50 UTC+4 пользователь mj10777 написал:

[a.fu...@lqt.it]

On Sun, 1 Dec 2013 06:26:10 -0800 (PST), Артем Круковец wrote:
> I couldn't even call sqlite3_enable_load_extension() it files with
> unhandled exception:
>

> <snip>

>
> May be I'm using wrong sqlite3 library? (I use that one provided with
> Qt 4.6.3)
>

Hi Artem,

I've no idea about the version of libsqlite3 internally provided
with QT 4.6.3, but it probably correspond to some obsolete version.

anyway, you can download from here the most recent 3.8.1:
http://www.sqlite.org/2013/sqlite-amalgamation-3080100.zip

and accordingly to the following QT document building always
using the most recent libsqlite seems to always be the better
option:
http://sourceforge.net/apps/mediawiki/maille/index.php?title=Install#SQLite

On Sun, 1 Dec 2013 04:53:50 -0800 (PST), mj10777 wrote:
> Why 'SELECT load_extension('
> - the next step done in the above code does this:
> - we will never know what or where the dll is [under linux it is .so]
> called,
> so 'SELECT load_extension(' is never called.

please note: the portability issue affecting .dll / .so / .dylib
platform specific file extension is no longer valid.
starting since 3.7.17 SQLite has completeley revolutionized its
LOAD EXTENSION mechanisms, and now appending the appropriate suffix
is internally hanlded by SQLite itself.
and this one surely marks a big improvement, as explained in the
SQLite's own documentation:

"Enhance the extension loading mechanism to be more flexible (while
still maintaining backwards compatibility) in two ways:
- If the default entry point "sqlite3_extension_init" is not present in
the loadable extension, also try an entry point "sqlite3_X_init"
where "X" is based on the shared library filename. This allows every
extension to have a different entry point, which allows them to be
statically linked with no code changes.
- The shared library filename passed to sqlite3_load_extension() may
omit
the filename suffix, and an appropriate architecture-dependent suffix
(".so", ".dylib", or ".dll") will be added automatically."

side effect: the most recent versione of libspatialite now adopts
the "new styled" entry point name "sqlite3_spatialite_init"

> - I remember hearing that under windows it was difficult place the
> dll
> where sqlite could find it
>

it's not a SQLite specific problem; it's the overall design of the
Windows
DLL's architecture which is rather cumbersome.
here you can get more precise informations:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586%28v=vs.85%29.aspx

bye Sandro

[Артем Круковец]

It seems to me that libsqlite3-0.dll provided in spatialite-4.1.1-DLL-win-x86.zip doesn't work as expected.

I think so because of I've downloaded SQLite 3.7.17 Shell (<-- link), placed it to the downloaded folder with ell, run it 

and type .load ./libspatialite-4 (.load ./libspatialite-4.dll) and it crashed with error in libsqlite3-0.dll

I've done the same things with libspatialite-2 DLLs and it loaded them perfect. 

Actually, I'm not sure with all this things, but it would be great if you can check them :)

воскресенье, 1 декабря 2013 г., 19:01:46 UTC+4 пользователь sandro furieri написал: