Spatialite 4.1.1 and ODBC - Windows 7

[Goyo]

Dear all,

I am having a really hard time trying to use spatialite 4.1.1 with ODBC in Windows 7 (32 bits). My goal is to access a database with libreoffice. This is what I have done so far:

- Install the sqlite ODBC driver from http://www.ch-werner.de/sqliteodbc
- Download spatialite-4.1.1-DLL-win-x86.zip, unzip and copy all files to c:\windows\sistem32 (I did not find prebuilt dlls for 4.2.0)
- Create a ODBC datasource pointing to the database. This works quite well, except of course for spatialite-specific stuff.
- In the ODBC DSN configuration dialog, set "Load extensions" to libspatialite-4 (or libspatialite-4.dll). After that libreoffice crashes when trying to connect to the database. No meaningful error messages, just the libreoffice recovery dialog.

It looks like the ODBC driver and spatalite dll don't play well toguether. If I open the sqlite shell that comes with the driver and type ".load libspatialite-4" or "SELECT load_extension('libspatialite-4')" shell also crashes without any useful message (just a "sqlite3.exe stopped working due to a problem, close the program." dialog).

Maybe someone got this working and can lend me a hand?

BTW zero issues in Ubuntu using standard packages + ubuntugis-unstable. But I need this to work in windows...

Regards

Goyo

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

On Tue, 29 Jul 2014 06:15:30 -0700 (PDT), Goyo wrote:
> Dear all,
>
> I am having a really hard time trying to use spatialite 4.1.1 with
> ODBC in Windows 7 (32 bits). My goal is to access a database with
> libreoffice. This is what I have done so far:
>
> - Install the sqlite ODBC driver from
> http://www.ch-werner.de/sqliteodbc
> - Download spatialite-4.1.1-DLL-win-x86.zip, unzip and copy all files

> to c:windowssistem32 (I did not find prebuilt dlls for 4.2.0)
>

Hi Goyo,

please notice: starting since 4.2.0 loading the dynamic extension
on the top of SQLite3 is now based on "mod_spatialite" (a pure
extension module, not a classic shared library).
You can eventually read more technical details from here:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite

and consequently the binary Windows packages (including any DLL)
are now the followings:

http://www.gaia-gis.it/gaia-sins/windows-bin-x86/mod_spatialite-4.2.0-win-x86.7z
http://www.gaia-gis.it/gaia-sins/windows-bin-amd64/mod_spatialite-4.2.0-win-amd64.7z

> - Create a ODBC datasource pointing to the database. This works quite
> well, except of course for spatialite-specific stuff.
> - In the ODBC DSN configuration dialog, set "Load extensions" to
> libspatialite-4 (or libspatialite-4.dll). After that libreoffice
> crashes when trying to connect to the database. No meaningful error
> messages, just the libreoffice recovery dialog.
>
> It looks like the ODBC driver and spatalite dll don't play well
> toguether. If I open the sqlite shell that comes with the driver and
> type ".load libspatialite-4" or "SELECT
> load_extension('libspatialite-4')" shell also crashes without any
> useful message (just a "sqlite3.exe stopped working due to a problem,
> close the program." dialog).
>

we've defintely abandoned the "classic shared library" approach
exactly because such a configuration had many stability issues,
as clearly reported by many C# and Java users.
the new "mod_spatialite" approach seems to definitely resolve
any issue ensuring a fair good stability for all language
connectors requiring to initialize SpatiaLite as a dynamic
loadable extension. Exactly as is the case for Win ODBC.

> Maybe someone got this working and can lend me a hand?
>

I've never personally tested ODBC + OO on Windows; but my
reasonable expectation is that after installing the brand
new "mod_spatialite" 4.2.0 it should work ... let me know ;-)

bye Sandro

[Goyo]

Thanks Sandro. I have read about the new mod_spatialite before, but I completely forgot it until you reminded me. Now I am almost where I want to:

- After downloading and unzipping mod_spatialite-4.2.0-win-x86.7z and copying the files to c:\windows\system32 I can load the extension in the sqlite shell.

- I can not create an ODBC datasource which loads the extension. I write mod_spatialite in the Load extension text box but I get an error connecting to the datasource: "Extension 'C:\Windows\system32\mod_spatialite' did not load: No se encontró el proceso especificado." (spanish for "Did not find the specified process.") I tried the full path and mod_spatialite.dll with the same result. Applications (LibreOffice, MS Office...) can connect to the datasource but spatialite is not available.

- The good news: I can execute "SELECT load_extension('mod_spatialite');" from the application and after that the full power of spatialite becomes available.

Anyway it would be more convenient to just specify the extension in the ODBC datasource and get it loaded automatically. If somebody knows how to do this please let me know.

Best regards

Goyo

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

Hi Goyo,

On Wed, 30 Jul 2014 01:06:01 -0700 (PDT), Goyo wrote:
> Thanks Sandro. I have read about the new mod_spatialite before, but I
> completely forgot it until you reminded me. Now I am almost where I
> want to:
>
> - After downloading and unzipping mod_spatialite-4.2.0-win-x86.7z and

> copying the files to c:windowssystem32 I can load the extension in
> the
> sqlite shell.
>

:-D

> - I can not create an ODBC datasource which loads the extension. I
> write mod_spatialite in the Load extension text box but I get an
> error
> connecting to the datasource: "Extension

> 'C:Windowssystem32mod_spatialite' did not load: No se encontró el

> proceso especificado." (spanish for "Did not find the specified
> process.") I tried the full path and mod_spatialite.dll with the same
> result. Applications (LibreOffice, MS Office...) can connect to the
> datasource but spatialite is not available.
>

please note: this could be possibly related to the version of SQLite3
used by the ODBC connector.
there were several changes affecting "load_extesion" in recent times
(more or less starting since version 3.7.17 2013-05-20)

new versions will always attempt to locate the extension on the most
obvious search paths, and will automatically append the appropriate
platform suffix (.dll or .so or .dylib or whatever else) to the
extension's base-name.
old versions were much more limited, and very often required to specify
an absolute path in order to correctly locate the extension.

that's not all; in new versions the default entry point has a different
name than the one assumed by older versions.
anyway loading any "new style" extension using an obsolete sqlite3
should always be possible, but surely requires a different syntax:

SELECT load_extension(module-name, entry-point-name);

bye Sandro

[Goyo]

I'm using the latest version of the driver, which uses sqlite 3.8.5. Also load_extension('mod_spatialite') works in applications using the connector and in the sqlite shell provided with the connector.

Anyway loading the extension from the client appllication is doing for me so far.

Goyo