[Gambas-user] create custom user functions in SQLITE3 from Gambas

[Angel]

El 4/4/23 a las 21:29, Benoit Minisini escribió:
> Le 04/04/2023 à 20:51, Angel a écrit :
>> hello
>>
>> I am trying to create custom user functions in SQLITE3 from Gambas 
>> but given my ignorance of C it is impossible.
>>
>> Has anyone tried?The function in C is:
>>
>> int sqlite3_create_function(
>>    sqlite3 *db,
>>    const char *zFunctionName,
>>    int nArg,
>>    int eTextRep,
>>    void *pApp,
>>    void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
>>    void (*xStep)(sqlite3_context*,int,sqlite3_value**),
>>    void (*xFinal)(sqlite3_context*)
>> );
>>
>> The explanation at: https://www.sqlite.org/c3ref/create_function.html
>>
>> I've done:
>>
>>
>> ' Gambas module file
>>
>> Library "libsqlite3"
>> Private Extern sqlite3_create_function(db As Pointer, name As String, 
>> arg As Integer, eTextRep As Integer, pApp As Pointer, xFun As 
>> Pointer, xStep As Pointer, xFinal As Pointer)
>> Private BD As Connection
>>
>> Public Sub Main()
>>
>>      '#define SQLITE_UTF8     1
>>
>>      Dim punt1 As Pointer
>>      Dim punt2 As Pointer
>>      Dim ia As Integer
>>
>>      BD = New Connection
>>      With BD
>>          .Type = "sqlite3"
>>          .Host = Application.Path
>>          'verificating if db exist
>>          .Name = ""
>>          .Open
>>      End With
>>
>>      punt1 = VarPtr(BD)
>>      Debug sepTelef("666777888")
>>      Debug punt1
>>
>>      ia = sqlite3_create_function(punt1, "sepTelef", 1, 1, Null, 
>> sepTelef, Null, Null)
>>
>> End
>>
>> Function sepTelef(telefono As String) As String
>>
>>      Dim scad As String
>>
>>      scad = Mid(telefono, 1, 3) & "-" & Mid(telefono, 4, 3) & "-" & 
>> Mid(telefono, 7, 3)
>>      Return scad
>>
>> End
>>
>>
>> result
>>
>> Segment violation 11
>>
>
> 'VarPtr(BD)' is the memory address where a pointer to a Gambas object 
> (BD) is stored. It has nothing to do with the "sqlite3 *" pointer of 
> the underlying connection. So It cannot work.
>
> The Handle property of BD returns a pointer that points at a structure 
> that represents a SQLite database to Gambas. But it's still not the 
> "sqlite3 *" pointer you need.
>
> Fortunately, this pointer is stored at the beginning of that 
> structure. (you can know that by reading the source code of the 
> 'gb.db.sqlite3' component).
>
> So Try 'Pointer@(BD.Handle)' instead of 'VarPtr(BD)'.
>
> And try a bit to understand how C works otherwise you won't really 
> understand how external function management works in Gambas!
>
> Regards,
>
Thanks to Benoit for giving me the track of 'Pointer@(Bd.handle)'

I thought that creating a user function in SQLite3 would be something 
similar to declare external function, creating a function in Gambas3 and 
SQLite3 would use the value returned by the function.
But it seems that SQLite3 uses calling functions, and is more complicated.
I leave an example in case someone may be interested.


' Gambas module file

' codigo sacado de 
https://stackoverflow.com/questions/7867099/how-can-i-create-a-user-defined-function-in-sqlite
' codigo en java
'Static void firstchar(sqlite3_context * context, int argc, 
sqlite3_value * * argv)
'{
'    If (argc == 1) {
'        char * text = sqlite3_value_text(argv[0]);
'        If (text & & text[0]) {
'          char result[2];
'          result[0] = text[0]; result[1] = '\0';
'          sqlite3_result_text(context, result, -1, SQLITE_TRANSIENT);
'          Return ;
'        }
'    }
'    sqlite3_result_null(context);
'}


Library "libsqlite3" 'declaracion de la libreria

'int sqlite3_create_function(sqlite3 * db, Const char * zFunctionName, 
int nArg, int eTextRep, void * pApp, void( * xFunc)(sqlite3_context *, 
int, sqlite3_value * * ), void( * xStep)(sqlite3_context *, int, 
sqlite3_value * * ), void( * xFinal)(sqlite3_context * ) );
Private Extern sqlite3_create_function(db As Pointer, name As String, 
Argc As Integer, numParam As Integer, pApp As Pointer, xFun As Pointer, 
xStep As Pointer, xFinal As Pointer)

' void sqlite3_result_text(sqlite3_context*, const char*, int, 
void(*)(void*));
Private Extern sqlite3_result_text(sqlite3_context As Pointer, cadena As 
String, numParam As Integer, valorsqlite As Pointer)

' void sqlite3_result_null(sqlite3_context*);
Private Extern sqlite3_result_null(sqlite3_context As Pointer)

' const unsigned char *sqlite3_value_text(sqlite3_value*);
Private Extern sqlite3_value_text(sqlite3_value As Pointer) As String

Private BD As Connection

Public Sub Main()

     '#define SQLITE_UTF8     1
     Dim punt1 As Pointer
     Dim ia As Integer
     Dim rsFun As Result
     Dim tb As Table
     Dim rsCli As Result
     Dim snombre, stel As String

     BD = New Connection
     With BD ' creamos Bd en memoria
         .Type = "sqlite3"
         .Host = Application.Path
         .Name = ""
         .Open
     End With
     ' creamos una tabla con dos campos en la conexion
     bd.Tables.Add("cli")
     tb = bd.Tables["cli"]
     tb.Fields.Add("nombre", gb.String, 9)
     tb.Fields.Add("telf", gb.String, 9)
     tb.Update
     rsCli = bd.Create("cli")

     For i As Integer = 0 To 20 ' llenamos la tabla
         Randomize
         ia = Rand(100000000, 999999999)
         rsCli!nombre = "nombre_" & Str(i)
         rsCli!telf = Str(ia)
         rsCli.Update
     Next

     punt1 = Pointer@(BD.Handle) ' obtencion del puntero de la conexion
     Debug punt1

     ' declaracion de user function
     sqlite3_create_function(punt1, "sepTelef", 1, 1, Null, sepTelef, 
Null, Null)
     rsFun = BD.Exec("Select nombre, telf, sepTelef(telf) as funTelf 
 From cli") ' Result con la llamada a la funcion
     rsFun.MoveFirst
     For i As Integer = 0 To rsFun.Max
         Debug rsFun!nombre, rsFun!telf, rsFun!funTelf
         rsFun.MoveNext
     Next

End

Sub sepTelef(sqlite3_context As Pointer, Argc As Integer, sqlite_val As 
Pointer)
     ' void sqlite3_result_text(sqlite3_context*, const char*, int, 
void(*)(void*));
     ' const unsigned char *sqlite3_value_text(sqlite3_value*);
     '#define SQLITE_STATIC      ((sqlite3_destructor_type)0)
     '#define SQLITE_TRANSIENT   ((sqlite3_destructor_type)-1)

     Dim scad, tel As String
     Dim punt1 As Pointer

     If Argc = 1 Then
         punt1 = Pointer@(sqlite_val)
         tel = sqlite3_value_text(punt1)
         scad = Mid(tel, 1, 3) & "-" & Mid(tel, 4, 3) & "-" & Mid(tel, 7, 3)
         sqlite3_result_text(sqlite3_context, scad, -1, -1)
         Return
     Endif
     sqlite3_result_null(sqlite3_context)

End