Loading Spatialite extension via SQLite3 ODBC Driver?

Question

I am having trouble getting the Spatialite extension to load as part of the SQLite3 ODBC driver.

What works [loading extension into SQLite Driver]:

I have software that is connected using the SQLite3 ODBC driver (http://www.ch-werner.de/sqliteodbc/). I have previously loaded extensions into this driver without any problems. A previous working example was a trigger that updated columns [using triggers] using some trig functions that are not part of base SQLite functionality - I built the *.so extension and pointed SQLite to it as part of the ODBC connection and everything worked fine - when I connect/export data from the software I got no issues.

What works [loading Spatialite extension into SQLite]:

If I use the SQLite shell I can load the Spatialite (or any other extension) using: select load_extension('libspatialite-2.dll');. That works without fail.

What doesn't work:

However I cannot seem to get the Spatialite extension to load properly as part of the ODBC connection - and I am using a trigger that builds the geometry column of a Spatialite database based on the software providing only x and y values. I get an error that says "Extension C:\windows\system32\libspatialite-2.dll did not load: the specified module could not be found". I also tried the spatialite-4.dll and it doesn't work either. The module and its requirements are located there and the environmental path has (always) been there. If I do not load the extension then I will get an error when the trigger calls a function (makegeometry, makepoint or whatever you want) that is not part of the SQLite driver - it doesn't understand the function.

My hack workaround at this point are batch files that run what the trigger should do after the data has transferred from the software to the Spatialite database, but I'd prefer it to be a one-stop shop.

I haven't seen this much on the web, other than a Google Groups question (https://groups.google.com/forum/#!topic/spatialite-users/3ImfXm7cdpU) that didn't resolve the problem [I haven't tried to build the CppDB yet - I want to see if there is a more lazy straightforward solution].

Answer 1

I did the following:

1. Put the DLLs in a different spot (NOT system32) - in my case c:\spatialite\bin. Note that you need more than just libspatialite - there are supporting DLLs that need to also be there.
2. Put the entire path in the extension box, not just the name.
3. Commence with great joy

Oddly enough I had already done the first step - so just adding c:\spatialite\bin to my odbc dll extension is all I needed to do. It's the little things sometimes. I also had already added that location to my PATH environmental variable - but I did a quick test and it doesn't appear to be a requirement.

I'm GUESSING that perhaps it has something to do with it looking in system32 but being a 64bit driver. I'm just guessing on that - I'm still on step #3.