PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

SQLite create function

https://www.purebasic.fr/english/viewtopic.php?t=36272

Page 1 of 1

SQLite create function

Posted: Tue Feb 03, 2009 10:45 am
by 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?

Posted: Tue Feb 03, 2009 11:43 am
by 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?

Posted: Tue Feb 03, 2009 1:13 pm
by 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

Posted: Tue Feb 03, 2009 3:02 pm
by 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!

Re:

Posted: Fri Jan 25, 2019 6:30 pm
by 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

Re: SQLite create function

Posted: Fri Jan 25, 2019 8:28 pm
by 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

Re: SQLite create function

Posted: Fri Jan 25, 2019 11:32 pm
by mk-soft
Cool :wink:

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
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited