SQLite3 Updated

Share your advanced PureBasic knowledge/code with the community.
Post Reply
chris319
Enthusiast
Enthusiast
Posts: 782
Joined: Mon Oct 24, 2005 1:05 pm

SQLite3 Updated

Post by chris319 »

This post contains two programs. sqlite3.pb builds upon kiffi's very fine work with SQLite3. sqlite3_toy.pb is a simple demonstration program.

Kiffi's sqlite3.pb has been modified, mainly abbreviating procedure names for less typing. In addition, two new procedures have been added: SQLite3_Row() selects a specified row, and SQLite3_GetRowCol() gets data through direct row and column addressing (rows 1 to n; columns 0 to n). sqlite3.pb has also been made compatible with PureBasic 4.0. Comments have been (poorly) translated from Deutsch to English.

Please report any issues to chris319.

sqlite3.pb

Code: Select all

;sqlite3.pb
;builds upon kiffi's fine work with sqlite3
;runs on purebasic 4.0+ for windows
;requires sqlite3.dll
;sqlite3.dll can be obtained from http://www.sqlite.org/download.html

;modified on 4/10/2006 by chris319
;made compatible with purebasic version 4

;added two new procedures:

;SQLite3_GetRowCol(row.l, col.l, *Recordset.s_RecordSet)
;fetches Data through direct row And column addressing

;SQLite3_Row(row.l, *Recordset.s_RecordSet)
;selects a specific row number

;abbreviated the following procedure names for less typing and improved clarity
;SQLite3_GetRecordset -> SQLite3_GetTable
;SQLite3_RecordsetMoveFirst -> SQLite3_FirstRow
;SQLite3_RecordsetMoveLast -> SQLite3_LastRow
;SQLite3_RecordsetMoveNext -> SQLite3_NextRow
;SQLite3_RecordsetMovePrevious -> SQLite3_PrevRow
;SQLite3_GetRecordsetValueByName -> SQLite3_GetDataByName
;SQLite3_GetRecordsetValueByIndex -> SQLite3_GetDataByIndex
;SQLite3_ReleaseRecordset-> SQLite3_ReleaseTable


Structure s_RecordSet
  BOF.l
  EOF.l
  Handle.l
  Rows.l
  Cols.l
  CurrentPos.l
  sValue.s
EndStructure

#SQLITE3_OK          =   0   ; Successful Result
#SQLITE3_ERROR       =   1   ; SQL error Or missing database
#SQLITE3_INTERNAL    =   2   ; An internal logic error in SQLite
#SQLITE3_PERM        =   3   ; Access permission denied
#SQLITE3_ABORT       =   4   ; Callback routine requested An abort
#SQLITE3_BUSY        =   5   ; The database file is locked
#SQLITE3_LOCKED      =   6   ; A table in The database is locked
#SQLITE3_NOMEM       =   7   ; A malloc() failed
#SQLITE3_READONLY    =   8   ; Attempt To write A readonly database
#SQLITE3_INTERRUPT   =   9   ; Operation terminated by SQLite_Interrupt()
#SQLITE3_IOERR       =  10   ; Some kind of disk I/O error occurred
#SQLITE3_CORRUPT     =  11   ; The database disk image is malformed
#SQLITE3_NOTFOUND    =  12   ; (internal Only) table Or record not found
#SQLITE3_FULL        =  13   ; Insertion failed because database is full
#SQLITE3_CANTOPEN    =  14   ; Unable To open The database file
#SQLITE3_PROTOCOL    =  15   ; database lock protocol error
#SQLITE3_EMPTY       =  16   ; (internal Only) database table is empty
#SQLITE3_SCHEMA      =  17   ; The database schema changed
#SQLITE3_TOOBIG      =  18   ; Too much Data For one Row of A table
#SQLITE3_CONSTRAINT  =  19   ; abort due To contraint violation
#SQLITE3_MISMATCH    =  20   ; Data type mismatch
#SQLITE3_MISUSE      =  21   ; Library used incorrectly
#SQLITE3_NOLFS       =  22   ; Uses OS features not supported on host
#SQLITE3_AUTH        =  23   ; Authorization denied
#SQLITE3_ROW         = 100   ; sqlite_step() has another Row ready
#SQLITE3_DONE        = 101   ; sqlite_step() has finished executing

;- Declarations

Global LastMessage.s

