mod_spatialite under Lazarus/FreePascal ZeosDB - "not authorized" sql error

[Immo Blecher]

I have tried probably almost all spatialite and mod_spatialite dlls, but can't get my ZConnection.ExecuteDirect command to successfully load the mod_spatialite.dll. My code basically is:

if ZConnectionDB.Protocol = 'sqlite-3' then

      begin

        ZConnectionDB.ExecuteDirect('PRAGMA foreign_keys = ON;'); //Enable foreign keys on sqlite

        ZConnectionDB.ExecuteDirect('SELECT load_extension(''mod_spatialite'');');

      end;

If I use sqlite3 command line and connect to the same database as the ZConnectionDB then it works 100%...it connects, no error. What is going wrong? I even added the application path to the Windows PATH. All mod_spatialite dlls are in the application directory. I have tried with sqlite3.dll as LibraryLocation and libsqlite3-0.dll (with path to app directory). There is no reason whatsoever that it should not work.

Essentially I am trying to update a GEOMETRY column in one of the tables in the database with the POINT of the Longitude and Latitude fields with an After Update trigger (MakePoint(Longitude, Latitude, 4326);).

Any experience under these conditions? Any help would be appreciated.

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

On Sun, 13 Dec 2015 09:09:13 -0800 (PST), Immo Blecher wrote:
> I have tried probably almost all spatialite and mod_spatialite dlls,
> but can't get my ZConnection.ExecuteDirect command to successfully
> load the mod_spatialite.dll.
>

> If I use sqlite3 command line and connect to the same database as the
> ZConnectionDB then it works 100%...it connects, no error. What is
> going wrong? I even added the application path to the Windows PATH.
> All mod_spatialite dlls are in the application directory. I have
> tried
> with sqlite3.dll as LibraryLocation and libsqlite3-0.dll (with path
> to
> app directory). There is no reason whatsoever that it should not
> work.
>

> Any experience under these conditions? Any help would be appreciated.
>

Hi Immo,

I have no familiarity at all with Lazarus/FreePascal, anyway
I can easily image a valid explanation.
libsqlite3 supports this API [1]:

int sqlite3_enable_load_extension(sqlite3 *db, int onoff);

[1] https://www.sqlite.org/c3ref/enable_load_extension.html

it's exactly intended to switch on/off the authorization
to load external modules; when set to OFF any attempt
to load an external module will simply raise a
"NOT AUTHORIZED" error.

Check better your documentation; presumably Lazarus is
keeping this option disabled by default, and is just
expecting some appropriate call intended to switch the
authorization flag to the ON status before any actual
attempt to load an external module.

bye Sandro

[Immo Blecher]

Thanks for your quick reply Sandro,

I have used the API calls before and could successfully load the mod_spatialite, but the problem is that you basically create a connection to the database with a pointer to sqlite3 and connect to it and enable extensions and spatialite, but my ZConnection is a different connection and therefore does not know about my previous DB connection through the API calls. Basically the two lie in different memory spaces.

Any further ideas?

--
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/LxllPytnoJk/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.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

[Brad Hards]

On Sun, 13 Dec 2015 08:25:55 PM Immo Blecher wrote:
> Thanks for your quick reply Sandro,
>
> I have used the API calls before and could successfully load the
> mod_spatialite, but the problem is that you basically create a connection
> to the database with a pointer to sqlite3 and connect to it and enable
> extensions and spatialite, but my ZConnection is a different connection and
> therefore does not know about my previous DB connection through the API
> calls. Basically the two lie in different memory spaces.

Can you show a minimal, compilable example that demonstrates the problem?

Brad

[Immo Blecher]

Hello Brad,

The following code runs with the SQLiteWrap, which uses the SQLite3 API from

{
  Simplified interface for SQLite.

  This version :Ported to D2009 Unicode by Roger Lascelles (sup...@veecad.com)
  V2.0.0  29 June 2010

  History
  Reworked by Lukáš Gebauer at http://www.ararat.cz/doku.php/en:sqlitewrap.
  Updated for Sqlite 3 by Tim Anderson (t...@itwriting.com)
  Note: NOT COMPLETE for version 3, just minimal functionality
  Adapted from file created by Pablo Pissanetzky (pa...@myhtpc.net)
  which was based on SQLite.pas by Ben Hochstrasser (bh...@surfeu.ch)

  Require: Delphi 6+, FreePascal
  Sqlite 3.7.1+
}

Using these two I can do:

      if ZConnectionDB.Protocol = 'sqlite-3' then
      begin
        ZConnectionDB.ExecuteDirect('PRAGMA foreign_keys = ON;'); //Enable foreign keys on sqlite

        //ZConnectionDB.ExecuteDirect('SELECT load_extension(''mod_spatialite'');');
        MySQLiteDB := TSQLiteDatabase.Create(ZConnectionDB.Database);
        MySQLiteDB.EnableLoadExtension(True);
        MySQLiteDB.ExecSQL('SELECT load_extension(''mod_spatialite'');');
      end;

All goes fine, it loads the extension, no error messages. But obviously all my tables (something like 80) use the ZConnectionDB as Connection, which can connect to a variety of databases. My users can also use MySQL and PostgreSQL, and will later also be able to use MSSQL.

So the connection that loads the extension is not the same as the Zeos connection (ZConnectionDB) and therefore that connection is not spatially enabled, which I can see when posting a record that triggers the AFTER_UPDATE SQLite trigger:

UPDATE basicinf SET DATE_UPDTD = strftime ( '%Y%m%d' , 'now' ) , GEOMETRY = MakePoint ( LONGITUDE , LATITUDE , 4326 ) WHERE SITE_ID_NR = OLD . SITE_ID_NR

It does not find the "MakePoint" function.

Or is my thinking not right? It almost seems this is not really spatialite related but more a ZEOS problem that cannot load/enable extensions. The unfortunate thing is that my whole app is based on ZEOS components.

Immo

--

Immo F. Blecher
Street: 3 Briony Close, Noordhoek, Cape Town, South Africa
Postal: P.O. Box 662, Noordhoek, 7979, South Africa
Tel.: +27 21 789 2933 | Fax: +27 86 7363302 | Cell.: +27 83 2726791
Email: im...@blecher.co.za | imm...@gmail.com | im...@mymtnmail.co.za
Internet: http://www.blecher.co.za/

Contact me: imm...@gmail.com immoblecher

Please consider your environmental responsibility. Before printing this e-mail message, ask yourself whether you really need a hard copy.

IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.

This email has been sent from a virus-free computer protected by Avast. www.avast.com

[Immo Blecher]

I have solved this quite a while ago already....thanks to OpenSource!!!! If anybody is interested in the solution please contact me under this post again.

Immo