load_extension does not find mod_spatialite.dll in x64 using .net

[Olivier]

Hi everyone,

I have a quite annoying problem, everything woks fine on my x64 machine but whenI am trying to build my c#/.net project on a x64 machine I have this [unable to find the module] error.

1. My application is compiled as AnyCPU

2. I have the package System.Data.SQLite(x86/x64) installed

3. My application use SQLite.Interop.dll

4. I downloaded the 64bit version of spatialite "mod_spatialite-4.3.0a-win-amd64"

I unzipped all the dlls to my application directory, and inside my code source I use the same code as SpatialiteSharp.SpatialiteLoader : 

  var spatialitePath = Path.Combine(Path.GetDirectoryName(Util.AssemblyFile.AssemblyDirectory), (Environment.Is64BitProcess ? "x64" : "x86"), "spatialite");

  conn.LoadExtension("mod_spatialite.dll");

Everything works fine with my 32bits machine but with the 64bits I have the error [unable to find the module], like it does not find the correct path to my spatialite dlls...

Any ideas?

[Pac]

Hi Olivier,

SQLite.Interop.dll and mod_spatialite.dll are platform dependent, so you must check that its x64 versions are used. Also, mod_spatialite.dll depends on several other DLLs which should be found besides your executable or in a directory included in your PATH environment variable (what are you using 'spatialitePath' variable for?)

For another example and ideas, you could check this post in the forum.

Regards,
Paco.

[Olivier]

Hi Pac,

Thank you for your answer.

In my bin/Debug folder of my application I have a x86 and an x64 folder for my SQLite.Interop.dll. 

For the mod_spatialite I tryed several things : 

1. Unzip the content of mod_spatialite-4.2.0-win-amd64 to the bin/Debug folder of my application, and add my Util.AssemblyFile.AssemblyDirectory to my system variable "Path" => doe's not work on my x64 machine (works for my x86 machine with mod_spatialite-4.2.0-win-x86 spatialite dlls)

2. Use the SpatialiteSharp nuget package which does almost the same thing except that the spatialite dlls are put in the two different folders x64 & x86 in my bin/Debug folder of my application and the system variable added to the "Path" is the spatialitePath you saw earlier

My project is build under "Any CPU"

Best regards,

Olivier. 

[Pac]

Hi Olivier,

It's OK to have both x86 and x64 directories in your output path: there System.Data.SQLite package will copy the different SQLite.Interop DLLs; at runtime the desired native DLL will be loaded. The same applies to SpatialiteSharp.

Also, you have to prepend your mod_spatialite's directory to your PATH variable, so it will be searched first. This must be done only once in your application, because this change will last until your application ends.

But if you are using SpatialiteSharp package you don't have to mess with any environment variable, only invoke its SpatialiteLoader.Load method on any SQLite connection.

Regards,
Paco.

[olivier leprêtre]

Hi,

Coming back to this old post.

A Visual Studio is a 32 bits system

I finally downloaded http://www.gaia-gis.it/gaia-sins/windows-bin-x86/mod_spatialite-4.3.0a-win-x86.7z on a Windows 7/X64 machine

Expanded in a folder ex : D:\spatialite

Added folder to path

Add these commands to load module in Visual Studio 2010 VB.net

connSq = New SQLiteConnection(cnx)

connSq.Open()

connSq.EnableExtensions(True)

connSq.LoadExtension("mod_spatialite")

After several "SQL logic error or missing database Procedure is not found" This finally worked.

Not understanding really why. So I wanted to do the same on another machine Windows 10/X64/Visual studio 2013. I copied the project, set .net framework to 4.5.1, added nuget sqlite version, copied the D:\spatialite folder and added it to the path. And once again I get this error message, not explaining precisely the problem. 

There is no problem to open the database with spatialite

Any suggestion will be appreciated.

Olivier

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

On Mon, 21 Nov 2016 01:25:59 -0800 (PST), olivier leprêtre wrote:
>
> downloaded http://www.gaia-gis.it/gaia-sins/windows-bin-x86/mod_spatialite-4.3.0a-win-x86.7z
> on a Windows 7/X64 machine
> Expanded in a folder ex : D:spatialite
>

> -------------- <snip< -------------
>
> ... on another machine Windows 10/X64/Visual studio 2013
> I copied the D:\spatialite folder and added it to the path.

> And once again I get this error message
>

Hi Olivier,

I'm not really sure to correctly understand what you
are saying: did you copied the same mod_spatialite
working on a 32 bit platform on your new 64 bit .net ?

if this is what you really did it's obvious that it
can't work, because you can never mix 32 and 64 bit
executable code.

