SQLite create function

[Polly]

Hi, I'm trying to create a user-defined function in SQLite, something like this:

Code: Select all

ProcedureCDLL test(ctx, nArg, ppArg)
   Debug PeekS(ppArg)
   pArg=PeekL(ppArg)
   Debug pArg
   Debug PeekS(pArg)
EndProcedure

Debug sqlite3_create_function(handleDatabase, "test", 1, 1, 0, @test(), 0, 0)
sql$="select test(field) from table"

The creation returns ok, the callback procedure gets invoked properly, but I'm not able to read the argument (the table field), that what is in C: **argv. The Debugs only report nonsense or an empty string.

Does anybody have a working example?

[srod]

The callback function parameters (pointed to by the ppArg array) are in fact 'protected SQLite3 objects' and so you need to use SQLite3 functions to get at 'em!

The following works for me on a little database I cooked up :

Code: Select all

ImportC "sqlite3.lib" 
  sqlite3_create_function(hDB, zFunctionName.s, nArg, eTextRep, pApp, xFunc, xStep, xFinal)
  sqlite3_value_text(sqlite3_value)
EndImport 

ProcedureC test(ctx, nArg, ppArg) 
  pArg=PeekI(ppArg) 
  Debug PeekS(sqlite3_value_text(pArg))
EndProcedure 


UseSQLiteDatabase() 