Global sqlite3_aggregate_context.l
Global sqlite3_aggregate_count.l
Global sqlite3_bind_blob.l
Global sqlite3_bind_double.l
Global sqlite3_bind_int.l
Global sqlite3_bind_int64.l
Global sqlite3_bind_null.l
Global sqlite3_bind_parameter_count.l
Global sqlite3_bind_parameter_index.l
Global sqlite3_bind_parameter_name.l
Global sqlite3_bind_text.l
Global sqlite3_bind_text16.l
Global sqlite3_busy_handler.l
Global sqlite3_busy_timeout.l
Global sqlite3_changes.l
Global sqlite3_close.l
Global sqlite3_collation_needed.l
Global sqlite3_collation_needed16.l
Global sqlite3_column_blob.l
Global sqlite3_column_bytes.l
Global sqlite3_column_bytes16.l
Global sqlite3_column_count.l
Global sqlite3_column_decltype.l
Global sqlite3_column_decltype16.l
Global sqlite3_column_double.l
Global sqlite3_column_int.l
Global sqlite3_column_int64.l
Global sqlite3_column_name.l
Global sqlite3_column_name16.l
Global sqlite3_column_text.l
Global sqlite3_column_text16.l
Global sqlite3_column_type.l
Global sqlite3_commit_hook.l
Global sqlite3_complete.l
Global sqlite3_complete16.l
Global sqlite3_create_collation.l
Global sqlite3_create_collation16.l
Global sqlite3_create_function.l
Global sqlite3_create_function16.l
Global sqlite3_data_count.l
Global sqlite3_errcode.l
Global sqlite3_errmsg.l
Global sqlite3_errmsg16.l
Global sqlite3_exec.l
Global sqlite3_finalize.l
Global sqlite3_free.l
Global sqlite3_free_table.l
Global sqlite3_get_auxdata.l
Global sqlite3_get_table.l
Global sqlite3_interrupt.l
Global sqlite3_last_insert_rowid.l
Global sqlite3_libversion.l
Global sqlite3_mprintf.l
Global sqlite3_open.l
Global sqlite3_open16.l
Global sqlite3_prepare.l
Global sqlite3_prepare16.l
Global sqlite3_progress_handler.l
Global sqlite3_reset.l
Global sqlite3_result_blob.l
Global sqlite3_result_double.l
Global sqlite3_result_error.l
Global sqlite3_result_error16.l
Global sqlite3_result_int.l
Global sqlite3_result_int64.l
Global sqlite3_result_null.l
Global sqlite3_result_text.l
Global sqlite3_result_text16.l
Global sqlite3_result_text16be.l
Global sqlite3_result_text16le.l
Global sqlite3_result_value.l
Global sqlite3_set_authorizer.l
Global sqlite3_set_auxdata.l
Global sqlite3_snprintf.l
Global sqlite3_step.l
Global sqlite3_total_changes.l
Global sqlite3_trace.l
Global sqlite3_user_data.l
Global sqlite3_value_blob.l
Global sqlite3_value_bytes.l
Global sqlite3_value_bytes16.l
Global sqlite3_value_double.l
Global sqlite3_value_int.l
Global sqlite3_value_int64.l
Global sqlite3_value_text.l
Global sqlite3_value_text16.l
Global sqlite3_value_text16be.l
Global sqlite3_value_text16le.l
Global sqlite3_value_type.l
Global sqlite3_vmprintf.l

;// Globale Variable der DLL
Global sqlite3DLL
;- 
;// Procedure um DLL wieder freizugeben
ProcedureDLL.l SQLite3_End()
  If sqlite3DLL
    CloseLibrary(sqlite3DLL)
  EndIf
  sqlite3DLL = 0
EndProcedure

