Hi all,
I am wondering if anyone has successfully used MBS SQL Plugin, on OSX, to load an extension via sqlite ?
I am working on a spatial database which uses the spatialite extension to sqlite. I can compile and run spatialite using brew quite successfully.
Using load_extension simply doesnt work in OSX currently, and I need to find out if this is possible with the plugin before committing my £££.
It is mentioned on one of the videos on the website too but it is unclear if that is for OSX or windows / linux.
Im assuming at this point that MBS SQL Plugin contains its own instance of sqlite, which is configured to allow the loading of extensions.
Thanks all.
I believe you can try out the MBS suite before buying a license. I think the only restriction is that without a license you can’t build the app for distribution. Essentially the plugin version of #if DebugBuild MsgBox("Buy MBS") #endif
Did you file a bug report about SQLiteDatabase.LoadExtensions not working?
[quote=303027:@Tim Parnell]I believe you can try out the MBS suite before buying a license. I think the only restriction is that without a license you can’t build the app for distribution. Essentially the plugin version of #if DebugBuild MsgBox("Buy MBS") #endif
Did you file a bug report about SQLiteDatabase.LoadExtensions not working?[/quote]
I’ll take another look then, I didnt spot that.
Sqlite on OSX is built without the option to load extensions so that would never work anyway. The sqlite in Xojo i assume is the same since it comes up with a ‘not authorised’ message when I try to load an extension.
Ive not filed any report so far as Im still looking into it to see what my options are.
Did you set the SQLiteDatabase.LoadExtensions property to true before trying to load an extension?
(sorry if these seem like dumb questions)
I actually haven’t, no.
I went straight to the ‘SELECT load_extension’ part as soon as I had opened the database.
Is this just a case of using db.LoadExtensions = True prior to attempting to load the extension ?
From the way the documentation reads that’s what it sounds like, give it a try.
I got a little further but not by much. The db error trap reports this :
Error :dlopen(/usr/local/lib/mod_spatialite.dylib, 10): no suitable image found. Did find:
/usr/local/lib/mod_spatialite.dylib: mach-o, but wrong architecture
The location is correct and so is the file. If I fire up sqlite3 from the command line using the instance at /usr/local/Cellar/sqlite/foo then it works by using the .load command i.e
.load /usr/local/lib/mod_spatialite
Of course I have no way of telling Xojo to use that instance of sqlite.
Ive done quick try of the MBS SQL Plugin and see the same results as above i.e wrong architecture.
If I switch to libspatialite.dylib instead of mod_spatialite.dylib, then I get the following :
dlsym(0x672cf0, sqlite3_spatialite_init): symbol not found
can you run file command in terminal on those dylibs?
I would expect an 32-bit vs. 64-bit issue.
So maybe app is 32-bit and libs are 64-bit.
Oh i maybe see where you are thinking with this.
‘mod_spatialite.dylib’ shows : Mach-O 64-bit bundle x86_64
‘libspatialite.dylib’ shows : Mach-O 64-bit dynamically linked shared library x86_64
Testing runs in 32 bit, so I maybe need to build the app as 64 bit and see if that works…
If I build the apps in 64 bit I get :
Using Xojo load_extension : Error :dlsym(0x7fe4d874d020, sqlite_spatialite_init): symbol not found
Using MBS SQL Plugin : The only message I see is about registering and not finding function VersionMBS. There is no message relating to mod_spatialite. (I used the load_extension example from the plugin for this).
A little more info regarding the setup I am using :
Xojo 2016r1.1
Mac OSX 10.11.6
Spatialite is installed using ‘brew’. As part of the dependencies it also builds its own instance of SQLite3, Proj4 and Geos. They are the absolute minimum for building it. There are other optional packages such as FreeXL; these are not essential though.
I suppose my next option may be to build the packages myself directly from the source code, but the result is unlikely to be any different from using brew anyway.
Spatialite can be built to include sqlite3 and extension in one lib. So you would use that lib as the sqlite library.
e.g.
[code] dim con as SQLConnectionMBS // your connection
dim f as FolderItem = GetFolderItem("sqlite.dylib")
con.SetFileOption con.kOptionLibrarySQLite, f[/code]
[quote=303076:@Christian Schmitz]Spatialite can be built to include sqlite3 and extension in one lib. So you would use that lib as the sqlite library.
e.g.
[code] dim con as SQLConnectionMBS // your connection
dim f as FolderItem = GetFolderItem("sqlite.dylib")
con.SetFileOption con.kOptionLibrarySQLite, f[/code][/quote]
It still gives me the message : Error :dlsym(0x7fe4d874d020, sqlite_spatialite_init): symbol not found