the appropriate package for a 64 bit installation is
http://www.gaia-gis.it/gaia-sins/windows-bin-amd64/mod_spatialite-4.3.0a-win-amd64.7z

bye Sandro

[olivier leprêtre]

Hi Sandro,

Forget about the very first post that was about x64. I stopped trying this because Visual Studio is still a 32 bits application. So I used the x86 version.http://www.gaia-gis.it/gaia-sins/windows-bin-x86/mod_spatialite-4.3.0a-win-x86.7z  this which works on an x64 machine, and I copied it to another x64 machine.  

The main difference seems to be VS 2010/Windows 7 on the first machine, VS 2013/Windows 10 on second one. Not sure if this is the cause. What is frustrating is this message "SQL Logic error or missing database. Procedure can't be not found" which does not help to find the solution. Is it really because mod_spatialite.dll is not found, even if folder is in the path, or is it something completely different.

Olivier

[Yvan B]

Hi Olivier,

I also use spatialite in some VB.NET projects and it works fine.  I'm using Visual Studio 2015 Community Edition and here is the way I do:

- download the Nuget Package for System.data.SQLite

- in the Project Properties / Compile : choose x86 as target CPU

- create a "spatialite" folder in the project directory and extract the content of http://www.gaia-gis.it/gaia-sins/windows-bin-x86/mod_spatialite-4.3.0a-win-x86.7z inside this folder

- run the following code to update the path environment at the begining of the code (VB.NET):

slPath = Environment.GetEnvironmentVariable("Path", EnvironmentVariableTarget.Machine) & ";" & My.Application.Info.DirectoryPath & "\spatialite"

Environment.SetEnvironmentVariable("Path", slPath, EnvironmentVariableTarget.Process)

(note that this code can be different if you are developping a web application)

After that, everything is working like a charm

Bye, Yvan

[olivier leprêtre]

Hi Yvan,

That's very nice of you to answer. I did the same things you suggest, except copy in the project folder and modify path command (mine was D:\Spatialite). 

