[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    [sqlite] Load extension in a trigger?
From:       Brent Wood <pcreso () yahoo ! com>
Date:       2019-08-20 21:42:19
Message-ID: 1933541230.620135.1566337339288 () mail ! yahoo ! com
[Download RAW message or body]

 Hi,

I'm using spatialite via the command line in a script/batch file:

sqlite3 -cmd ".load mod_spatialite.com" %DB%

everything works perfectly...

However, I need to use it via JDBC for a java application and loading the spatialite \
extension in the jdbc connection is extremely buggy... unusable.

My current hack/workaround is a batch file (or bash script depending on platform) to \
do a spatial update after the non-spatial insert via JDBC. The only positive thing \
about this approach is that it works reliably...

Given it seems I'm not going to be able to get a robust JDBC/spatialite working I was \
trying to use a db trigger to do the update automatically.

I can create a trigger with a spatialite sql function to modify point and line \
geometry columns quite easily, but the SQL that invokes the trigger also needs the \
spatial library to be loaded in the connection... so this doesn't work with JDBC - \
the trigger gives an unknown function error for any spatial function in the trigger \
sql.

I have tried another approach which would work nicely for this use case, but cannot \
get it to work - that is, loading the spatialite library inside the trigger, so even \
a non-spatial connection could load the extension and run the query:

create trigger mk_geom after insert on station
begin
  --.load mod_spatialite  or --
  select load_extension(mod_spatialite);
  <spatial sqls....>;
end;

This fails. Reading the docs suggests this may because in this situation I can't load \
extensions (but I'm not sure).

My initial question is:
Can anyone tell me how to load the extension inside the trigger (perhaps how to \
enable extensions in a cli connection)?

Can anyone suggest an alternative way to avoid the use of system calls invoking batch \
files (from a java application) to run spatial queries?


Thanks

Brent Wood

  
 
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[prev in list] [next in list] [prev in thread] [next in thread]