How can I create a user-defined function in SQLite?

Question

I am developing an application in android and I need to create an udf in sqlite. Is it possible to create it in sqlite? And if yes how to do this?

Aftmost

BTW - why not recompiling sqlite with additional function(s) ? – Telecast

There is an excellent description of how to accomplish this in Java, without need to compile SQLite C module with the NDK, at programering.com/a/MDO0ADMwATU.html (using SQLiteOpenHelper). – Phosphide

Answer 1

SQLite does not have support for user-defined functions in the way that Oracle or MS SQL Server does. For SQLite, you must create a callback function in C/C++ and hook the function up using the sqlite3_create_function call.

Unfortunately, the SQLite API for Android does not allow for the sqlite3_create_function call directly through Java. In order to get it to work you will need to compile the SQLite C library with the NDK.

And if you are still interested read 2.3 User-defined functions...

Here's how to create a function that finds the first byte of a string.

static void firstchar(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    if (argc == 1) {
        char *text = sqlite3_value_text(argv[0]);
        if (text && text[0]) {
          char result[2]; 
          result[0] = text[0]; result[1] = '\0';
          sqlite3_result_text(context, result, -1, SQLITE_TRANSIENT);
          return;
        }
    }
    sqlite3_result_null(context);
}

Then attach the function to the database.

sqlite3_create_function(db, "firstchar", 1, SQLITE_UTF8, NULL, &firstchar, NULL, NULL)

Finally, use the function in a sql statement.

SELECT firstchar(textfield) from table

Cultrate

A wonderfully succint example. However the example you've chosen to write would return the first byte, not the first character of a UTF-8 string which is not necessarily a valid Unicode value. In case anyone ever copies this verbatim. – Concourse

@Michael For one of my projects... I downloaded the NDK, enabled Native support for the project through Eclipse, added the sqlite.c. I added the custom functions to a separate file than the sqlite.c. Try to not modify the sqlite.c file. Keep in mind that the native sqlite library is not be the same as the Java sqlite library. So any sqlite functions defined in the native C/C++ code will not be available in Java. – Cultrate

@Michael, a jar is for Java, .so is for NDK. If all goes well you should see a .so file in the libs directory of your project. I recommend downloading some sample NDK projects from the Google Android site. You may want to learn a bit more about how Java interfaces with the native environment through JNI first. – Cultrate

Hi @jojaba i found your solution very helpfull to understand my problem to define a distance LatLng function. I tried to use NDK under Windows 8, but I failed. Can you suggest me some good tutorial that help me to add User function in SQLite source code? thank you for your time – Dominquedominquez

@jojaba Could you please let me know where did you put the sqlite3_create_function(db, "firstchar", 1, SQLITE_UTF8, NULL, &firstchar, NULL, NULL) statement (in the sqlite.c file; if not, how do you declare db in a separate file)? – Winzler

Created .so file successfully also loaded library using System.loadLibrary("sqliteExtensionName") still getting error no such function: firstchar any ideas what would be wrong ? – Cheapjack

@Yogesh It's hard to say without seeing your code. But I'm guessing that you are loading the .so but also using the Java sqlite methods. You cannot mingle to two. – Cultrate

@jojaba Could you please let me know where did you put the sqlite3_create_function(db, "firstchar", 1, SQLITE_UTF8, NULL, &firstchar, NULL, NULL) statement (in the sqlite.c file; if not, how do you declare db in a separate file)? – Qualitative

@Qualitative Add the sqlite3_create_function call immediately following a successful sqlite3_open call. – Cultrate

I am coding in java. I have loaded .so file of sqlite c library. Again where should I write sqlite_open – Qualitative

@Qualitative Add sqlite3_open in a separate C file from sqlite.c. How to add C files, https://mcmap.net/q/121714/-sqlite-with-android-ndk. The SQLite pipeline, sqlite.org/cintro.html. You will be using the NDK to export native methods to Java. – Cultrate

@jojaba i have created sqlite3.c file and a separate c file(sql_trig.c) in which i have define custom function and call sqlite3_create_function from sqlite3_extension_init function. I have generated ".so" but when i call function from sqlite query.it throw exception like "android.database.sqlite.SQLiteException: no such function". i can not identify where is problem. please describe it. – Kneepan

@Qualitative Keep in mind that the native sqlite library is not be the same as the Java sqlite library. So any sqlite functions defined in the native C/C++ code will not be available in Java. You must interface with C sqlite through JNI/NDK. – Cultrate

Can u please provide source code of c file and how to call it from java. I don't have much knowledge about c/c++ language. – Kneepan

@user1404930 You will need to work with the JNI/NDK. There's a couple links in this answer that may help you get started. There's many examples online of how to interact with C from Java and vise versa. – Cultrate

@Cultrate Hi. I'm having the same problem. Where should i write the sqlite3_create_function? – Debus

@Debus It's been a few years since I developed on Android, so my info may be out of date. The sqlite3_create_function should be added to a C file in your NDK project. This function must be called after you open the Sqlite database. Keep in mind that this function can only be used in NDK and that you must also include the sqlite.c file to your NDK project. Thus the Java Sqlite API cannot interoperate with the NDK. Basically, you must choose between NDK/Sqlite or JDK/Sqlite. – Cultrate

@Cultrate Sir, I've done it after a lot of struggle and searching. But now the problem is it's giving an error wrong number of arguments to function firstchar() (code 1). Kindly help me out. I'll be thankful if you also have a look at question I asked – Superhuman