So I did that also unfortunately, no change :-( 

I remember I had same problems with VS 2010, put that away for a while and retried later when it finally worked without me being able to explain what changes. I'm afraid that this could happen without understanding.

I wonder if the "procedure not found" message is really mod_spatialite.dll or another procedure which could be find by the path, path being modified by other installation..

Could you post your loadextension command if it is different of mine ?

                .connSq = New SQLiteConnection(cnx)

                .connSq.Open()

                .connSq.EnableExtensions(True)

                .connSq.LoadExtension("mod_spatialite") <= error occurs there

Thanks,

Olivier

[Yvan B]

This is strange indeed (are you sure that you app is targeted for x86 and that you have the x86 version of spatialite as well?).  Here is the way I enable the extension (no real difference) :

            Dim cnn As New SQLiteConnection(dbConnection)

            cnn.Open()

            cnn.EnableExtensions(True)

            cnn.LoadExtension("mod_spatialite")

yvan

[mj10777]

Just to see what happens, try this with the full-path with extention

-  connSq.LoadExtension("D:\Spatialite\mod_spatialite.dll")

- on my machine I have the problem that a simple "mod_spatialite" does not work. but giving the full path does

Mark

Thanks,

Olivier

[olivier leprêtre]

Thanks Yvan, Yes, I downloaded this very same package that works on my other machine. I re-downloaded it without no improvment. At least your suggestion shows me that I didn't do wrong settings.

Thanks Mark. No change I tried also loadextension("D:\Spatialite\mod_spatialite.dll","mod_spatialite") as the sqlite message shows also (String fileName, String procName)

I checked also the sqlite version in the project reference, Sqlite, Ef6, Linq all are 1.0.103, installed by nuget, Client .net Profile is 4.5.1. Path includes D:\Spatialite

I don't see where to search...

[Yvan B]

Just an idea : do you have enough rights?  Maybe you can try as an admin?

yvan

[olivier leprêtre]

Yes, I work with an Admin account. I checked securities on this folder, I added whole control for everyone, no change.

[Brad Hards]

On some versions of Windows, perhaps check permissions (from Windows Explorer)
and make sure that the file is not showing as "blocked" at the bottom of that
properties dialog.

Brad

> -----Original Message-----
> From: spatiali...@googlegroups.com [mailto:spatialite-
> us...@googlegroups.com] On Behalf Of Yvan B
> Sent: Tuesday, 22 November 2016 21:12
> To: SpatiaLite Users <spatiali...@googlegroups.com>
> Subject: [SpatiaLite-Users] Re: load_extension does not find
> mod_spatialite.dll
> in x64 using .net
>
> Just an idea : do you have enough rights? Maybe you can try as an admin?
> yvan
>

> Le mardi 22 novembre 2016 11:00:03 UTC+1, olivier leprętre a écrit :
>
> Thanks Yvan, Yes, I downloaded this very same package that works on
> my other machine. I re-downloaded it without no improvment. At least your
> suggestion shows me that I didn't do wrong settings.
>
> Thanks Mark. No change I tried also
> loadextension("D:\Spatialite\mod_spatialite.dll","mod_spatialite") as the
> sqlite
> message shows also (String fileName, String procName)
>
> I checked also the sqlite version in the project reference, Sqlite, Ef6,
> Linq all are 1.0.103, installed by nuget, Client .net Profile is 4.5.1. Path
> includes
> D:\Spatialite
>
> I don't see where to search...
>
>
>
>

> --
> You received this message because you are subscribed to the Google Groups
> "SpatiaLite Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email
> to spatialite-use...@googlegroups.com <mailto:spatialite-
> users+un...@googlegroups.com> .
> To post to this group, send email to spatiali...@googlegroups.com
> <mailto:spatiali...@googlegroups.com> .
> Visit this group at https://groups.google.com/group/spatialite-users.
> For more options, visit https://groups.google.com/d/optout.

[olivier leprêtre]

Thanks Brad, File not blocked total access to everyone for all the files in this folder and the folder itself. 

Is it really "mod_spatialite" which can't be found, or could it be any included other dll or could any dll conflict occurs.. This kind of problem is mentioned several times on the web but I didn't find any definitive solution. 

[Yvan B]

A last precision about my own workflow:

in Visual Studio (2015 CE), I need to check properties of each file of the \spatialite subfolder and choose the following settings:

Build Action : Content

Copy to Output Directory : Copy if Newer

This allows to copy all the dll files of spatialite in the destination folder when you compile your project

yvan

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

On Tue, 22 Nov 2016 02:52:39 -0800 (PST), olivier leprêtre wrote:
> Is it really "mod_spatialite" which can't be found, or could it be
> any
> included other dll or could any dll conflict occurs..
>

certainly yes: any missing DLL required by mod_spatialite
will forbid to load mod_spatialite itself.
usually diagnostic messages returned by MS own tools are
very cryptic and useless.

hint: use the sqlite3.exe [1] front-end from the command
shell (cmd.exe), then try loading mod_spatialite as an
extension (obviously using the same PATH settings you
use on .net):

SELECT load_extension('mod_spatialite');

sqlite3 will emit a clear message for each DLL it's
unable to load, thus making simpler to understand
what's really happening.

second hint: use the nice DependencyWalker [2] (always
using the same PATH settings you use on .net) so to
check if there are broken DLL references.

[1] https://sqlite.org/2016/sqlite-tools-win32-x86-3150100.zip
[2] http://www.dependencywalker.com/

bye Sandro

[olivier leprêtre]

Thanks for all these suggestions; Unfortunately, nothing works. I tried also another pc with Windows 10/Visual Studio 2015 same problem

I tried to copy all dll in the publish folder, not working

I tried sqlite3 load extension as suggested by Sandro : Working.

About DependencyWalker, On the two Windows 10 machine, dozen of API-MS-WIN or EXT-MS-WIN are missing, when they aren't on the Windows 7 machine (which works).

Could it be those dll which are required ? Is someone using a Windows 10 machine to tell me it works ? 

Thanks

[Yvan B]

I've just tried a small console application on a Windows 10 machine and it works fine

Here is the VB.NET code (path to test database is hard coded and the geometry column of test table is called geom).

The project includes a "spatialite" folder containing all spatialite dlls.

yvan

Imports System.Data.SQLite

Module Module1

    Sub Main()

        Console.WriteLine("TEST Spatialite")

        Dim inputFile As String = "d:\test.sqlite"

        Dim mod_spatialite_folderPath As String = "\spatialite"

        Dim sl_path As String = Environment.GetEnvironmentVariable("Path", EnvironmentVariableTarget.Machine) & ";" & My.Application.Info.DirectoryPath & mod_spatialite_folderPath

        Dim cnString As String = [String].Format("Data Source={0};foreign keys=true;cache size=10000;Page Size = 4096;", inputFile)

        Dim sql As String = "SELECT AsWKT(geom) as g FROM myTable limit 10"

        Dim dt As DataTable

        Environment.SetEnvironmentVariable("Path", sl_path, EnvironmentVariableTarget.Process)

        Using cnn As New SQLiteConnection(cnString)

            cnn.Open()

            cnn.EnableExtensions(True)

            cnn.LoadExtension("mod_spatialite")

            Dim mycommand As New SQLiteCommand(sql, cnn)

            Dim ds As New DataSet

            Dim da As SQLiteDataAdapter = New SQLiteDataAdapter(sql, cnn)

            Try

                da.Fill(ds)

                da.Dispose()

                dt = ds.Tables(0)

                For Each r As DataRow In dt.Rows

                    Console.WriteLine(r("g"))

                Next

            Catch ex As Exception

                Console.WriteLine(ex.Message)

            End Try

        End Using

        Console.ReadKey()

    End Sub

End Module

[olivier leprêtre]

Thanks very much Yvan, that  is very nice of you to do that.

So the answer : It works !! It takes well mod_spatialite from D:\spatialite, I deleted it and get the same error message.

Environment settings aren't required.

So now, I have to understand but at least, it's not a machine problem, which is a good step. 

Thanks to you, will let you know the results...

Good day to all of you,

Olivier

[olivier leprêtre]

So here is the explanation : My application uses GIS component Mapwingis. It was working with Mapwingis 4.8, but 4.9 includes spatialite.  I just had to add the Gis component to a form with your working code to make get this problem again. As upgrading from VS 2010 required to change several references before being able to compile, it was not easy to detect which one was responsible. So now, the problem is how to deal with that, but this is another story.

Thanks again,

Olivier

[Joe Rose]

I found the same problem 4 years later.... Really?

Using Windows 10/64 bit

VS2013

Mysql Data client

Sqlite Data (with or without SpatialiteSharp)

Mapwingis

in a vb.net 4.5.2 application

Everything works for the Mysql & Sqlite3 (Spatiallite) until you add the axmap control to the application. 

I wished to use sqlite(3) as the spatial data source, for portability, but the adding the axmap control causes the spatial initialization on the sqlite data connection (SqliteConnection.LoadExtension(mod_spatialite.dll) to fail because of something axmap hijacked. 

This must be fixed if the mapwingis control is to be any good as a tool. The OGR "openfromquery" works regardless. I haven't checked "ogrdatasource". 

Also, the move shape option in the shape editor MUST be removed as it's a data destroyer! 

Joe

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

On Mon, 4 Feb 2019 06:29:53 -0800 (PST), Joe Rose wrote:
> I found the same problem 4 years later.... Really?
>
> Using Windows 10/64 bit
> VS2013
> Mysql Data client
> Sqlite Data (with or without SpatialiteSharp)
> Mapwingis
> in a vb.net 4.5.2 application
>
> Everything works for the Mysql & Sqlite3 (Spatiallite) until you add
> the axmap control to the application. 
>
> I wished to use sqlite(3) as the spatial data source, for
> portability,
> but the adding the axmap control causes the spatial initialization on
> the sqlite data connection
> (SqliteConnection.LoadExtension(mod_spatialite.dll) to fail because
> of something axmap hijacked. 
>

Hi Joe,

a very quick basic explanation:

1. load_extension(), as implemented by sqlite3, just searches the
module to be loaded within a list of directories specified by
the currently set system-wide searching rules for binaries.
if the required module cannot be found by using the current
settings an error will follow.

2. in the specific case of Windows, the directories usually searched
for binaries are (in this exact order):
2.a) the directory from which the main app was initially loaded.
2.b) the system directory (usually C:\Windows\System32)
2.c) the windows directory (usually C:\Windows)
2.d) the current working directory
2.e) any other directory defined by the system variable %PATH%

