mopyot mopyot - 4 years ago 180
C Question

sqlite3 C user defined function not being called

first post here! This site has been of paramount help on a huge number of previous tech matters.
Thanks for your hard work, collectively spoken.

And now coming to my problem.

I'd like to write a C sqlite3 UDF (User Defined Function).
Assume the function name is "myFunc".

I work on a Raspberry PI 3B - Raspbian default (distro up-to-date).

test_table has the following schema:

create table test_table (
a integer,
b integer

The mini-program (to test the approach) opens the DB and installs the function.
Then waits indefinitely for trigger events.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <signal.h>
#include <stdbool.h>
#include <unistd.h>
#include <sys/stat.h>

static void myFuncBody(sqlite3_context *context, int argc, sqlite3_value **argv)
printf("myFunc fired!\n");

int main(int argc, char** argv)
sqlite3 *db_handler;
char *errMsg;
int error;

error = sqlite3_open("test.db", &db_handler);
if (error)
printf("Error opening the DB.\n");

error = sqlite3_create_function(
if (error != SQLITE_OK)
printf("Error creating the function.\n");

for (;;);

return 0;

I then open separately test.db on the sqlite3 console:

$ sqlite3 test.db

$ sqlite> select myFunc();
Error: no such function: myFunc

I think myFunc should be visible but this seems not to be the case.

I could also create a trigger but the result obviously does not change.

$ sqlite> create trigger trigger1 after insert on test_table begin select myFunc(); end;

$ sqlite> insert into test_table values(-100, -150);
Error: no such function: myFunc

My aim is simply to be notified about an insertion on test_table.

Is something basically wrong with my approach?

Any help would be much approciated.


Ctx Ctx
Answer Source

There is a misunderstanding here:

A newly defined C-function is only defined in the context of the handle (db_handler in your case) where it was added. It is not future part of the database file test.db.

So if you do a

sqlite3_exec(db_handler, "select myFunc()", ...);

after your call to sqlite3_create_function() it should work as you expect.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download