SQLite UDF - VBA Callback

Question

Has anyone tried to pass a VBA (or VB6) function (via AddressOf?) To SQLite by creating a UDF function ( http://www.sqlite.org/c3ref/create_function.html ).

How will the resulting callback arguments be handled using VBA?

The function to be called will have the following signature ...

void (xFunc) (sqlite3_context, int, sqlite3_value **)

Answer 1

Unfortunately, you cannot use the VB6 / VBA function as a callback directly, since VB6 only generates stdcall functions, not the cdecl functions expected by SQLite.

You will need to write a C dll to proxy back and forth calls or recompile SQLite to support your own custom extension.

After recompiling your DLL to export functions as stdcall you can register a function with the following code:

 'Create Function Public Declare Function sqlite3_create_function Lib "SQLiteVB.dll" (ByVal db As Long, ByVal zFunctionName As String, ByVal nArg As Long, ByVal eTextRep As Long, ByVal pApp As Long, ByVal xFunc As Long, ByVal xStep As Long, ByVal xFinal As Long) As Long 'Gets a value Public Declare Function sqlite3_value_type Lib "SQLiteVB.dll" (ByVal arg As Long) As SQLiteDataTypes 'Gets the type Public Declare Function sqlite3_value_text_bstr Lib "SQLiteVB.dll" (ByVal arg As Long) As String 'Gets as String Public Declare Function sqlite3_value_int Lib "SQLiteVB.dll" (ByVal arg As Long) As Long 'Gets as Long 'Sets the Function Result Public Declare Sub sqlite3_result_int Lib "SQLiteVB.dll" (ByVal context As Long, ByVal value As Long) Public Declare Sub sqlite3_result_error_code Lib "SQLiteVB.dll" (ByVal context As Long, ByVal value As Long) Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, source As Any, ByVal bytes As Long) Public Property Get ArgValue(ByVal argv As Long, ByVal index As Long) As Long CopyMemory ArgValue, ByVal (argv + index * 4), 4 End Property Public Sub FirstCharCallback(ByVal context As Long, ByVal argc As Long, ByVal argv As Long) Dim arg1 As String If argc >= 1 Then arg1 = sqlite3_value_text_bstr(ArgValue(argv, 0)) sqlite3_result_int context, AscW(arg1) Else sqlite3_result_error_code context, 666 End If End Sub Public Sub RegisterFirstChar(ByVal db As Long) sqlite3_create_function db, "FirstChar", 1, 0, 0, AddressOf FirstCharCallback, 0, 0 'Example query: SELECT FirstChar(field) FROM Table End Sub