Is also still expandable
Update v1.03
- Bugfix CDecl
- Added string example
Update v1.04
- Bugfix sqlite3_result_text. Show sql_destructor_freememory
Update v1.05.2
- Optimization
- Update example
Code: Select all
;-TOP by mk-soft
; SQLite C-Interface v1.05.2
; Update 25.02.2024
; Link: https://www.purebasic.fr/english/viewtopic.php?t=75264
UseSQLiteDatabase()
ImportC ""
sqlite3_create_function.i(DatabaseID, zFunctionName.p-utf8, nArg.i, eTextRep.i, *pApp, *xFunc, *xStep, *xFinal)
sqlite3_create_function16.i(DatabaseID, zFunctionName.s, nArg.i, eTextRep.i, *pApp, *xFunc, *xStep, *xFinal)
sqlite3_aggregate_context(*sqlite3_context, nBytes.i)
sqlite3_result_int(*sqlite3_value, lVal.l)
sqlite3_result_int64(*sqlite3_value, qVal.q)
sqlite3_result_double(*sqlite3_context, dblVal.d)
sqlite3_result_text(*sqlite3_context, *char, cBytes, *void1, *void2)
sqlite3_result_text16(*sqlite3_context, *char, cBytes, *void1, *void2)
sqlite3_value_numeric_type.i(*sqlite3_value)
sqlite3_value_int.l(*sqlite3_value)
sqlite3_value_int64.q(*sqlite3_value)
sqlite3_value_double.d(*sqlite3_value)
sqlite3_value_text(*sqlite3_value)
sqlite3_value_text16(*sqlite3_value)
sqlite3_value_type.i(*argv)
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
Structure udtArgv
*Index[0]
EndStructure
; ********
CompilerIf #PB_Compiler_IsMainFile
Procedure Unicode(String.s)
Protected *mem
*mem = AllocateMemory(StringByteLength(String) + SizeOf(Character))
If *mem
PokeS(*mem, String, -1, #PB_Unicode)
EndIf
ProcedureReturn *mem
EndProcedure
; ****
ProcedureC sql_sin(*context, argc.i, *argv.udtArgv)
Protected a.d
a = sqlite3_value_double(*argv\Index[0])
a = Sin(a)
sqlite3_result_double(*context, a)
EndProcedure
; ----
ProcedureC sql_cos(*context, argc.i, *argv.udtArgv)
Protected a.d
a = sqlite3_value_double(*argv\Index[0])
a = Cos(a)
sqlite3_result_double(*context, a)
EndProcedure
; ----
ProcedureC sql_tan(*context, argc.i, *argv.udtArgv)
Protected a.d
a = sqlite3_value_double(*argv\Index[0])
a = Tan(a)
sqlite3_result_double(*context, a)
EndProcedure
; ****
ProcedureC sql_destructor_freememory(*void)
If *void
Debug "SQL-Destructor: FreeMemory: " + *void
FreeMemory(*void)
EndIf
EndProcedure
; ----
; ProcedureC sql_lcase(*context, argc.i, *argv.udtArgv)
; Protected *string, *result
; *string = sqlite3_value_text(*argv\Index[0])
; *result = UTF8(LCase(PeekS(*string, -1, #PB_UTF8)))
; sqlite3_result_text(*context, *result, -1, @sql_destructor_freememory(), 0)
; EndProcedure
ProcedureC sql_lcase(*context, argc.i, *argv.udtArgv)
Protected *string, result.s
*string = sqlite3_value_text16(*argv\Index[0])
result = LCase(PeekS(*string))
sqlite3_result_text16(*context, result, -1, #SQLITE_TRANSIENT, 0)
EndProcedure
; ----
; ProcedureC sql_ucase(*context, argc.i, *argv.udtArgv)
; Protected *string, *result
; *string = sqlite3_value_text(*argv\Index[0])
; *result = UTF8(UCase(PeekS(*string, -1, #PB_UTF8)))
; sqlite3_result_text(*context, *result, -1, @sql_destructor_freememory(), 0)
; EndProcedure
ProcedureC sql_ucase(*context, argc.i, *argv.udtArgv)
Protected *string, result.s
*string = sqlite3_value_text16(*argv\Index[0])
result = UCase(PeekS(*string))
sqlite3_result_text16(*context, result, -1, #SQLITE_TRANSIENT, 0)
EndProcedure
; ----
ProcedureC sql_myfunction(*context, argc.i, *argv.udtArgv)
Protected i
Debug "* sql_myfunction:"
For i = 0 To argc -1
Select sqlite3_value_numeric_type(*argv\Index[i])
Case #SQLITE_INTEGER
Debug "- Integer Arg[" + i + "] = " + sqlite3_value_int64(*argv\Index[i])
Case #SQLITE_FLOAT
Debug "- Float Arg[" + i + "] = " + sqlite3_value_double(*argv\Index[i])
Case #SQLITE_TEXT
Debug "- Text Arg[" + i + "] = " + PeekS(sqlite3_value_text16(*argv\Index[i]))
Case #SQLITE_BLOB
Debug "- Blob Arg[" + i + "] = Blob"
Case #SQLITE_NULL
Debug "- Null Arg[" + i + "] = NULL"
EndSelect
Next
Debug "****"
sqlite3_result_int(*context, 1)
EndProcedure
ProcedureC sql_myfunction2(*context, argc.i, *argv.udtArgv)
Debug "* sql_myfunction2:"
For i = 0 To argc -1
Select sqlite3_value_numeric_type(*argv\Index[i])
Case #SQLITE_INTEGER
Debug "- Integer Arg[" + i + "] = " + sqlite3_value_int64(*argv\Index[i])
Case #SQLITE_FLOAT
Debug "- Float Arg[" + i + "] = " + sqlite3_value_double(*argv\Index[i])
Case #SQLITE_TEXT
Debug "- Text Arg[" + i + "] = " + PeekS(sqlite3_value_text16(*argv\Index[i]))
Case #SQLITE_BLOB
Debug "- Blob Arg[" + i + "] = Blob"
Case #SQLITE_NULL
Debug "- Null Arg[" + i + "] = NULL"
EndSelect
Next
Debug "****"
sqlite3_result_int(*context, 2)
EndProcedure
; ****
Procedure RegisterSQLiteFunctions(DataBase.i)
Protected dataBaseID.i = DatabaseID(DataBase)
sqlite3_create_function16(dataBaseID, "Sin", 1, #SQLITE_UTF8, #Null, @sql_sin(), #Null, #Null)
sqlite3_create_function16(dataBaseID, "Cos", 1, #SQLITE_UTF8, #Null, @sql_cos(), #Null, #Null)
sqlite3_create_function16(dataBaseID, "Tan", 1, #SQLITE_UTF8, #Null, @sql_tan(), #Null, #Null)
; sqlite3_create_function16(dataBaseID, "LCase", 1, #SQLITE_UTF8, #Null, @sql_lcase(), #Null, #Null)
; sqlite3_create_function16(dataBaseID, "UCase", 1, #SQLITE_UTF8, #Null, @sql_ucase(), #Null, #Null)
; Replace functions
sqlite3_create_function16(dataBaseID, "lower", 1, #SQLITE_UTF16, #Null, @sql_lcase(), #Null, #Null)
sqlite3_create_function16(dataBaseID, "upper", 1, #SQLITE_UTF16, #Null, @sql_ucase(), #Null, #Null)
; Same function name with diverent parameter count
sqlite3_create_function16(dataBaseID, "myfunc", 1, #SQLITE_UTF8, #Null, @sql_myfunction(), #Null, #Null)
sqlite3_create_function16(dataBaseID, "myfunc", 2, #SQLITE_UTF8, #Null, @sql_myfunction2(), #Null, #Null)
EndProcedure
; ----
Procedure CheckDatabaseUpdate(Database, Query.s)
Protected r1
r1 = DatabaseUpdate(Database, Query.s)
If r1 = 0
Debug "Error DatabaseUpdate: " + DatabaseError()
EndIf
ProcedureReturn r1
EndProcedure
Procedure CheckDatabaseQuery(Database, Query.s)
Protected r1
r1 = DatabaseQuery(Database, Query.s)
If r1 = 0
Debug "Error DatabaseQuery: " + DatabaseError()
EndIf
ProcedureReturn r1
EndProcedure
; ----
#DB_Calc = 0
Global db_file.s, sql.s
; ----
db_file = ":memory:"
If Not OpenDatabase(#DB_Calc, db_file, "", "")
Debug "Error Opendatabase"
End
EndIf
RegisterSQLiteFunctions(#DB_Calc)
sql = "CREATE TABLE vars (recid int, a double, b double, c double, d double)"
If Not CheckDatabaseUpdate(#DB_Calc, sql)
End
EndIf
sql = "INSERT INTO vars (recid, a, b, c, d) VALUES (0, 1, 2, 3, 4)"
If Not CheckDatabaseUpdate(#DB_Calc, sql)
End
EndIf
sql = "INSERT INTO vars (recid, a, b, c, d) VALUES (1, 10, 20, 30, 40)"
If Not CheckDatabaseUpdate(#DB_Calc, sql)
End
EndIf
sql = "INSERT INTO vars (recid, a, b, c, d) VALUES (2, 100, 200, 300, 400)"
If Not CheckDatabaseUpdate(#DB_Calc, sql)
End
EndIf
sql = "Select recid, (a * b + c) From vars"; where recid = 0"
If CheckDatabaseQuery(#DB_Calc, sql)
While NextDatabaseRow(#DB_Calc)
Debug "RecID " + GetDatabaseLong(#DB_Calc, 0) + ": a * b + c = " + GetDatabaseDouble(#DB_Calc, 1)
Debug "---------------------------"
Wend
EndIf
sql = "Select sin(a), cos(a), tan(a) From vars"; where recid = 0"
If CheckDatabaseQuery(#DB_Calc, sql)
While NextDatabaseRow(#DB_Calc)
Debug "Sin(a) = " + GetDatabaseDouble(#DB_Calc, 0)
Debug "Cos(a) = " + GetDatabaseDouble(#DB_Calc, 1)
Debug "Tan(a) = " + GetDatabaseDouble(#DB_Calc, 2)
Debug "---------------------------"
Wend
EndIf
sql = "Select lower('Hello World! ÄÖÜ'), upper('I Like Purebasic! äöü')"; where recid = 0"
If CheckDatabaseQuery(#DB_Calc, sql)
While NextDatabaseRow(#DB_Calc)
Debug "LCase = " + GetDatabaseString(#DB_Calc, 0)
Debug "UCase = " + GetDatabaseString(#DB_Calc, 1)
Debug "---------------------------"
Wend
EndIf
sql = "Select myfunc(1), myfunc(1.1,'Hello')"
If CheckDatabaseQuery(#DB_Calc, sql)
While NextDatabaseRow(#DB_Calc)
Debug "0: Result = " + GetDatabaseString(#DB_Calc, 0)
Debug "1: Result = " + GetDatabaseString(#DB_Calc, 1)
Debug "---------------------------"
Wend
EndIf
CompilerEndIf