accordingly to your report it seems very alike that the "axmap"
control is silently resetting the current working directory (or
alternatively the %PATH% variable, or may be both) in such a way
that the directory where mod_spatialite is actually installed will
be removed from the searching path.

you say us absolutely nothing about the actual location of
mod_spatialite, but I strongly suspect that's it simply
installed on the current working directory.

possible hints and suggested remedies:
a) force your code to reset the expected working dir immediately
after loading "axmap" (and/or appropriately reset %PATH%)
b) alternatively you could install mod_spatialite in a fixed
immutable directory, as e.g. C:\Windows\System32
c) the last desperate resort; pass to load_extension() a fully
qualified path, as e.g. 'C:\myapp\spatialite\mod_spatialite.dll'

> This must be fixed if the mapwingis control is to be any good as a
> tool. The OGR "openfromquery" works regardless. I haven't checked
> "ogrdatasource".
>
> Also, the move shape option in the shape editor MUST be removed as
> it's a data destroyer!
>

sorry but there is nothing like "a shape option" in SpatiaLite;
I suppose that this latest claim should be reported to the
developers of some other package (probably Mapwingis).

bye Sandro

[Joe Rose]

Thanks Sandro;

I was aware of most of what you are telling me . There are a couple of "possible hints and suggested remedies:" that could still try although I still think it's a mapwingis issue. I will likely have to look into the source code as theirs is opensource.

Thanks again

Joe

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/ENJQXmzswdo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.