Info: How to use create_function

[git2013vb]

I was trying to use create_function in this way:

    <..snip..>
    test_function(db, "Health", "Stats", "WHERE CodeName='base'")

func my_udf(x:int) -> int:
    return x * 2

func test_function(db:SQLite, column_name:String, table_name:String, where:String) -> void:
    Log.Print()
    db.create_function("my_udf", my_udf, 1)
    var sql:String = "SELECT my_udf(" + column_name + ") FROM " + table_name + " " + where
    Log.Print("sql=" + sql, Log.DEBUG)
    var result = db.query(sql)
    Log.Print("result=" + str(result), Log.CRITICAL)

It don't write the my_func in database and result is true. What I'm missing? If I execute the sql in my db without calling my_udf it will work. Using PRAGMA function_list I don't see my_udf

Thanks

[2shady4u]

Hello @git2013vb,

I am assuming you are using Godot 4.X+? In that case you'll probably have to do it like this:

var callable := Callable(self, "my_udf")
db.create_function("my_udf", callable, 1)

[2shady4u]

Hello @git2013vb,

As there has been no response on my previous answer, I will consider this issue solved :)

[git2013vb]

Hi @2shady4u , sorry for the late answer :) Yes I use Godot 4.1. I tried to implement your suggestion in my code. I still have true as result This is my code with your suggestions (I added a log to check create_function result):

func my_udf(x:int) -> int:
    return x * 2

func test_function(db:SQLite, column_name:String, table_name:String, where:String) -> void:
    Log.Print()
    var callable := Callable(self, "my_udf")
    var create_result = db.create_function("my_udf", callable, 1)
    Log.Print("create_result=" + str(create_result), Log.CRITICAL)
    var sql:String = "SELECT my_udf(" + column_name + ") FROM " + table_name + " " + where
    Log.Print("sql=" + sql, Log.DEBUG)
    var result = db.query(sql)
    Log.Print("result=" + str(result), Log.CRITICAL)

this the logs:

2023-09-13 07:13:03.508 T: auto_load/db_helper.gd (27) [test_function] 
2023-09-13 07:13:03.508 C: auto_load/db_helper.gd (29) [test_function] create_result=true
2023-09-13 07:13:03.508 G: auto_load/db_helper.gd (31) [test_function] sql=SELECT my_udf(Health) FROM Stats WHERE CodeName='base'
2023-09-13 07:13:03.508 C: auto_load/db_helper.gd (33) [test_function] result=true

I used both of these to check if my_func is is database already:

select * from sqlean_define

PRAGMA function_list

But nothing. Not sure why it didn't work. I saw your source code too. What did I miss? :)

My suspects land to enable_load_extension. Can be disabled and in case of db in file have to be enabled?

Thanks

[2shady4u]

Hello @git2013vb

The query()-method does not return the query_result. Instead the query()-method returns a boolean which indicates if the query failed or not. (True = Query succeeded) The query result itself needs to be read from the query_result-property, as such:

    Log.Print("result=" + str(db.query_result), Log.CRITICAL)

Same with the create_function()-method; it returns a success boolean.

[git2013vb]

Hi @2shady4u, Thanks a lot. The result is what expected : result=[{ "my_udf(Health)": 1000 }] Do you know , by chance why PRAGMA function_list didn't show the function? create_function save it in different place?

Thanks.

[2shady4u]

Hi @git2013vb,

I tried this out and PRAGMA function_list; does return the custom function alongside a big list of already defined methods.

What is the value of the query_result-property after calling this pragma?

[git2013vb]

Hi, @2shady4u , I tried to tu run pragma in my code and this is the result...{ "name": "my_udf", "builtin": 0, "type": "s", "enc": "utf8", "narg": 1, "flags": 0 }, ... So, I guess I don't see it when I check it in dbeaver because i do it after I run my code and I close the db connection. So it will be removed from sqlite db. Sqlite do not make custom funcs persistent, unless you re-copile sqlite with them on it. I read this somewhere.

Sqlean do something different. It will make a custom table in database to save its custom functions. https://github.com/nalgeon/sqlean/tree/main

Thanks :)