spatialite extension on windows doesn't work

[Tara]

I am trying to use sqlite3 with Spatialite for my web application. I have downloaded

• sqlite-dll-win32-x86-3200100 (32-bit DLL for SQLite version 3.20.1.)
• mod_spatialite-4.3.0a-win-amd86

I extracted both files and added to my path (C://spatialite). First the SQLlite archive then mod_spatialite, and overwrote any files. I also added the folder to the system path.

But if use in my flask application following line:

g.db.execute("select load_extension('C://spatialite//mod_spatialite')")

I got this error message:

the procedure entry point "_ZNSt8_detail15_List_node_base11_M_transferEPS0_S1_" could not be located in the DLL "C:\spatialite\libgeos-3-5-0.dll"

[mj10777]

And which geos version did you install in that directory? 

- the message tells you that is looking for 'libgeos-3-5-0.dll', which it cannot find

Once you supply that dynamic-library, it will ask your the next that it needs

- libproj and most likely libfreexml

Rarely do you find Dynamic-Libraries (dll/so) that are self contained (dependencies).

- such self-made package constellations will never work, if you do not supply everything that it needs

It is like making a potato omelet without eggs

- you will not get what you want

There are tools for this

- search for 'Windows dll dependencies tools' in google and the first result will show you what you need:

-- 'Dependency Walker'

(In linux this is called 'ldd')

Such tools read entries such as 'procedure entry point' and list its needs.

When creating self-made package constellations an absolute must, otherwise you must simply add (error for error) each dependency until its needs are satisfied.

Mark

[Tara]

In my directory (C:\spatialite) I have following files:

• libfreexl-1.dll
• libgcc_s_dw2-1.dll
• libgeos_c-1.dll
  
• libgeos-3-5-0.dll
• libiconv-2.dll
• liblzma-5.dll
• libproj-9.dll
• libsqlite3-0-dll
• libstdc++-6.dll
• libxml2-2.dll
• mod_spatialite.dll
• sqlite3.dll
• sqlite3.exe
• zlib1.dll
• my_database.sqlite

I downloaded the mod_spatialite-4.3.0a-win-x86.7z from this site (http://www.gaia-gis.it/gaia-sins/windows-bin-x86/), which contained already all DLL files....

the sqlite-dll-win32-x86-3200100.zip is from https://sqlite.org/download.htm. 

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

On Sun, 22 Oct 2017 01:46:45 -0700 (PDT), Tara wrote:
> In my directory (C:spatialite) I have following files:

> * libfreexl-1.dll
> * libgcc_s_dw2-1.dll
> * libgeos_c-1.dll
> * libgeos-3-5-0.dll
> * libiconv-2.dll
> * liblzma-5.dll
> * libproj-9.dll
> * libsqlite3-0-dll
> * libstdc++-6.dll
> * libxml2-2.dll
> * mod_spatialite.dll
> * sqlite3.dll
> * sqlite3.exe
> * zlib1.dll
> * my_database.sqlite
>
> I downloaded the mod_spatialite-4.3.0a-win-x86.7z [1] from this site
> (http://www.gaia-gis.it/gaia-sins/windows-bin-x86/ [2]), which

> contained already all DLL files....
>

> the sqlite-dll-win32-x86-3200100.zip [3] is from
> https://sqlite.org/download.htm.  [4]
>

Hi Tara,

you have already installed a SQLite DLL (libsqlite3-0-dll);
there is absolutely no reason to install a second copy
(sqlite3.dll). It's not only useless, it could even be
dangerous, because the two DLLs have been compiled by
different compilers (MinGW vs MSVC) and are of different
versions.

> I extracted both files and added to my path (C://spatialite).
> First the SQLlite archive then mod_spatialite, and overwrote
> any files. I also added the folder to the system path.
>

I'm unable to exactly understand what you've really done;
could you report the precise syntax of your commands ?

> I got this error message:
> the procedure entry point
> "_ZNSt8_detail15_List_node_base11_M_transferEPS0_S1_"
> could not be located in the DLL "C:\spatialite\libgeos-3-5-0.dll"
>

I've checked (by using the "nm" tool) the link symbols declared
by libgeos-3-5-0.dll (compiled by the MinGW compiler); it contains
the "__imp___ZNSt8__detail15_List_node_base11_M_transferEPS0_S1_"
symbol (please notice the "__imp__" decoration), and this fully
accounts for your reported failure.
The most obvious cause explaining such a mismatch is in that
you are actually using badly mixed DLLs of different origins
and probably created by different compilers (symbol's decorations
are handled in different ways by different compilers).

The following is a very simple diagnostic check:
1. open a cmd.exe command shell
2. type: cd C:\spatialite
this will change your current working directory
3. type: set PATH=.
this will restrict searching all DLLs only within
the current working directory
4. type: sqlite.exe
5. type: SELECT load_extension('mod_spatialite');
6. type: SELECT spatialite_version();
the expected result is "4.3.0"

if this test successfully passes (as I'm expecting), we
can then positively confirm that all DLLs downloaded
from within "mod_spatialite-4.3.0a-win-x86.7z" are
correctly working.
and in this case your next step will be the one to
understand why your application do actually loads
DLLs from different locations; the most probable
cause should be a badly set %PATH% variable.

hint: your actual %PATH% should not only cover all
required directories, but should also ensure the
right searching priorities.

bye Sandro

[Tara]

Hy Sandro,

I followed your hints. First of all I deleted the sqlite3.dll file, and now I just extracted the 64bit "mod_spatialite-4.3.0a-win-amd64.7z" archive. As Mark mentioned I installed the Dependency Walker Tool. My dll Files were 32bit, I had to download the 64bit file. Now I could fix one Error message. But now on the dependency Walker tool I can see another error Message:

Error: At least one required implicit or forwarded dependency was not found.
Warning: At least one delay-load dependency module was not found.

EXT-MS-WIN-RTCORE-NTUSER-WINDOW-EXT-L1-1-0.DLL
EXT-MS-WIN-SECURITY-CAPAUTHZ-L1-1-0.DLL
EXT-MS-WIN-SECURITY-EFSWRT-L1-1-1.DLL
EXT-MS-WIN-SHELL32-SHELLCOM-L1-1-0.DLL

Error opening file: System could not found the DLL.


What do you mean exactly with "badly set %PATH%" ? And how can I ensure the right searching priorities in my %PATH%?

bye Tara

[Tara]

I got it. I had to set my System Path to "C:\Python27\DLLs". Thanks a lot!!