; Open database 
  Nr=OpenDatabase(#PB_Any, "addresses.db", "", "", #PB_Database_SQLite) 
If Nr 
  hDB = PeekI(PeekI(Nr+SizeOf(INTEGER))) 
  sqlite3_create_function(hDB, "test", 1, 1, 0, @test(), 0, 0) 
  cmd$="select test(Surname) from customers"
  If DatabaseQuery(Nr, cmd$) 
    While NextDatabaseRow(Nr) 
    Wend 
    FinishDatabaseQuery(Nr)
  EndIf
  CloseDatabase(nr)
EndIf

You may need to import the sqlite3_value_type() function as well.

I hope it helps?

[Polly]

Yepp, thanks, helpful as always. One has to use the value* function for the arguments and the result* functions for the return value, that's all:

Code: Select all

ProcedureC test(ctx, nArg, ppArg)
   pArg=PeekL(ppArg)
   Global strg$=PeekS(sqlite3_value_text(pArg))
   strg$=UCase(strg$)
   sqlite3_result_text(ctx, @strg$, Len(strg$), 0)
   ProcedureReturn 0
EndProcedure

[srod]

Indeed.

btw, you can use

Code: Select all

hDB = DatabaseID(Nr)

to get the SQLite DB handle as opposed to all the Peeking I did in the above code!

[Oliver13]

Sorry for bringing up this topic - but I do not see the forest for the trees..

Obviously, also this funtcion must be imported:

sqlite3_result_text(ctx, @strg$, Len(strg$), 0)

I tried as following, but does not work.

ImportC "sqlite3.lib"
sqlite3_result_text(*sqlite3_context, *returnvalue, nBytes, nParam)
EndImport

So can anybody help ?
TX

[infratec]

Hi,

a small example:

Code: Select all

ImportC "sqlite3.lib" 
  sqlite3_create_function.l(DatabaseID, zFunctionName.p-utf8, nArg.l, eTextRep.l, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_aggregate_context(*sqlite3_context, nBytes.l)
  sqlite3_result_double(*sqlite3_context, dbl.d)
  sqlite3_result_text(*sqlite3_context, *returnvalue, nBytes.l, *nParam)
  sqlite3_value_numeric_type.l(*sqlite3_value)
  sqlite3_value_double.d(*sqlite3_value)
  sqlite3_value_int.i(*sqlite3_value)
  
  sqlite3_value_type.i(*argv)
  
  ;Neue Funktionen
  sqlite3_backup_init(pDest, zDestName.p-ascii, pSource, zSourceName.p-ascii)
  sqlite3_backup_step(sqlite3_backup, nPage)
  sqlite3_backup_finish(sqlite3_backup)
  sqlite3_errcode(db)
EndImport


#SQLITE_UTF8 = 1  ; IMP: R-37514-35566
#SQLITE_UTF16LE = 2 ; IMP: R-03371-37637
#SQLITE_UTF16BE = 3 ; IMP: R-51971-34154
#SQLITE_UTF16 = 4 ; Use native byte order
#SQLITE_ANY = 5 ; Deprecated
#SQLITE_UTF16_ALIGNED = 8 ; sqlite3_create_collation only


#SQLITE_INTEGER = 1
#SQLITE_FLOAT = 2
#SQLITE_TEXT = 3
#SQLITE_BLOB = 4
#SQLITE_NULL = 5

#SQLITE_STATIC = 0
#SQLITE_TRANSIENT = -1


ProcedureC TypeOfFunc(*context, argc.l, *argv)
  
  Protected *Buffer
      
    Select sqlite3_value_type(PeekL(*argv))
      Case #SQLITE_NULL
        *Buffer = UTF8("null")
      Case #SQLITE_INTEGER
        *Buffer = UTF8("integer")
      Case #SQLITE_TEXT
        *Buffer = UTF8("text")
      Case #SQLITE_FLOAT
        *Buffer = UTF8("real")
      Case #SQLITE_BLOB
        *Buffer = UTF8("blob")
    EndSelect
    sqlite3_result_text(*context, *Buffer, -1, #SQLITE_TRANSIENT)
    
    If *Buffer
      FreeMemory(*Buffer)
    EndIf
    
EndProcedure




Procedure RegisterSQLiteFunctions(dataBase.i)
  
  Protected dataBaseID.i
  
  
  dataBaseID = DatabaseID(dataBase)
  sqlite3_create_function(dataBaseID, "typeofcol",  1, #SQLITE_UTF8, #Null, @TypeofFunc(), #Null, #Null)
  
EndProcedure


UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite) 
If DB
  RegisterSQLiteFunctions(DB)
  
  DatabaseUpdate(DB, "CREATE TABLE myTable (id INTEGER, data DOUBLE, description text)")
  DatabaseUpdate(DB, "INSERT INTO myTable VALUES ( 1, 18.5, 'bla')")
  
  If DatabaseQuery(DB, "SELECT typeofcol(id), typeofcol(data), typeofcol(description) FROM myTable")
    If NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0)
      Debug GetDatabaseString(DB, 1)
      Debug GetDatabaseString(DB, 2)
    EndIf
    FinishDatabaseQuery(DB)
  Else
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(DB)
EndIf

Bernd

[mk-soft]

Cool

But so work with Window, Linux, MacOS and x86,x64

Code: Select all

ImportC ""
  sqlite3_create_function.i(DatabaseID, zFunctionName.p-utf8, nArg.i, eTextRep.i, *pApp, *xFunc, *xStep, *xFinal)
  sqlite3_aggregate_context(*sqlite3_context, nBytes.i)
  sqlite3_result_double(*sqlite3_context, dbl.d)
  sqlite3_result_text(*sqlite3_context, *returnvalue, nBytes.i, *nParam)
  sqlite3_value_numeric_type.i(*sqlite3_value)
  sqlite3_value_double.d(*sqlite3_value)
  sqlite3_value_int.i(*sqlite3_value)
  
  sqlite3_value_type.i(*argv)
  
  ;Neue Funktionen
  sqlite3_backup_init(pDest, zDestName.p-ascii, pSource, zSourceName.p-ascii)
  sqlite3_backup_step(sqlite3_backup, nPage)
  sqlite3_backup_finish(sqlite3_backup)
  sqlite3_errcode(db)
EndImport


#SQLITE_UTF8 = 1  ; IMP: R-37514-35566
#SQLITE_UTF16LE = 2 ; IMP: R-03371-37637
#SQLITE_UTF16BE = 3 ; IMP: R-51971-34154
#SQLITE_UTF16 = 4 ; Use native byte order
#SQLITE_ANY = 5 ; Deprecated
#SQLITE_UTF16_ALIGNED = 8 ; sqlite3_create_collation only


#SQLITE_INTEGER = 1
#SQLITE_FLOAT = 2
#SQLITE_TEXT = 3
#SQLITE_BLOB = 4
#SQLITE_NULL = 5

#SQLITE_STATIC = 0
#SQLITE_TRANSIENT = -1

ProcedureC TypeOfFunc(*context, argc.i, *argv.integer)
  
  Protected *Buffer
      
    Select sqlite3_value_type(*argv\i)
      Case #SQLITE_NULL
        *Buffer = UTF8("null")
      Case #SQLITE_INTEGER
        *Buffer = UTF8("integer")
      Case #SQLITE_TEXT
        *Buffer = UTF8("text")
      Case #SQLITE_FLOAT
        *Buffer = UTF8("real")
      Case #SQLITE_BLOB
        *Buffer = UTF8("blob")
    EndSelect
    sqlite3_result_text(*context, *Buffer, -1, #SQLITE_TRANSIENT)
    
    If *Buffer
      FreeMemory(*Buffer)
    EndIf
    
EndProcedure

Procedure RegisterSQLiteFunctions(dataBase.i)
  
  Protected dataBaseID.i
  
  
  dataBaseID = DatabaseID(dataBase)
  sqlite3_create_function(dataBaseID, "typeofcol",  1, #SQLITE_UTF8, #Null, @TypeofFunc(), #Null, #Null)
  
EndProcedure


UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite) 
If DB
  RegisterSQLiteFunctions(DB)
  
  DatabaseUpdate(DB, "CREATE TABLE myTable (id INTEGER, data DOUBLE, description text)")
  DatabaseUpdate(DB, "INSERT INTO myTable VALUES ( 1, 18.5, 'bla')")
  
  If DatabaseQuery(DB, "SELECT typeofcol(id), typeofcol(data), typeofcol(description) FROM myTable")
    If NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0)
      Debug GetDatabaseString(DB, 1)
      Debug GetDatabaseString(DB, 2)
    EndIf
    FinishDatabaseQuery(DB)
  Else
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(DB)
EndIf