* Cross-platform - Code once then deploy on macOS, Windows, Linux, the web, iOS and Raspberry Pi. Use it as you like.Rapid application development for Desktop, Web, Mobile & Raspberry Pi. If you succeed in this and like to share your results, I'm happy to include them in my downloadable file or link to yours.Īs usual, my demo code is free of restrictions. I believe the extensions can also be built for Windows and Linux without too much effort if you follow the sqlite instructions under the topic "Compiling A Loadable Extension".
If you replace it with the newer SQLiteDatabase class, you need to also add this line each time before you try to install an exension, or you'll get an "Not authorized" error: The proejct uses the older REALSQLDatabase class. I've tested it with both Real Studio 2012r2.1 and Xojo 2015r2.2. Note, however, that it only works on OS X out of the box.
The project shows how to load both extensions and then tests the results, proving that both the unicode extension and the added custom functions work. To sum this all up, I'm now presenting to you a ready-made Xojo project along with compiled libs for both extensions and their source code with Xcode project files. It should also make it clear how to add more API functions if needed. I have then located some of the other functions to access passed parameters and to set returned results and make proxy functions for them to make calling them easier. Turns out that sqlite3_result_int is at index 82, for instance. And that can only be found out by looking at the header file ( sqlite3ext.h) and counting through the positions. sqlite3_result_int, we must first find out which place its function pointer occupies in said array. In C, macros take care of making them convenient to call, but in Xojo it means a bit more work for us.īasically, to call a function, e.g. For this, sqlite provides a large set of functions through its API. The problem is: While it's fairly easy to invoke them from C code, it's rather tedious to do in Xojo, because they're accessed through an array of function pointers (note the pApi parameter the sqlite3_extension_init gets passed - that's where all those pointers are located). Our custom function certainly wants to be able to receive parameters, return values and maybe even perform database access operations on its own. How that's done is documented in the SQLite docs.
Once the extension gets loaded and our callback function gets called, we perform the registration of the custom functions.
(This code should be portable for Windows and Linux as well - the actual source code contains some extra instructions for Windows DLLs).
Its code then invokes our previously set Xojo function through the stored callback function pointer. Then, once we load this extension using the "SELECT load_extension" command, sqlite3_extension_init will get called. The SetCallback function stores the address of our Xojo function for later use. We pass it the address of a Xojo function in which we'll handle the installation of our custom functions. This is a function we'll call from Xojo before loading this extension into the sqlite engine. The first relevant part here is the SetCallback function. so files of the ICU-sqlite lib on the web and use them with these instructions. I believe, however, that you can more easily find. Mind you, only for OS X, though, as I do not have the time to make and test versions for Windows and Linux as well. Therefore, I'm making my own built version available, along with the source code. And unfortunately, there is none publically available as far as I could see, at least not for OS X. Still, you need to have a readily built ICU lib for sqlite before you can load it. Furtunately, though, Xojo's plugin allows dynamic loading of extensions (like plugins) into the sqlite engine.
This is not a viable option with Xojo because you'd also have to write your own Database plugin to make the sqlite lib usable in Xojo (while the sqlite lib is open source, Xojo's plugin is not, or this would be less of an issue). The usual solution to this is to compile the sqlite3 library with ICU support and then include this lib in your program. This missing unicode support also affects sorting and related comparison functions. only the plain ASCII chars (a-z) will get uppercased but not the special french chars. For instance, if you try to use the SQL function upper on a french phrase such as Tête-à-Tête, you will get TêTE-à-TêTE, i.e. Xojo's default Database class REALSQLDatabase (or its replacement SQLiteDatabase), which is based on sqlite, does not support international (non-english) characters.