PureBasic Forums - English

Code: Select all

;-TOP by mk-soft

UseSQLiteDatabase()

; SQLite C-Interface v1.04

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
  
  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
  
  ; ****
  
  Procedure RegisterSQLiteFunctions(DataBase.i)
    Protected dataBaseID.i = DatabaseID(DataBase)
    
    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)
    
  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 = 0
  
  Global db_file.s, sql.s
  
  ; ----
  
  db_file = ":memory:"
  
  If Not OpenDatabase(#DB, db_file, "", "")
    Debug "Error Opendatabase"
    End
  EndIf
  
  RegisterSQLiteFunctions(#DB)
  
  sql = "Select lower('Hello World! ÄÖÜ'), upper('I Like Purebasic! äöü')"; where recid = 0"
  If CheckDatabaseQuery(#DB, sql)
    While NextDatabaseRow(#DB)
      Debug "Lower = " + GetDatabaseString(#DB, 0)
      Debug "Upper = " + GetDatabaseString(#DB, 1)
      Debug "---------------------------"
    Wend
  EndIf
  
CompilerEndIf

Code: Select all

UseSQLiteDatabase("mySQLite.dll")