SQLite Forum

User Defined Functions
Login

User Defined Functions

(1) By anonymous on 2021-05-04 11:48:43 [link] [source]

Given that C/C++ is not in my skills set, I need some help/guidance on two issues:

A. When should I use sqlite3_create_function as opposed to building an SQLite extension and loading it?

As far as I can see, both options are connection bound and evaporate on the connection being closed and I realise that building an extension is a neater way when creating many functions

B. Anyone has a worked example (of something simple, like adding a number to an existing column in a table in the current connection) of using sqlite3_create_function? I can adapt the template for my purposes.

(2) By Keith Medcalf (kmedcalf) on 2021-05-04 17:39:40 in reply to 1 [link] [source]

When should I use sqlite3_create_function as opposed to building an SQLite extension and loading it?

The sqlite3_create_function is how you "hook up" a function so it can be executed by the SQLite3 engine. An "SQLite3 extension" is merely a wrapping paper packaging for functions which are added to SQLite3 via sqlite3_create_function.

That is, if you want to make toast, you need to make a toaster (write the function). Then you need to hook it up (plug it into the electrical outlet -- as in call sqlite3_create_function to "hook up" the toaster").

The "packaging" the toaster arrives in makes no difference. It does not matter whether you build it from scratch yourself, or order it in an Amazon box from Amazon (a loadable extension). In both cases you have to take it out of the box (if any) and plug it in (call sqlite3_create_function) before you can make toast.

(3) By Keith Medcalf (kmedcalf) on 2021-05-04 17:47:01 in reply to 1 [link] [source]

Anyone has a worked example (of something simple, like adding a number to an existing column in a table in the current connection) of using sqlite3_create_function? I can adapt the template for my purposes.

This is not normally what a function does. A function is like a toaster. You put something in (raw bread) and something else comes out (toasted bread). While a toaster may have "side-effects" (such as burning down your house) these are generally not the primary function of the toaster.

In an SQL database the way to update a value in a table is with the UPDATE statement. While you may write a function that takes raw bread and make toast and at the same time tallies up the number of slices of bread toasted in some table as a side-effect, but that would greatly limit the utility of the function.

(4) By anonymous on 2021-05-04 20:21:41 in reply to 3 [link] [source]

Thanks Keith; as I've mentioned, lack of C/C++ experience is holding me back and I'd appreciate some help.

I am looking for a way to achieve what follows below using the API sqlite3_create_function: is that possible?

connection.CreateFunction(
    "volume",
    (double radius, double height)
        => Math.PI * Math.Pow(radius, 2) * height);

var command = connection.CreateCommand();
command.CommandText =
@"
    SELECT name,
           volume(radius, height) AS volume
    FROM cylinder
    ORDER BY volume DESC
";

(5) By Keith Medcalf (kmedcalf) on 2021-05-05 03:24:18 in reply to 4 [source]

Why not just do it directly?

  select name,
         pi() * pow(radius, 2) * height as volume
    from cylinder
order by 2 desc
;

(6) By Keith Medcalf (kmedcalf) on 2021-05-05 03:26:02 in reply to 5 [link] [source]

You will, of course, need to be using a version of SQLite3 that has the math functions available and has been compiled with those functions turned on.

(7) By anonymous on 2021-05-06 12:50:26 in reply to 5 [link] [source]

The function example was simply to provide an illustration: my objective is to be able to find what it takes to have sqlite3_create_function working.

(8) By David Jones (vman59) on 2021-05-06 14:40:28 in reply to 4 [link] [source]

Your example is using a .net interface presumably provided by Microsoft, the underlying sqlite3_create_function() is a C interface and requires the user to process the argument list built by the SQLite VDBE at a somewhat low level.

(10) By anonymous on 2021-05-06 18:41:11 in reply to 8 [link] [source]

Not sure where the comedy lies but I found a worked example here which I've successfully adapted to work with C#.

(11) By skywalk on 2021-05-07 12:57:01 in reply to 10 [link] [source]

The tragedy is the lengths people go to avoid answering a question.

(9) By jose isaias cabrera (jicman) on 2021-05-06 15:55:37 in reply to 3 [link] [source]

Thanks, Keith. I am laughing so hard here. This is great! Keep it going. :-)