How to create custom functions in SQLite
SQLite does not have a stored function/stored procedure language. So CREATE FUNCTION
does not work. What you can do though is map functions from a c library to SQL functions (user-defined functions). To do that, use SQLite's C API (see: http://www.sqlite.org/c3ref/create_function.html)
If you're not using the C API, your wrapper API may define something that allows you access to this feature, see for example:
- PHP
sqlite_create_function()
(http://www.php.net/manual/en/function.sqlite-create-function.php) - Python
sqlite3.create_function()
(http://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function) - Perl
$dbh->sqlite_create_function($name,$argc,$code_ref,$flags)
(https://metacpan.org/pod/DBD::SQLite#$dbh-%3Esqlite_create_function(-$name,-$argc,-$code_ref,-$flags-))
How can I create a user-defined function in SQLite?
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
Is it possible to create a javascript User-defined function in sqlite
Yes. It is possible to call javascript functions
//(thanks to Mirnal Kant, SQLManager)
//Version 2 -- Prevent Firefox crashing
// -- Suspect a problem with continual creation of Regex objects
var g_RegExpString = null;
var g_RegExp = null;
//functions to be created for the db
var smDbFunctions = {
// (0) = Regex Expression
// (1) = Column value to test
regexp: {
onFunctionCall: function(val) {
if (g_RegExp == null || val.getString(0) != g_RegExpString)
{
g_RegExpString = val.getString(0);
g_RegExp = new RegExp(g_RegExpString);
}
if (val.getString(1).match(g_RegExp)) return 1;
else return 0;
}
}
};
after instantiating a SQLite instance:
Database.createFunction("REGEXP", 2, smDbFunctions.regexp);
How do I call user-defined function in SQLite only if it exists?
SQLite is designed as an embedded database, so it is assumed that your application controls what is done with the database.
SQLite has no SQL function to check for user-defined functions.
If you want to detect changes made only from your own program, use sqlite3_update_hook.
Related Topics
How to Set Versionname in APK Filename Using Gradle
Android Webview - Webpage Should Fit the Device Screen
"Failed to Install the Following Android Sdk Packages as Some Licences Have Not Been Accepted" Error
Difference Between /Res and /Assets Directories
Android: How to Periodically Send Location to a Server
Android Command Line Tools Sdkmanager Always Shows: Warning: Could Not Create Settings
Design Android Edittext to Show Error Message as Described by Google
Do I Need All Three Constructors for an Android Custom View
Uploading Images to Server Android
Multi Flavor App Based on Multi Flavor Library in Android Gradle
Detect from Browser If a Specific Application Is Installed in Android
How to Reference Style Attributes from a Drawable
Customize Android Facebook Login Button
Detect a New Android Notification