47

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?

Mridang Agarwalla
  • 43,201
  • 71
  • 221
  • 382
Mustafa Güven
  • 15,526
  • 11
  • 63
  • 83
  • 1
    BTW - why not recompiling sqlite with additional function(s) ? – Berry Tsakala Jul 26 '12 at 11:09
  • There is an excellent description of how to accomplish this in Java, without need to compile SQLite C module with the NDK, at http://www.programering.com/a/MDO0ADMwATU.html (using SQLiteOpenHelper). – brodybits Jan 17 '18 at 00:20

1 Answers1

88

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
StilesCrisis
  • 15,972
  • 4
  • 39
  • 62
tidwall
  • 6,881
  • 2
  • 36
  • 47
  • 11
    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. – Samuel Harmer Jun 26 '12 at 10:19
  • 2
    @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. – tidwall Jan 24 '13 at 23:02
  • @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. – tidwall Jan 25 '13 at 00:51
  • 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 – antedesk Apr 26 '13 at 21:01
  • 4
    @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)? – syntagma Nov 12 '13 at 17:22
  • Created .so file successfully also loaded library using `System.loadLibrary("sqliteExtensionName")` still getting error `no such function: firstchar ` any ideas what would be wrong ? – Yogesh Malpani Apr 17 '14 at 16:07
  • @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. – tidwall Apr 30 '14 at 15:25
  • @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)? – shashwat Aug 11 '14 at 11:27
  • @shashwat Add the `sqlite3_create_function` call immediately following a successful `sqlite3_open` call. – tidwall Aug 11 '14 at 16:53
  • I am coding in java. I have loaded .so file of sqlite c library. Again where should I write sqlite_open – shashwat Aug 11 '14 at 17:05
  • 1
    @shashwat Add sqlite3_open in a separate C file from sqlite.c. How to add C files, http://stackoverflow.com/a/6672422/424124. The SQLite pipeline, http://www.sqlite.org/cintro.html. You will be using the NDK to export native methods to Java. – tidwall Aug 12 '14 at 12:54
  • @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. – Rajan1404930 Aug 18 '14 at 10:45
  • @shashwat 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. – tidwall Aug 18 '14 at 12:34
  • 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. – Rajan1404930 Aug 18 '14 at 18:53
  • 1
    @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. – tidwall Aug 18 '14 at 19:15
  • @tidwall Hi. I'm having the same problem. Where should i write the sqlite3_create_function? – Jas Nov 05 '15 at 10:25
  • @Jas 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. – tidwall Nov 05 '15 at 12:28
  • @tidwall 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](http://stackoverflow.com/questions/42062760/exclude-html-tags-and-some-unicode-characters-while-searching-in-sqlite?) – Waqas Ahmed Ansari Feb 09 '17 at 10:22