PureBasic Forums - English

Code: Select all

  lStatement = SQLitePrepare(DbHandle.l, "SELECT * FROM tableName WHERE record='" + Record.s + "'")
  If lStatement
    While SQLiteStep(lStatement) <> #False
      Debug SQLiteColumnText(lStatement,  0)
      Debug SQLiteColName(*Table.SQ3_TABLEMAP, 0)     ; Is this possible?
    Wend
    SQLiteFinalize(lStatement) = #False
  EndIf
  

Code: Select all

Procedure.s SQLiteColName(*Table.SQ3_TABLEMAP, Col.l)
  Protected *val.l
  If *Table
    If Col > 0 And Col <= *Table\Cols
      *val.l = PeekL(*Table\Table + ((Col - 1) * 4))
      If *val
        ProcedureReturn PeekS(*val, #PB_Any, SQ3\PeekMode)
      EndIf
    EndIf
  EndIf
  ProcedureReturn ""
EndProcedure

Code: Select all

; Any specific modules that have to load first

XIncludeFile "C:\Development\Resources\Modules\Ts_Soft\_SqLite3Base.pbi"                                         ; Prototype function declarations

; Initialise the SQLite3.x library and environment
 
SQLiteInit("C:\Development\Resources\Libraries\sqlite3upxl.dll")                                                   ; Load the external sqlite library

; Main program code

  Define.SQ3_TABLEMAP Table 

  DbHandle.l = SQLiteOpen("D:\testicle.db", #True)
  
  If DbHandle.l
    
    ;--------------------------------------------------------------------------
    ; Testing to see if I can get the number of columns first, then get the 
    ; of the columns,
    ;--------------------------------------------------------------------------
    
     Table.SQ3_TABLEMAP 
     
     SQLiteGetTable(DbHandle.l, "PRAGMA table_info(inventory)", Table)    ; Works properly
     
     ; Debug SQLiteCols(Table)         ; Rows down, not cols across!!!. This is backwards
     
     sqliteCols =  SQLiteRows(Table)   ; Columns across, not rows down!!!. This is backwards. Gets the correct count of cols now
     
    ;--------------------------------------------------------------------------
    ; Get the column names from the table, jsut to prove that I can. Didn't work
    ;--------------------------------------------------------------------------

     For ColNames = 0 To sqliteCols
     
      Debug SQLiteColName(Table, ColNames.l)    ; Returns column definitions, not column names???
      
     Next ColNames
     
    ;--------------------------------------------------------------------------
    ; Don't free the table just yet, need column name from it
    ;--------------------------------------------------------------------------

    ; SQliteFreeTable(Table) 

;**************************************************************************************************************

    ;--------------------------------------------------------------------------
    ; No problems with the below routine.
    ;--------------------------------------------------------------------------
        
    lStatement = SQLitePrepare(DbHandle.l, "SELECT * FROM inventory WHERE record=2")
    
    If lStatement
    
      While SQLiteStep(lStatement) <> #False
      
        For ColsAcross = 0 To sqliteCols
        
          ;--------------------------------------------------------------------------
          ; Returning the text in the columns
          ;--------------------------------------------------------------------------

          Debug SQLiteColumnText(lStatement,  ColsAcross)     ; Doing this
          
          ;--------------------------------------------------------------------------
          ; Want to return the column name with the column text
          ;--------------------------------------------------------------------------

          ; Debug SQLiteColName(*Table.SQ3_TABLEMAP, ColsAcross.l) + ": " + SQLiteColumnText(lStatement,  ColsAcross)
          
        Next ColsAcross
        
      Wend
      
      ;--------------------------------------------------------------------------
      ; Finalise the statement
      ;--------------------------------------------------------------------------

      If SQLiteFinalize(lStatement) = #False
      
        Debug  SQLiteErrorMsg(DbHandle.l)
        
      EndIf
      
    EndIf
    
  EndIf

  ;--------------------------------------------------------------------------
  ; Now free the table
  ;--------------------------------------------------------------------------

  SQliteFreeTable(Table) ; Now free table, got everything I wanted

End

Code: Select all

; SQLite3 Include for Windows, Linux and MacOS
; based on a example by MLK
; binding functions based on code by Kiffi (PBOSL)
; tested with Ansi and Unicode under WinXP and XUbuntu
; Autor: ts-soft
; new Version For PB 4.30 + using static lib from pb
; supports 64-Bit

Enumeration
  #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

  #SQLITE3_STATIC      =   0
  #SQLITE_TRANSIENT    =  -1

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

UseSQLiteDatabase()

ImportC ""
  sqlite3_close (hDB.i)
  sqlite3_errcode (hDB.i)
  sqlite3_errmsg (hDB.i)
  sqlite3_errmsg16 (hDB.i)
  sqlite3_exec (hDB.i, Statement.p-utf8, cb.i = 0, cbdata.i = 0, errmsg.i = 0)
  sqlite3_free_table (*table)
  sqlite3_get_table (hDB.i, Statement.p-utf8, *table, nRow.i, nColumn.i, errmsg.i = 0)
  sqlite3_libversion ()
  sqlite3_open (filename.s, *hDB)
  sqlite3_open16 (filename.s, *hDB)
  sqlite3_last_insert_rowid (hDB.i)
  sqlite3_prepare (hDB.i, Statement.p-utf8, nBytes.i, *hResult, *pzTail = 0)
  sqlite3_prepare16 (hDB.i, Statement.p-unicode, nBytes.i, *hResult, *pzTail = 0)
  sqlite3_finalize (lStatement.i)
  sqlite3_reset(lStatement.i)
  sqlite3_step (lStatement.i)
  sqlite3_bind_blob (lStatement.i, lIndex.i, lBinaryBuffer.i, lBinaryBufferLength.i, *void = -1)
  sqlite3_bind_double (lStatement.i, lIndex.i, dValue.d)
  sqlite3_bind_int (lStatement.i, lIndex.i, lValue.i)
  sqlite3_bind_int64 (lStatement.i, lIndex.i, qValue.q)
  sqlite3_bind_text (lStatement.i, lIndex.i, lTextBuffer.p-ascii, len.i = -1, type.i = #SQLITE_TRANSIENT)
  sqlite3_bind_text16 (lStatement.i, lIndex.i, lTextBuffer.p-unicode, len.i = -1, type.i = #SQLITE_TRANSIENT)
  sqlite3_column (lStatement.i, lCol.i)
  sqlite3_column_double (lStatement.i, lCol.i)
  sqlite3_column_quad (lStatement.i, lCol.i)
  sqlite3_column_blob (lStatement.i, lCol.i)
  sqlite3_column_bytes (lStatement.i, lCol.i)
  sqlite3_column_bytes16 (lStatement.i, lCol.i)
  sqlite3_column_int (lStatement.i, lCol.i)
  sqlite3_column_int64 (lStatement.i, lCol.i)
  sqlite3_column_text (lStatement.i, lCol.i)
  sqlite3_column_text16 (lStatement.i, lCol.i)
  sqlite3_column_type (lStatement.i, lCol.i)
EndImport

Structure SQ3_TABLEMAP
  Table.i
  Rows.i
  Cols.i
  RowPos.i
  ColPos.i
EndStructure

Procedure.i SQLiteClose(hDB.i)
  If Not hDB : ProcedureReturn #False : EndIf
  If Not sqlite3_close(hDB)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteOpen(FileName.s)
  Protected hDB.i
  CompilerIf #PB_Compiler_Unicode
  If Not sqlite3_open16(FileName, @hDB)
    ProcedureReturn hDB
  EndIf
  CompilerElse
  If Not sqlite3_open(FileName, @hDB)
    ProcedureReturn hDB
  EndIf
  CompilerEndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteErrorCode(hDB.i)
  If Not hDB : ProcedureReturn -1 : EndIf
  ProcedureReturn sqlite3_errcode(hDB)
EndProcedure

Procedure.s SQLiteErrorMsg(hDB.i)
  If Not hDB : ProcedureReturn "Database handle missing" : EndIf
  CompilerIf #PB_Compiler_Unicode
  ProcedureReturn PeekS(sqlite3_errmsg16(hDB))
  CompilerElse
  ProcedureReturn PeekS(sqlite3_errmsg(hDB))
  CompilerEndIf
EndProcedure


Procedure.s SQLiteLibversion()
  ProcedureReturn PeekS(sqlite3_libversion(), #PB_Any, #PB_Ascii)
EndProcedure

Procedure.i SQLiteExecute(hDB.i, Statement.s, callback.i = 0, cbpara.i = 0)
  If Not hDB : ProcedureReturn #False : EndIf
  If Not sqlite3_exec(hDB, Statement, callback, cbpara)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteLastInsertRowId(hDB.i)
  If Not hDB : ProcedureReturn #False : EndIf
  ProcedureReturn sqlite3_last_insert_rowid(hDB)
EndProcedure

; table-functions
Procedure.i SQliteFreeTable(*table.SQ3_TABLEMAP)
  If *table
    sqlite3_free_table(*table\Table)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteGetTable(hDB.i, Statement.s, *table.SQ3_TABLEMAP)
  Protected nRow.i, nColumn.i, lResultPtr.i, result.i
  If Not hDB : ProcedureReturn #False : EndIf
  result = sqlite3_get_table(hDB, Statement, @lResultPtr, @nRow, @nColumn)
  If Not result
    *table\Table = lResultPtr
    *table\Rows = nRow
    *table\Cols = nColumn
    *table\RowPos = 0
    *table\ColPos = 0
    ProcedureReturn *table
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteNextRow(*Table.SQ3_TABLEMAP)
  If *Table
    If *Table\RowPos < *Table\Rows
      *Table\RowPos + 1
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteNextCol(*Table.SQ3_TABLEMAP)
  If *Table
    If *Table\ColPos < *Table\Cols
      *Table\ColPos + 1
      ProcedureReturn #True
    Else
      *Table\ColPos = 0
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteSelectRow(*Table.SQ3_TABLEMAP, Row.i)
  If *Table
    If Row > 0 And Row <= *Table\Rows
      *Table\RowPos = Row
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteSelectCol(*Table.SQ3_TABLEMAP, Col.i)
  If *Table
    If Col > 0 And Col <= *Table\Cols
      *Table\ColPos = Col
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteSelectPos(*Table.SQ3_TABLEMAP, Row.i, Col.i)
  If *Table
    If Row > 0 And Row <= *Table\Rows And Col > 0 And Col <= *Table\Cols
      *Table\RowPos = Row
      *Table\ColPos = Col
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteResetPos(*Table.SQ3_TABLEMAP)
  If *Table
    *Table\RowPos = 0
    *Table\ColPos = 0
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.s SQLiteColName(*Table.SQ3_TABLEMAP, Col.i)
  Protected *val
  If *Table
    If Col > 0 And Col <= *Table\Cols
      *val = PeekI(*Table\Table + ((Col - 1) * SizeOf(Integer)))
      If *val
        ProcedureReturn PeekS(*val, #PB_Any, #PB_UTF8)
      EndIf
    EndIf
  EndIf
  ProcedureReturn ""
EndProcedure

Procedure.s SQLiteValue(*Table.SQ3_TABLEMAP)
  Protected *val
  If *Table
    If *Table\RowPos > 0 And *Table\RowPos <= *Table\Rows And *Table\ColPos > 0 And *Table\ColPos <= *Table\Cols
      *val = PeekI(*Table\Table + ((*Table\ColPos - 1 + (*Table\RowPos * *Table\Cols)) * SizeOf(Integer)))
      If *val
        ProcedureReturn PeekS(*val, #PB_Any, #PB_UTF8)
      EndIf
    EndIf
  EndIf
  ProcedureReturn ""
EndProcedure

Procedure.s SQLiteRowValue(*Table.SQ3_TABLEMAP, Row.i)
  Protected *val
  If *Table
    If Row > 0 And Row <= *Table\Rows And *Table\Cols > 0 And *Table\Cols <= *Table\Cols
      *val = PeekI(*Table\Table + ((*Table\Cols - 1 + (Row * *Table\Cols)) * SizeOf(Integer)))
      If *val
        ProcedureReturn PeekS(*val, #PB_Any, #PB_UTF8)
      EndIf
    EndIf
  EndIf
  ProcedureReturn ""
EndProcedure

Procedure.s SQLiteColValue(*Table.SQ3_TABLEMAP, Col.i)
  Protected *val
  If *Table
    If *Table\RowPos > 0 And *Table\RowPos <= *Table\Rows And Col > 0 And Col <= *Table\Cols
      *val = PeekI(*Table\Table + ((Col - 1 + (*Table\RowPos * *Table\Cols)) * SizeOf(Integer)))
      If *val
        ProcedureReturn PeekS(*val, #PB_Any, #PB_UTF8)
      EndIf
    EndIf
  EndIf
  ProcedureReturn ""
EndProcedure

Procedure.s SQLiteSelectValue(*Table.SQ3_TABLEMAP, Row.i, Col.i)
  Protected *val
  If *Table
    If Row > 0 And Row <= *Table\Rows And Col > 0 And Col <= *Table\Cols
      *val = PeekI(*Table\Table + ((Col - 1 + (Row * *Table\Cols)) * SizeOf(Integer)))
      If *val
        ProcedureReturn PeekS(*val, #PB_Any, #PB_UTF8)
      EndIf
    EndIf
  EndIf
  ProcedureReturn ""
EndProcedure

Procedure.i SQLiteRows(*Table.SQ3_TABLEMAP)
  If *Table
    ProcedureReturn *Table\Rows
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteCols(*Table.SQ3_TABLEMAP)
  If *Table
    ProcedureReturn *Table\Cols
  EndIf
  ProcedureReturn #False
EndProcedure

; binding-functions
Procedure.i SQLitePrepare(hDB.i, Statement.s); Result is lStatement
  Protected Result.i
  CompilerIf #PB_Compiler_Unicode
  If Not sqlite3_prepare16(hDB, Statement, -1, @Result)
    ProcedureReturn Result
  EndIf
  CompilerElse
  If Not sqlite3_prepare(hDB, Statement, -1, @Result)
    ProcedureReturn Result
  EndIf
  CompilerEndIf
EndProcedure

Procedure.i SQLiteFinalize(lStatement.i)
  If Not sqlite3_finalize(lStatement)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteReset(lStatement.i)
  If Not sqlite3_reset(lStatement)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteStep(lStatement.i)
  If sqlite3_step(lStatement) = #SQLITE3_ROW
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteBindBlob(lStatement.i, lIndex.i, lBuffer.i, lBufferLength.i)
  If Not sqlite3_bind_blob(lStatement, lIndex, lBuffer, lBufferLength)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteBindDouble(lStatement.i, lIndex.i, dValue.d)
  If Not sqlite3_bind_double(lStatement, lIndex, dValue)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteBindLong(lStatement.i, lIndex.i, lValue.i)
  If Not sqlite3_bind_int(lStatement, lIndex, lValue)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteBindQuad(lStatement.i, lIndex.i, qValue.q)
  If Not sqlite3_bind_int64(lStatement, lIndex, qValue)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteBindText(lStatement.i, lIndex.i, Text.s)
  CompilerIf #PB_Compiler_Unicode
  If Not sqlite3_bind_text16(lStatement, lIndex, Text)
    ProcedureReturn #True
  EndIf
  CompilerElse
  If Not sqlite3_bind_text(lStatement, lIndex, Text)
    ProcedureReturn #True
  EndIf
  CompilerEndIf
  ProcedureReturn #False
EndProcedure

Procedure.i SQLiteColumnBlob(lStatement.i, lColumn.i)
  ProcedureReturn sqlite3_column_blob(lStatement, lColumn)
EndProcedure

Procedure.i SQLiteColumnByte(lStatement.i, lColumn.i)
  CompilerIf #PB_Compiler_Unicode
  ProcedureReturn sqlite3_column_bytes16(lStatement, lColumn)
  CompilerElse
  ProcedureReturn sqlite3_column_bytes(lStatement, lColumn)
  CompilerEndIf
EndProcedure

Procedure.d SQLiteColumnDouble(lStatement.i, lColumn.i)
  ProcedureReturn sqlite3_column_double(lStatement, lColumn)
EndProcedure

Procedure.l SQLiteColumnLong(lStatement.i, lColumn.i)
  ProcedureReturn sqlite3_column_int(lStatement, lColumn)
EndProcedure

Procedure.q SQLiteColumnQuad(lStatement.i, lColumn.i)
  ProcedureReturn sqlite3_column_int64(lStatement, lColumn)
EndProcedure

Procedure.s SQLiteColumnText(lStatement.i, lColumn.i)
  Protected Result.i
  CompilerIf #PB_Compiler_Unicode
  Result = sqlite3_column_text16(lStatement, lColumn)
  If Result
    ProcedureReturn PeekS(Result)
  EndIf
  CompilerElse
  Result = sqlite3_column_text(lStatement, lColumn)
  If Result
    ProcedureReturn PeekS(Result)
  EndIf
  CompilerEndIf
EndProcedure 

Procedure.i SQLiteColumnType(lStatement.i, lColumn.i)
  ProcedureReturn sqlite3_column_type(lStatement, lColumn)
EndProcedure

Code: Select all

; SQLite3 Include for Windows, Linux and MacOS
; based on a example by MLK
; binding functions based on code by Kiffi (PBOSL)
; tested with Ansi and Unicode under WinXP and XUbuntu
; Autor: ts-soft
; new Version For PB 4.30 + using static lib from pb
; supports 64-Bit

; New Version 2008/10/02
; since beta 3 you can mix native commands with API command, so i have make some changes and removed useless functions

Enumeration
  #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

  #SQLITE3_STATIC      =   0
  #SQLITE_TRANSIENT    =  -1

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

UseSQLiteDatabase()

ImportC ""
  sqlite3_close (hDB.i)
  sqlite3_errcode (hDB.i)
  sqlite3_errmsg (hDB.i)
  sqlite3_errmsg16 (hDB.i)
  sqlite3_exec (hDB.i, Statement.p-utf8, cb.i = 0, cbdata.i = 0, errmsg.i = 0)
  sqlite3_free_table (*table)
  sqlite3_get_table (hDB.i, Statement.p-utf8, *table, nRow.i, nColumn.i, errmsg.i = 0)
  sqlite3_libversion ()
  sqlite3_open (filename.s, *hDB)
  sqlite3_open16 (filename.s, *hDB)
  sqlite3_last_insert_rowid (hDB.i)
  sqlite3_prepare (hDB.i, Statement.p-utf8, nBytes.i, *hResult, *pzTail = 0)
  sqlite3_prepare16 (hDB.i, Statement.p-unicode, nBytes.i, *hResult, *pzTail = 0)
  sqlite3_finalize (lStatement.i)
  sqlite3_reset(lStatement.i)
  sqlite3_step (lStatement.i)
  sqlite3_bind_blob (lStatement.i, lIndex.i, lBinaryBuffer.i, lBinaryBufferLength.i, *void = -1)
  sqlite3_bind_double (lStatement.i, lIndex.i, dValue.d)
  sqlite3_bind_int (lStatement.i, lIndex.i, lValue.i)
  sqlite3_bind_int64 (lStatement.i, lIndex.i, qValue.q)
  sqlite3_bind_text (lStatement.i, lIndex.i, lTextBuffer.p-ascii, len.i = -1, type.i = #SQLITE_TRANSIENT)
  sqlite3_bind_text16 (lStatement.i, lIndex.i, lTextBuffer.p-unicode, len.i = -1, type.i = #SQLITE_TRANSIENT)
  sqlite3_column (lStatement.i, lCol.i)
  sqlite3_column_double (lStatement.i, lCol.i)
  sqlite3_column_quad (lStatement.i, lCol.i)
  sqlite3_column_blob (lStatement.i, lCol.i)
  sqlite3_column_bytes (lStatement.i, lCol.i)
  sqlite3_column_bytes16 (lStatement.i, lCol.i)
  sqlite3_column_int (lStatement.i, lCol.i)
  sqlite3_column_int64 (lStatement.i, lCol.i)
  sqlite3_column_text (lStatement.i, lCol.i)
  sqlite3_column_text16 (lStatement.i, lCol.i)
  sqlite3_column_type (lStatement.i, lCol.i)
EndImport

Procedure SQLiteErrorCode(Database)
  Protected hDB = DatabaseID(Database)
  If Not hDB : ProcedureReturn -1 : EndIf
  ProcedureReturn sqlite3_errcode(hDB)
EndProcedure

Procedure.s SQLiteLibversion()
  ProcedureReturn PeekS(sqlite3_libversion(), #PB_Any, #PB_Ascii)
EndProcedure

Procedure SQLiteExecute(Database, Statement.s, callback = 0, cbpara = 0)
  Protected hDB = DatabaseID(Database)
  If Not hDB : ProcedureReturn #False : EndIf
  If Not sqlite3_exec(hDB, Statement, callback, cbpara)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure SQLiteLastInsertRowId(Database)
  Protected hDB = DatabaseID(Database)
  If Not hDB : ProcedureReturn #False : EndIf
  ProcedureReturn sqlite3_last_insert_rowid(hDB)
EndProcedure

; binding-functions
Procedure SQLitePrepare(Database, Statement.s); Result is lStatement
  Protected hDB = DatabaseID(Database)
  Protected Result
  CompilerIf #PB_Compiler_Unicode
  If Not sqlite3_prepare16(hDB, Statement, -1, @Result)
    ProcedureReturn Result
  EndIf
  CompilerElse
  If Not sqlite3_prepare(hDB, Statement, -1, @Result)
    ProcedureReturn Result
  EndIf
  CompilerEndIf
EndProcedure

Procedure SQLiteFinalize(lStatement)
  If Not sqlite3_finalize(lStatement)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure SQLiteReset(lStatement)
  If Not sqlite3_reset(lStatement)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure SQLiteStep(lStatement)
  If sqlite3_step(lStatement) = #SQLITE3_ROW
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure SQLiteBindBlob(lStatement, lIndex, lBuffer, lBufferLength)
  If Not sqlite3_bind_blob(lStatement, lIndex, lBuffer, lBufferLength)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure SQLiteBindDouble(lStatement, lIndex, dValue.d)
  If Not sqlite3_bind_double(lStatement, lIndex, dValue)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure SQLiteBindLong(lStatement, lIndex, lValue)
  If Not sqlite3_bind_int(lStatement, lIndex, lValue)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure SQLiteBindQuad(lStatement, lIndex, qValue.q)
  If Not sqlite3_bind_int64(lStatement, lIndex, qValue)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure SQLiteBindText(lStatement, lIndex, Text.s)
  CompilerIf #PB_Compiler_Unicode
  If Not sqlite3_bind_text16(lStatement, lIndex, Text)
    ProcedureReturn #True
  EndIf
  CompilerElse
  If Not sqlite3_bind_text(lStatement, lIndex, Text)
    ProcedureReturn #True
  EndIf
  CompilerEndIf
  ProcedureReturn #False
EndProcedure

Procedure SQLiteColumnBlob(lStatement, lColumn)
  ProcedureReturn sqlite3_column_blob(lStatement, lColumn)
EndProcedure

Procedure SQLiteColumnByte(lStatement, lColumn)
  CompilerIf #PB_Compiler_Unicode
  ProcedureReturn sqlite3_column_bytes16(lStatement, lColumn)
  CompilerElse
  ProcedureReturn sqlite3_column_bytes(lStatement, lColumn)
  CompilerEndIf
EndProcedure

Procedure.d SQLiteColumnDouble(lStatement, lColumn)
  ProcedureReturn sqlite3_column_double(lStatement, lColumn)
EndProcedure

Procedure.l SQLiteColumnLong(lStatement, lColumn)
  ProcedureReturn sqlite3_column_int(lStatement, lColumn)
EndProcedure

Procedure.q SQLiteColumnQuad(lStatement, lColumn)
  ProcedureReturn sqlite3_column_int64(lStatement, lColumn)
EndProcedure

Procedure.s SQLiteColumnText(lStatement, lColumn)
  Protected Result.i
  CompilerIf #PB_Compiler_Unicode
  Result = sqlite3_column_text16(lStatement, lColumn)
  If Result
    ProcedureReturn PeekS(Result)
  EndIf
  CompilerElse
  Result = sqlite3_column_text(lStatement, lColumn)
  If Result
    ProcedureReturn PeekS(Result)
  EndIf
  CompilerEndIf
EndProcedure 

Procedure SQLiteColumnType(lStatement, lColumn)
  ProcedureReturn sqlite3_column_type(lStatement, lColumn)
EndProcedure

Code: Select all

EnableExplicit

XIncludeFile "SQLite3.pbi"

Define Mem, File, Size, hDB, lStatement

File = ReadFile(#PB_Any, #PB_Compiler_Home + "examples/sources/data/PureBasicLogo.bmp")
If File = #False
  Debug "Can't open PureBasicLogo.bmp"
  End
EndIf

Size = Lof(File)

Mem = AllocateMemory(Size)
If Mem = #False
  Debug "Can't allocate memory"
  End
EndIf

ReadData(File, Mem, Size)
CloseFile(File)

If CreateFile(0, "logo.db") : CloseFile(0) : EndIf
hDB = OpenDatabase(#PB_Any, "logo.db", "", "")
If hDB = #False
  Debug "Can't create database"
  End
EndIf

SQLiteExecute(hDB, "Create Table tblBlob (fldBlob BLOB)")

lStatement = SQLitePrepare(hDB, "insert into tblBlob (fldBlob) values (?);")
If lStatement
  SQLiteBindBlob(lStatement, 1, Mem, Size)
  SQLiteStep(lStatement)
  SQLiteFinalize(lStatement)
EndIf

CloseDatabase(hDB)
FreeMemory(Mem)

Code: Select all

EnableExplicit

XIncludeFile "SQLite3.pbi"

Define Mem, File, Size, hDB, lStatement

hDB = OpenDatabase(#PB_Any, "logo.db", "", "")
If hDB = #False
  Debug "Can't open database"
  End
EndIf

lStatement = SQLitePrepare(hDB, "Select fldBlob From tblBlob")

If lStatement
  SQLiteStep(lStatement)
  Size = SQLiteColumnByte(lStatement, 0)
  Mem = SQLiteColumnBlob(lStatement, 0)
  If Mem
;     File = CreateFile(#PB_Any, "logo.bmp")
;     If File
;       WriteData(File, Mem, Size)
;       CloseFile(File)
;     EndIf
    CatchImage(0, Mem)
    OpenWindow(0, #PB_Ignore, #PB_Ignore, ImageWidth(0), ImageHeight(0), "blob - example")
    ImageGadget(0, 0, 0, ImageWidth(0), ImageHeight(0), ImageID(0))
    Repeat : Until WaitWindowEvent() = #PB_Event_CloseWindow
  EndIf
  SQLiteFinalize(lStatement)
EndIf

CloseDatabase(hDB)

Code: Select all

; binding-functions
Procedure SQLitePrepare(Database, Statement.s); Result is lStatement
  Protected hDB = DatabaseID(Database)
  Protected Result
  CompilerIf #PB_Compiler_Unicode
  If Not sqlite3_prepare16(hDB, Statement, -1, @Result)
    ProcedureReturn Result
  EndIf
  CompilerElse
  If Not sqlite3_prepare(hDB, Statement, -1, @Result)
    ProcedureReturn Result
  EndIf
  CompilerEndIf
EndProcedure  <------- error "invalid memory access"

Code: Select all

Procedure.i SQLitePrepare(hDB.i, Statement.s); Result is lStatement
  Protected Result.i
  CompilerIf #PB_Compiler_Unicode
  If Not sqlite3_prepare16(hDB, Statement, -1, @Result)
    ProcedureReturn Result
  EndIf
  CompilerElse
  If Not sqlite3_prepare(hDB, Statement, -1, @Result)
    ProcedureReturn Result
  EndIf
  CompilerEndIf
EndProcedure