CODEWITHSUNDEEP
csqliteuser-defined-functionsraspberry-pi3udf
mopyot
mopyot

Reputation: 35

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

Upvotes: 2

Views: 657

Answers (1)

Ctx
Ctx

Reputation: 18420

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.

Upvotes: 2

Related Questions