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");
fflush(stdout);
}

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");
exit(-2);
}

error = sqlite3_create_function(
db_handler,
"myFunc",
0,
SQLITE_UTF8,
NULL,
myFuncBody,
NULL,
NULL
);
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.

Regards,
mopyot

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