SQLite/Spatialite ODBC driver questions/1 tip

[Koos]

Hi Everybody!

I'm new to Spatialite but so far it is great, it will end up being a great tool for the work I'm doing... this thread maybe piggy-backs off of this one:

SpatiaLite + QGIS, data portability but I decided to start a new one since this is more about ODBC.

I'm on Windows 7 (64 bit, but using the 32 bit drivers), and have the need to link to a spatialite table in MS Access- doable with ODBC.

I'm using  Christian Werners' SQLite ODBC driver which seems to work fine for SQLite databases, but gets finicky when using Spatialite databases. 

When I make an ODBC connection and try to load libspatialite-4.dll, the driver complains that it can't find the libspatialite-4.dll and can't load it...I scoured the web for a while looking for an answer to this, and found a few tips on copying dll's to the Windows/system32 folder. For me that did not do the trick. I've copied dll's everywhere but no go. If anyone has insight into this, I would love it. I can load libspatialite-4.dll if I am running SQLite in a command promt and type >.load libspatialte-4.dll.  So it's there and it works but the ODBC can't figure that out...

However- I found this helpful trick regarding Access this morning:

for an ODBC connected Spatialite database which has the extension .sqlite, Access was complaining that it was an invalid data source and not loading the tables...I solved this by changing the 'opens with' for .sqlite to 'opens with - spatialite.exe' in the properties dialog of my Spatialite database. (right click database file>properties>opens with...)

This allows me now to link to spatialite tables in Access, although I think I am still missing the geospatial functionality. That's ok for now though because I can handle that in QGIS or Spatialite-gui. At least I can make non geo-changes and reference my tables as needed in Access.

I hope this helps someone :) If anyone has any clues as to how to get the ODBC loading libspatialite nicely, please let me know.

Koos

ps...Spatialite is a long word... what's the abbreviation? :)

[a.furieri]

Your question excited my technical curiosity.
I'm not exactly a "windows fan" (I strongly feel Linux is
much more a better o.s.), anyway curiosity killed the cat,
you know ;-)

I still hold in my own trashware collection an archaic Pentium4
with WinXP pro (32 bit) and MS Office 2002, so I duly started an
ODBC test session.

Surprise: exploring a DB-file created by SpatiaLite is surely
possible using MS Access 2002 (and I suppose, using any other
subsequent version).

I simply had to perform the following steps:
1) install the SQLite-ODBC diver downloaded from
http://www.ch-werner.de/sqliteodbc/
2) download the latest SpatiaLite'own DLL's from
http://www.gaia-gis.it/gaia-sins/windows-bin-x86/spatialite-3.0.1-DLL-win-x86.zip
3) copy all the DLLs on C:\Windows\system32
- libspatialite-2.dll
- libfreexl-1.dll
- libgeos-3.3.1.dll
- libgeos_c-1.dll
- libproj-0.dll
- libiconv-2.dll
- libstdc++-6.dll
- libcc_s_dw2-1.dll
4) configure a system-wide OBDC datasource based on the SQLite3-ODBC
driver and pointed at my SpatiaLite's sample DB
Please note: in the "configure" panel there is a text-box labelled
"Load extension". I simply inserted "libspatialite-2" here, and
after this the SpatiaLite extension was immediately enabled.
Note well: specifying "libspatialite-2.dll" doesn't works, it looks
like if no extension at all is expected to be found in the library
name.

Good news: after all this MS Access is immediately able to perform
"simple"
queries on behalf of the SpatiaLite DB-file.

Bad news: no SQL Spatial function is enabled. When I tried to execute

SELECT ST_AsText(geometry) FROM my_table;

I immediately got an error: "undefined function ST_AsText"
It looks like ODBC/MS Access simply support basic SQL expressions, and
not the extended ones implemented by the target DBMS.
So standing things, I suppose that ODBC/MS Access is absolutely
useless
as a front end to SpatiaLite, because there is no way to perform any
spatial operation.

bye Sandro

[a.furieri]

> ps...Spatialite is a long word... what's the abbreviation? :)
>

"splite" is often used ... sometimes I personally use "SL" and "RL"
to identify respectively SpatiaLite and RasterLite

bye Sandro

[Noli Sicad]

Re: Front-end for Spatialite

I trying to use Kexi in this matter.

http://www.calligra.org/kexi/

Kexi is currently running in Linux, next Windows and then Mac OS X.

Status
http://community.kde.org/Kexi/TODOs

There are problems in direct connecting to databases, now working at
this moment. Predicate is not mature yet. It needs support for
SQLite3.

Sandro, probably we can make spatialite works in Kexi especially in
accessing spatial aspects (e.g. spatial queries) mentioned in the
earlier post in MS Access.

Thanks.

Noli

> --
> You received this message because you are subscribed to the Google Groups
> "SpatiaLite Users" group.
> To post to this group, send email to spatiali...@googlegroups.com.
> To unsubscribe from this group, send email to
> spatialite-use...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/spatialite-users?hl=en.
>
>

[markb]

In pass-through query mode MS Access can "run" native SQLite/SpatiaLite SQL via the SQLite ODBC driver and its loadable extension functionality (as per Sandro's instructions).  It has been a while (with SpatiaLite 2.3.1 or so, MS Access 2002/3 and some much earlier version of the SQLite ODBC driver), but it was quite easy for me to return query result sets via Sandro's method or record sets via code (not necessary, in code at least, to configure a system-wide ODBC datasource, just optional; the dll part is the same though).  I sort of remember some flakiness with "directly" editing linked tables, but never looked into it, since I was doing things via VBA.  Good Luck w/ SL Koos.   -mark

[David Stoup]

I know this isn't exactly what you asked but perhaps it can help

I use CPPDB as a connection wrapper for C++ database access, http://cppcms.com/sql/cppdb/.  It has support for sqlite and OBJBC ( as well as PostgreSQL and MySQL ).  It is fully functional.  If you load your spatialite library in your sqlite connection you will be able to use all supported spatial functions.

CPPDB will allow you to use the native sqlite/spatialite driver to create that connection and the native ODBC driver for the access connection and give you a common interface for speaking to both.  I've found it pretty clean and well constructed.  The API is similar in feel to JDBC.

Hope this helps,
Dave
 

[Reinier Olislagers]

If you didn't, when defining your query, you might want to try
specifying it's a "pass through" query. (E.g. via View/SQL Specific/pass
through query -note: translated names).
This instructs Access to pass the SQL straight to the driver; otherwise
it tries to interpret it as Access SQL which obviously won't work.

Hope that solves it...