ProcedureDLL.l SQLite3_InitLib(sSQLiteDLLPath.s)
;// umgestellt auf #PB_Any
  sqlite3DLL = OpenLibrary(#PB_Any, sSQLiteDLLPath)
  If sqlite3DLL
    sqlite3_aggregate_context = GetFunction(sqlite3DLL, "sqlite3_aggregate_context")
    sqlite3_aggregate_count = GetFunction(sqlite3DLL, "sqlite3_aggregate_count")
    sqlite3_bind_blob = GetFunction(sqlite3DLL, "sqlite3_bind_blob")
    sqlite3_bind_double = GetFunction(sqlite3DLL, "sqlite3_bind_double")
    sqlite3_bind_int = GetFunction(sqlite3DLL, "sqlite3_bind_int")
    sqlite3_bind_int64 = GetFunction(sqlite3DLL, "sqlite3_bind_int64")
    sqlite3_bind_null = GetFunction(sqlite3DLL, "sqlite3_bind_null")
    sqlite3_bind_parameter_count = GetFunction(sqlite3DLL, "")
    sqlite3_bind_parameter_index = GetFunction(sqlite3DLL, "sqlite3_bind_parameter_index")
    sqlite3_bind_parameter_name = GetFunction(sqlite3DLL, "sqlite3_bind_parameter_name")
    sqlite3_bind_text = GetFunction(sqlite3DLL, "sqlite3_bind_text")
    sqlite3_bind_text16 = GetFunction(sqlite3DLL, "sqlite3_bind_text16")
    sqlite3_busy_handler = GetFunction(sqlite3DLL, "sqlite3_busy_handler")
    sqlite3_busy_timeout = GetFunction(sqlite3DLL, "sqlite3_busy_timeout")
    sqlite3_changes = GetFunction(sqlite3DLL, "sqlite3_changes")
    sqlite3_close = GetFunction(sqlite3DLL, "sqlite3_close")
    sqlite3_collation_needed = GetFunction(sqlite3DLL, "sqlite3_collation_needed")
    sqlite3_collation_needed16 = GetFunction(sqlite3DLL, "sqlite3_collation_needed16")
    sqlite3_column_blob = GetFunction(sqlite3DLL, "sqlite3_column_blob")
    sqlite3_column_bytes = GetFunction(sqlite3DLL, "sqlite3_column_bytes")
    sqlite3_column_bytes16 = GetFunction(sqlite3DLL, "sqlite3_column_bytes16")
    sqlite3_column_count = GetFunction(sqlite3DLL, "sqlite3_column_count")
    sqlite3_column_decltype = GetFunction(sqlite3DLL, "sqlite3_column_decltype")
    sqlite3_column_decltype16 = GetFunction(sqlite3DLL, "sqlite3_column_decltype16")
    sqlite3_column_double = GetFunction(sqlite3DLL, "sqlite3_column_double")
    sqlite3_column_int = GetFunction(sqlite3DLL, "sqlite3_column_int")
    sqlite3_column_int64 = GetFunction(sqlite3DLL, "sqlite3_column_int64")
    sqlite3_column_name = GetFunction(sqlite3DLL, "sqlite3_column_name")
    sqlite3_column_name16 = GetFunction(sqlite3DLL, "sqlite3_column_name16")
    sqlite3_column_text = GetFunction(sqlite3DLL, "sqlite3_column_text")
    sqlite3_column_text16 = GetFunction(sqlite3DLL, "sqlite3_column_text16")
    sqlite3_column_type = GetFunction(sqlite3DLL, "sqlite3_column_type")
    sqlite3_commit_hook = GetFunction(sqlite3DLL, "sqlite3_commit_hook")
    sqlite3_complete = GetFunction(sqlite3DLL, "sqlite3_complete")
    sqlite3_complete16 = GetFunction(sqlite3DLL, "sqlite3_complete16")
    sqlite3_create_collation = GetFunction(sqlite3DLL, "sqlite3_create_collation")
    sqlite3_create_collation16 = GetFunction(sqlite3DLL, "sqlite3_create_collation16")
    sqlite3_create_function = GetFunction(sqlite3DLL, "sqlite3_create_function")
    sqlite3_create_function16 = GetFunction(sqlite3DLL, "sqlite3_create_function16")
    sqlite3_data_count = GetFunction(sqlite3DLL, "sqlite3_data_count")
    sqlite3_errcode = GetFunction(sqlite3DLL, "sqlite3_errcode")
    sqlite3_errmsg = GetFunction(sqlite3DLL, "sqlite3_errmsg")
    sqlite3_errmsg16 = GetFunction(sqlite3DLL, "sqlite3_errmsg16")
    sqlite3_exec = GetFunction(sqlite3DLL, "sqlite3_exec")
    sqlite3_finalize = GetFunction(sqlite3DLL, "sqlite3_finalize")
    sqlite3_free = GetFunction(sqlite3DLL, "sqlite3_free")
    sqlite3_free_table = GetFunction(sqlite3DLL, "sqlite3_free_table")
    sqlite3_get_auxdata = GetFunction(sqlite3DLL, "sqlite3_get_auxdata")
    sqlite3_get_table = GetFunction(sqlite3DLL, "sqlite3_get_table")
    sqlite3_interrupt = GetFunction(sqlite3DLL, "sqlite3_interrupt")
    sqlite3_last_insert_rowid = GetFunction(sqlite3DLL, "sqlite3_last_insert_rowid")
    sqlite3_libversion = GetFunction(sqlite3DLL, "sqlite3_libversion")
    sqlite3_mprintf = GetFunction(sqlite3DLL, "sqlite3_mprintf")
    sqlite3_open = GetFunction(sqlite3DLL, "sqlite3_open")
    sqlite3_open16 = GetFunction(sqlite3DLL, "sqlite3_open16")
    sqlite3_prepare = GetFunction(sqlite3DLL, "sqlite3_prepare")
    sqlite3_prepare16 = GetFunction(sqlite3DLL, "sqlite3_prepare16")
    sqlite3_progress_handler = GetFunction(sqlite3DLL, "sqlite3_progress_handler")
    sqlite3_reset = GetFunction(sqlite3DLL, "sqlite3_reset")
    sqlite3_result_blob = GetFunction(sqlite3DLL, "sqlite3_result_blob")
    sqlite3_result_double = GetFunction(sqlite3DLL, "sqlite3_result_double")
    sqlite3_result_error = GetFunction(sqlite3DLL, "sqlite3_result_error")
    sqlite3_result_error16 = GetFunction(sqlite3DLL, "sqlite3_result_error16")
    sqlite3_result_int = GetFunction(sqlite3DLL, "sqlite3_result_int")
    sqlite3_result_int64 = GetFunction(sqlite3DLL, "sqlite3_result_int64")
    sqlite3_result_null = GetFunction(sqlite3DLL, "sqlite3_result_null")
    sqlite3_result_text = GetFunction(sqlite3DLL, "sqlite3_result_text")
    sqlite3_result_text16 = GetFunction(sqlite3DLL, "sqlite3_result_text16")
    sqlite3_result_text16be = GetFunction(sqlite3DLL, "sqlite3_result_text16be")
    sqlite3_result_text16le = GetFunction(sqlite3DLL, "sqlite3_result_text16le")
    sqlite3_result_value = GetFunction(sqlite3DLL, "sqlite3_result_value")
    sqlite3_set_authorizer = GetFunction(sqlite3DLL, "sqlite3_set_authorizer")
    sqlite3_set_auxdata = GetFunction(sqlite3DLL, "sqlite3_set_auxdata")
    sqlite3_snprintf = GetFunction(sqlite3DLL, "sqlite3_snprintf")
    sqlite3_step = GetFunction(sqlite3DLL, "sqlite3_step")
    sqlite3_total_changes = GetFunction(sqlite3DLL, "sqlite3_total_changes")
    sqlite3_trace = GetFunction(sqlite3DLL, "sqlite3_trace")
    sqlite3_user_data = GetFunction(sqlite3DLL, "sqlite3_user_data")
    sqlite3_value_blob = GetFunction(sqlite3DLL, "sqlite3_value_blob")
    sqlite3_value_bytes = GetFunction(sqlite3DLL, "sqlite3_value_bytes")
    sqlite3_value_bytes16 = GetFunction(sqlite3DLL, "sqlite3_value_bytes16")
    sqlite3_value_double = GetFunction(sqlite3DLL, "sqlite3_value_double")
    sqlite3_value_int = GetFunction(sqlite3DLL, "sqlite3_value_int")
    sqlite3_value_int64 = GetFunction(sqlite3DLL, "sqlite3_value_int64")
    sqlite3_value_text = GetFunction(sqlite3DLL, "sqlite3_value_text")
    sqlite3_value_text16 = GetFunction(sqlite3DLL, "sqlite3_value_text16")
    sqlite3_value_text16be = GetFunction(sqlite3DLL, "sqlite3_value_text16be")
    sqlite3_value_text16le = GetFunction(sqlite3DLL, "sqlite3_value_text16le")
    sqlite3_value_type = GetFunction(sqlite3DLL, "sqlite3_value_type")
    sqlite3_vmprintf = GetFunction(sqlite3DLL, "sqlite3_vmprintf")
    
    If sqlite3_get_table = 0 
      LastMessage = "Couldn't init 'sqlite3_get_table'"
      ProcedureReturn #False
    EndIf
    
    If sqlite3_free_table = 0 
      LastMessage = "Couldn't init 'sqlite3_free_table'"
      ProcedureReturn #False
    EndIf
    
    If sqlite3_open = 0 
      LastMessage = "Couldn't init 'sqlite3_open'"
      ProcedureReturn #False
    EndIf
    
    If sqlite3_close = 0 
      LastMessage = "Couldn't init 'sqlite3_close'"
      ProcedureReturn #False
    EndIf
    
    If sqlite3_exec = 0 
      LastMessage = "Couldn't init 'sqlite3_exec'"
      ProcedureReturn #False
    EndIf
    
    ProcedureReturn #True
  Else
    LastMessage = "General Error - Couldn't open '"+sSQLiteDLLPath+"'" 
    ProcedureReturn #False
  EndIf
  
EndProcedure

ProcedureDLL.l SQLite3_CreateDatabase(sDataBase.s, bolOverwrite.l)
  
  If FileSize(sDataBase) = -1  ; DB noch nicht vorhanden 
    
    ; Erstellen
    
    hResult = CallCFunctionFast(sqlite3_open, sDataBase, @sqlite3_dbHandle)
    
    If hResult = #SQLITE3_OK
      LastMessage = "OK"
      ProcedureReturn sqlite3_dbHandle
    EndIf
    
  Else
    
    If bolOverwrite = 1 ; DB vorhanden, aber überschreiben
      
      ; Überschreiben
      
      DeleteFile(sDataBase)
      
      hResult = CallCFunctionFast(sqlite3_open, sDataBase, @sqlite3_dbHandle)
      
      If hResult = #SQLITE3_OK
        LastMessage = "OK"
        ProcedureReturn sqlite3_dbHandle
      EndIf
      
    EndIf
    
  EndIf
  
  ProcedureReturn 0
  
EndProcedure

ProcedureDLL.l SQLite3_OpenDatabase(sDataBase.s)
  
  If FileSize(sDataBase) > 0
    
    hResult = CallCFunctionFast(sqlite3_open, sDataBase, @sqlite3_dbHandle)
    
    If hResult = #SQLITE3_OK
      ProcedureReturn sqlite3_dbHandle
    EndIf
    
  EndIf
  
  ProcedureReturn 0
  
EndProcedure

ProcedureDLL.l SQLite3_CloseDatabase(lDataBaseHandle.l)
  
  If lDataBaseHandle = 0
    LastMessage = "Wrong Database-Handle"
    ProcedureReturn
  EndIf

  hResult = CallCFunctionFast(sqlite3_close, lDataBaseHandle)
  
  If hResult = #SQLITE3_OK
    LastMessage = "OK"
    ProcedureReturn #True
  Else
    LastMessage = "Couldn't close Database"
    ProcedureReturn #False
  EndIf
  
EndProcedure

ProcedureDLL.l SQLite3_Execute(sSQLQuery.s, lDataBaseHandle.l)
  
  If lDataBaseHandle = 0
    LastMessage = "Wrong Database-Handle"
    ProcedureReturn
  EndIf

  hResult = CallCFunctionFast(sqlite3_exec, lDataBaseHandle, sSQLQuery, #Null, #Null, @ReturnValue)
  
  If hResult = #SQLITE3_OK
    LastMessage = "OK"
    ProcedureReturn #True
  Else
    CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
    LastMessage = PeekS(ReturnValue)
    ProcedureReturn #False
  EndIf
  
EndProcedure 

; ProcedureDLL.l SQLite3_GetTable(sSQLQuery.s, *Array, *Rows, *Cols, lDataBaseHandle.l)
  ; 
  ; If lDataBaseHandle = 0
    ; LastMessage = "Wrong Database-Handle"
    ; ProcedureReturn
  ; EndIf
; 
  ; hResult = CallCFunctionFast(sqlite3_get_table, lDataBaseHandle, sSQLQuery, @lResultsPtr, @lRows, @lCols, @ReturnValue)
  ; 
  ; If hResult = #SQLITE3_OK
    ; 
    ; ; return number of rows/columns
    ; PokeL(*Rows, lRows)
    ; PokeL(*Cols, lCols)
    ; 
    ; If lRows > -1 And lCols > 0
      ; 
      ; ; redimension results array (clears data)
      ; Dim DBData.s(lRows, lCols - 1)
      ; 
      ; ; copy data into array
      ; Address.l  = lResultsPtr
      ; 
      ; For Row.l  = 0 To lRows
        ; For Col.l  = 0 To lCols - 1
          ; DBData(Row, Col) = PeekS(PeekL(Address + Col  * 4))
        ; Next
        ; Address + lCols * 4
      ; Next
      ; 
    ; EndIf
    ; 
    ; ; free table memory
    ; CallCFunctionFast(sqlite3_free_table, lResultsPtr)
    ; 
    ; LastMessage = "OK"
    ; ProcedureReturn #True
    ; 
  ; Else
    ; 
    ; CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
    ; LastMessage = PeekS(ReturnValue)
    ; ProcedureReturn #False
    ; 
  ; EndIf
  ; 
; EndProcedure 
; 
ProcedureDLL.l SQLite3_GetTable(sSQLQuery.s, lDataBaseHandle.l, *Recordset.s_RecordSet)
  
  If lDataBaseHandle = 0
    LastMessage = "Invalid Database Handle"
    ProcedureReturn
  EndIf
  
  *Recordset\Handle = 0

  hResult = CallCFunctionFast(sqlite3_get_table, lDataBaseHandle, sSQLQuery, @lResultsPtr, @lRows, @lCols, @ReturnValue)
  
  If hResult = #SQLITE3_OK
    
    *Recordset\Handle = lResultsPtr
    *Recordset\Rows = lRows
    *Recordset\Cols = lCols
    *Recordset\CurrentPos = 1
    
    LastMessage = "OK"

    ProcedureReturn @Recordset
    
  Else
    
    CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
    LastMessage = PeekS(ReturnValue)
    ProcedureReturn #False
    
  EndIf
  
EndProcedure 

ProcedureDLL.l SQLite3_GetFieldIndexByName(sFieldname.s, *Recordset.s_RecordSet)
  
  If *Recordset\Handle = 0
    LastMessage = "Wrong Recordset-Handle"
    ProcedureReturn
  EndIf
  
  If *Recordset\Handle <> 0
  
    Address.l  = *Recordset\Handle
    
    ; Search named Column
    For Col.l  = 0 To *Recordset\Cols - 1
      If sFieldname = PeekS(PeekL(Address + Col  * 4))
        LastMessage = "OK"
        ProcedureReturn Col
      EndIf
    Next

  EndIf
  
  LastMessage = "No such Columnname: '" + sFieldname + "'"
  ProcedureReturn -1
  
EndProcedure

ProcedureDLL.l SQLite3_GetRowCol(new_row.l, lIndex.l, *Recordset.s_RecordSet)
  
  If *Recordset\Handle = 0
    LastMessage = "Invalid recordset handle"
    ProcedureReturn
  EndIf

  If lIndex < 0 Or lIndex > *Recordset\Cols
    LastMessage = "No such column index: '" + Str(lIndex) + "'"
    *Recordset\sValue = ""
    ProcedureReturn #False
  EndIf
  
  If new_row > *Recordset\Rows Or new_row < 1
    LastMessage = "Invalid row specified"
    ProcedureReturn #False
  EndIf
  
    *Recordset\BOF = #False
    *Recordset\EOF = #False

    LastMessage = "OK"
    *Recordset\CurrentPos = new_row
    *Recordset\sValue = PeekS(PeekL(*Recordset\Handle + (lIndex * 4) + (*Recordset\CurrentPos * *Recordset\Cols) * 4))
  
  If *Recordset\CurrentPos = *Recordset\Rows
    *Recordset\EOF = #True
  EndIf
  
  If *Recordset\CurrentPos = 1
    *Recordset\BOF = #True
  EndIf

    ProcedureReturn #True
    
  ;EndIf
  
EndProcedure 

ProcedureDLL.l SQLite3_GetDataByName(sFieldname.s, *Recordset.s_RecordSet)
  
  If *Recordset\Handle = 0
    LastMessage = "Invalid recordset handle"
    ProcedureReturn
  EndIf

  myCol = SQLite3_GetFieldIndexByName(sFieldname, *Recordset)
  
  If myCol > -1
    
    LastMessage = "OK"
    *Recordset\sValue = PeekS(PeekL(*Recordset\Handle + (myCol * 4) + (*Recordset\CurrentPos * *Recordset\Cols) * 4))
    ProcedureReturn #True
    
  Else
    
    LastMessage = "No such Column name: '" + sFieldname + "'"
    *Recordset\sValue = ""
    ProcedureReturn #False
    
  EndIf
  
EndProcedure 

ProcedureDLL.l SQLite3_GetDataByIndex(lIndex.l, *Recordset.s_RecordSet)
  
  If *Recordset\Handle = 0
    LastMessage = "Invalid recordset handle"
    ProcedureReturn
  EndIf

  If lIndex < 0 Or lIndex > *Recordset\Cols
    
    LastMessage = "No such column index: '" + Str(lIndex) + "'"
    *Recordset\sValue = ""
    ProcedureReturn #False
    
  Else
    
    LastMessage = "OK"
    *Recordset\sValue = PeekS(PeekL(*Recordset\Handle + (lIndex * 4) + (*Recordset\CurrentPos * *Recordset\Cols) * 4))
    ProcedureReturn #True
    
  EndIf
  
EndProcedure 

ProcedureDLL.l SQLite3_ReleaseTable(*Recordset.s_RecordSet)
  If *Recordset\Handle <> 0
    CallCFunctionFast(sqlite3_free_table, *Recordset\Handle)
    *Recordset\Handle =0
  EndIf
  LastMessage = "OK"
EndProcedure

ProcedureDLL.l SQLite3_NextRow(*Recordset.s_RecordSet)
  
  If *Recordset\Handle = 0
    LastMessage = "Invalid Recordset Handle"
    ProcedureReturn
  EndIf

  *Recordset\BOF = #False
  *Recordset\EOF = #False
  
  *Recordset\CurrentPos = *Recordset\CurrentPos + 1
  
  If *Recordset\CurrentPos > *Recordset\Rows
    *Recordset\CurrentPos = *Recordset\Rows
    *Recordset\EOF = #True
  EndIf
  
  If *Recordset\CurrentPos = 1
    *Recordset\BOF = #True
  EndIf

EndProcedure
  
ProcedureDLL.l SQLite3_PrevRow(*Recordset.s_RecordSet)
  
  If *Recordset\Handle = 0
    LastMessage = "Invalid Recordset Handle"
    ProcedureReturn
  EndIf

  *Recordset\BOF = #False
  *Recordset\EOF = #False
  
  *Recordset\CurrentPos = *Recordset\CurrentPos - 1
  
  If *Recordset\CurrentPos < 1
    *Recordset\CurrentPos = 1
    *Recordset\BOF = #True
  EndIf
  
  If *Recordset\CurrentPos = *Recordset\Rows
    *Recordset\EOF = #True
  EndIf

EndProcedure

ProcedureDLL.l SQLite3_FirstRow(*Recordset.s_RecordSet)
  
  If *Recordset\Handle = 0
    LastMessage = "Wrong Recordset-Handle"
    ProcedureReturn
  EndIf

  *Recordset\BOF = #True
  *Recordset\EOF = #False
  *Recordset\CurrentPos = 1 
  
  If *Recordset\CurrentPos = *Recordset\Rows
    *Recordset\EOF = #True
  EndIf
  
EndProcedure

ProcedureDLL.l SQLite3_LastRow(*Recordset.s_RecordSet)
  
  If *Recordset\Handle = 0
    LastMessage = "Wrong Recordset-Handle"
    ProcedureReturn
  EndIf

  *Recordset\BOF = #False
  *Recordset\EOF = #True
  *Recordset\CurrentPos = *Recordset\Rows
  
  If *Recordset\CurrentPos = 1
    *Recordset\BOF = #True
  EndIf
  
EndProcedure

ProcedureDLL.l SQLite3_Row(new_row.l, *Recordset.s_RecordSet)
  
  If *Recordset\Handle = 0
    LastMessage = "Invalid recordset handle"
    ProcedureReturn
  EndIf

  If new_row > *Recordset\Rows Or new_row < 1
    LastMessage = "Invalid row specified"
    ProcedureReturn
  EndIf
  
  *Recordset\CurrentPos = new_row
  *Recordset\BOF = #False
  *Recordset\EOF = #False
  
  If *Recordset\CurrentPos = *Recordset\Rows
    *Recordset\EOF = #True
  EndIf
  
  If *Recordset\CurrentPos = 1
    *Recordset\BOF = #True
  EndIf

EndProcedure
  
ProcedureDLL.s SQLite3_GetLastMessage()
  ProcedureReturn LastMessage
EndProcedure

ProcedureDLL.s SQLite3_GetLibVersion()
  ProcedureReturn "SQLite3 Userlibrary V1.01 (27.05.2005)"
EndProcedure
sqlite3_toy.pb

Code: Select all

;sqlite3_toy.pb
;created 4/10/2006 by chris319
;builds upon kiffi's fine work with sqlite3
;runs on purebasic 4.0+ for windows
;requires sqlite3.pb and sqlite3.dll
;sqlite3.dll can be obtained from http://www.sqlite.org/download.html


;MUST INCLUDE THIS PUREBASIC FILE TO MAKE IT ALL WORK
IncludeFile "sqlite3.pb"

;THIS STRUCTURE IS ALREADY CONTAINED IN sqlite3.pb
;Structure s_RecordSet
;  BOF.l
;  EOF.l
;  Handle.l
;  Rows.l
;  Cols.l
;  CurrentPos.l
;  sValue.s
;EndStructure

Define.s_RecordSet RS ;Declare RecordSet object

#program_name = "sqlite3_test.pb" ;TITLE OF MESSAGE REQUESTERS

cmd.s ;SQLITE3 COMMAND STRING
rows.l ;ROW COUNT

Dim fruit.s(5)
fruit.s(1) = "Apple"
fruit.s(2) = "Orange"
fruit.s(3) = "Hammer"
fruit.s(4) = "Cherry"
fruit.s(5) = "Lemon"

Dim flavor.s(5)
flavor.s(1) = "Sweet"
flavor.s(2) = "Tart"
flavor.s(3) = "Hard"
flavor.s(4) = "Sweet"
flavor.s(5) = "Sour"

OpenConsole()

;INITIALIZE sqlite3.dll
If SQLite3_InitLib("sqlite3.dll") = #False
  MessageRequester(#program_name, SQLite3_GetLastMessage())
  End
Else
  PrintN("SQLite3 library initialized.")
EndIf

;OBTAIN A HANDLE TO THE DATABASE
dbHandle = SQLite3_CreateDatabase("sample.db", 1)
If dbHandle = 0
  MessageRequester(#program_name, SQLite3_GetLastMessage())
  End
Else
  PrintN("Connected to database.")
EndIf

;CREATE A TABLE AND POPULATE IT WITH DATA
cmd = "Create Table TempData (fruit char(8) , flavor char(8) , id_num)"
SQLite3_Execute(cmd, dbHandle)

;TRANSACTIONS ACCELERATE THE INSERTION OF MULTIPLE RECORDS
;Program will work OK but is slower without it
SQLite3_Execute("BEGIN TRANSACTION", dbHandle)

For intI = 1 To 5
  cmd = "Insert Into TempData (fruit, flavor, id_num) Values ('"+fruit(intI)+"' , '"+flavor(intI)+"' , '"+Str(intI)+"')"
  SQLite3_Execute(cmd, dbHandle)
Next intI

;COMMIT TRANSACTION
;Program will work OK but is slower without it
SQLite3_Execute("COMMIT", dbHandle)

;DETERMINE NUMBER OF ROWS
If SQLite3_GetTable("select * from TempData", dbHandle, @RS)
  PrintN("There are " + Str(RS\Rows) + " rows in the database." + Chr(10))
Else
  MessageRequester(#program_name, SQLite3_GetLastMessage())
EndIf

rows = RS\Rows

If SQLite3_GetTable("Select * From TempData Limit " + Str(rows), dbHandle, @RS)
  
  If RS\Handle 
    
    ;SQLite3_Row LETS US SELECT SPECIFIC ROWS
    For ct = 1 To rows
      SQLite3_Row(ct, @RS)
      If SQLite3_GetDataByName("fruit", @RS)
        PrintN(RS\sValue)
      Else
        MessageRequester(#program_name, SQLite3_GetLastMessage())
      EndIf
    Next

    PrintN("")
    
    ;LIST ROWS IN FORWARD ORDER
    SQLite3_FirstRow(@RS)
    For ct = 1 To rows
      If SQLite3_GetDataByName("flavor", @RS)
        PrintN(RS\sValue)
      Else
        MessageRequester(#program_name, SQLite3_GetLastMessage())
      EndIf
    SQLite3_NextRow(@RS)
    Next

    PrintN("")
     
    ;LIST ROWS BACKWARDS
    While RS\BOF = 0
      If SQLite3_GetDataByName("fruit", @RS)
        PrintN(RS\sValue)
      Else
        MessageRequester(#program_name, SQLite3_GetLastMessage())
      EndIf
      SQLite3_PrevRow(@RS)
    Wend
    PrintN("")
    
    ;JUMP TO THE FIRST RECORD
    SQLite3_FirstRow(@RS)
    
    If SQLite3_GetDataByName("flavor", @RS)
      Print("Field contents of field 'flavor' the first record (by name): ")
      PrintN(RS\sValue)
    Else
      MessageRequester(#program_name, SQLite3_GetLastMessage())
    EndIf
    
    If SQLite3_GetDataByIndex(0, @RS)
      Print("Field contents of field 'fruit' the first record (by index): ")
      PrintN(RS\sValue + "  ")
    Else
      MessageRequester(#program_name, SQLite3_GetLastMessage())
    EndIf
    
    ;JUMP TO THE LAST RECORD
    SQLite3_LastRow(@RS)
    If SQLite3_GetDataByName("fruit", @RS)
      Print("Field contents of field 'fruit' the last record: ")
      PrintN(RS\sValue)
    Else
      MessageRequester(#program_name, SQLite3_GetLastMessage())
    EndIf
    
  Else
    MessageRequester(#program_name, SQLite3_GetLastMessage())
  EndIf
   
Else
  MessageRequester(#program_name, SQLite3_GetLastMessage())
EndIf

SQLite3_GetTable("Select * From TempData Limit " + Str(rows), dbHandle, @RS)

;GetRowCol ALLOWS US TO ADDRESS ROWS AND COLUMNS DIRECTLY
;Rows are numbered 1 to n; columns are numbered 0 to n
PrintN(Chr(10) + "Row and column addressing")

For ct = 1 To rows
  SQLite3_GetRowCol(ct, 0, @RS): fr.s = (RS\sValue)
  SQLite3_GetRowCol(ct, 1, @RS): fl.s = (RS\sValue)
  SQLite3_GetRowCol(ct, 2, @RS): num.s = (RS\sValue)
  PrintN(num + "  " + fr + Chr(9) + fl)
Next

;RELEASE RECORDSET
SQLite3_ReleaseTable(@RS)

;CLOSE DATABASE
SQLite3_CloseDatabase(dbHandle)
  
Input()  
  
End
Top
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

Thanks chris319, I'll test this out as soon as I can.

In the meantime, thanks for posting; this will come in very handy.
I may look like a mule, but I'm not a complete ass.
Top
USCode
Addict
Addict
Posts: 923
Joined: Wed Mar 24, 2004 11:04 pm
Location: Seattle

Post by USCode »

Terrific! Thanks Chris!

Any thoughts on handling BLOBs?
Top
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4789
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Post by Fangbeast »

USCode wrote:Terrific! Thanks Chris!

Any thoughts on handling BLOBs?
Check out Kiffi's examples in handling blobs that he put into the PBOSL sqlite library and see if you can follow it. I got it to work, just was too complicated a solution for me and I went another way.

You can find a link to the PBOSL libraries at purearea.net
Amateur Radio/VK3HAF, (D-STAR/DMR and more), Arduino, ESP32, Coding, Crochet
Top
chris319
Enthusiast
Enthusiast
Posts: 782
Joined: Mon Oct 24, 2005 1:05 pm

Post by chris319 »

When I posted this code my purpose was to make Kiffi's code compatible with v4.0, to get the comments from German into English, and to make SQLite3 just functional enough to be usable in a project I'm working on. Right now I'm immersed in this other project and am by no means an expert on SQLite, so I've no immediate plans to go any further with this. If I do any more work on this I will share it here.
Top
ebs
Enthusiast
Enthusiast
Posts: 557
Joined: Fri Apr 25, 2003 11:08 pm

Post by ebs »

Chris,

I think I have found a bug in your SQLite3 wrapper code.
In the SQLite3_OpenDatabase() procedure, you test for file size > 0 before opening the database.
If SQLite3_CreateDatabase() is called to create a database, the file size will be 0, so it won't be opened correctly.

I changed the first line from:

Code: Select all

If FileSize(sDataBase) > 0
to

Code: Select all

If FileSize(sDataBase) >= 0
which seems to work.

Regards,
Eric
Top
Post Reply

Return to “Tricks 'n' Tips”