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?

BTW - why not recompiling sqlite with additional function(s) ? — Berry Tsakala
– Berry Tsakala, Commented Jul 26, 2012 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 programering.com/a/MDO0ADMwATU.html (using SQLiteOpenHelper). — brodybits
– brodybits, Commented Jan 17, 2018 at 0:20

StilesCrisis · Accepted Answer · 2017-11-08 00:08:13Z

89

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

edited Nov 8, 2017 at 0:08

StilesCrisis

16.4k4 gold badges43 silver badges64 bronze badges

answered Nov 27, 2011 at 2:44

tidwall

6,9592 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

Samuel Harmer Over a year ago

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.

tidwall Over a year ago

@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.

syntagma Over a year ago

@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)?

tidwall Over a year ago

@shashwat Add sqlite3_open in a separate C file from sqlite.c. How to add C files, stackoverflow.com/a/6672422/424124. The SQLite pipeline, sqlite.org/cintro.html. You will be using the NDK to export native methods to Java.

tidwall Over a year ago

@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.

|

Collectives™ on Stack Overflow

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

1 Answer 1

18 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

18 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related