SQLite3 BaseFunction-Include for Win + Lin + Unicode

Share your advanced PureBasic knowledge/code with the community.
Post Reply
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4792
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Post by Fangbeast »

Can SQLiteColName be made to work with prepare/step method? I'd like to be able to return the column name with my results. Tried a few things, no joy.

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
*EDIT** Been playing with this a little and noticed some things are backwards and need some clarification.

A "column" is a column across >>>>>
A "row" is a line down (a row down) right?

You definitions seem to be done backwards. Regardless, I cannot seem to get the column names with the included routine below. It seems to return column definitions and not column names, no matter what I do to it??

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

Try this code and tell me where I have gone wrong please.

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
Top
User avatar
ts-soft
Always Here
Always Here
Posts: 5756
Joined: Thu Jun 24, 2004 2:44 pm
Location: Berlin - Germany

Post by ts-soft »

Update for PB4.30

some small changes to work with x64

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
PureBasic 5.73 | SpiderBasic 2.30 | Windows 10 Pro (x64) | Linux Mint 20.1 (x64)
Old bugs good, new bugs bad! Updates are evil: might fix old bugs and introduce no new ones.
Image
Top
User avatar
GG
Enthusiast
Enthusiast
Posts: 266
Joined: Tue Jul 26, 2005 12:02 pm
Location: Lieusaint (77), France

Post by GG »

Thanks ! :D
Purebasic 6.12 64 bits - Windows 11 Pro 64 bits 23H2
Top
User avatar
ts-soft
Always Here
Always Here
Posts: 5756
Joined: Thu Jun 24, 2004 2:44 pm
Location: Berlin - Germany

Post by ts-soft »

you're welcome
PureBasic 5.73 | SpiderBasic 2.30 | Windows 10 Pro (x64) | Linux Mint 20.1 (x64)
Old bugs good, new bugs bad! Updates are evil: might fix old bugs and introduce no new ones.
Image
Top
User avatar
ts-soft
Always Here
Always Here
Posts: 5756
Joined: Thu Jun 24, 2004 2:44 pm
Location: Berlin - Germany

Post by ts-soft »

New Version for PB4.30 beta 3 and higher

- removed useless function (you can mix pb native functions and api)

SQLite3.pbi:

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
blob example:

create_blob.pb

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)
extract_blob.pb

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)
Last edited by ts-soft on Thu Oct 02, 2008 9:19 pm, edited 1 time in total.
PureBasic 5.73 | SpiderBasic 2.30 | Windows 10 Pro (x64) | Linux Mint 20.1 (x64)
Old bugs good, new bugs bad! Updates are evil: might fix old bugs and introduce no new ones.
Image
Top
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

Great job Thomas. 8)
I may look like a mule, but I'm not a complete ass.
Top
User avatar
ts-soft
Always Here
Always Here
Posts: 5756
Joined: Thu Jun 24, 2004 2:44 pm
Location: Berlin - Germany

Post by ts-soft »

srod wrote:Great job Thomas. 8)
thanks, by i have post the false extractcode example :lol:
fixed :wink:
PureBasic 5.73 | SpiderBasic 2.30 | Windows 10 Pro (x64) | Linux Mint 20.1 (x64)
Old bugs good, new bugs bad! Updates are evil: might fix old bugs and introduce no new ones.
Image
Top
User avatar
michel51
Enthusiast
Enthusiast
Posts: 290
Joined: Mon Nov 21, 2005 10:21 pm
Location: Germany

Post by michel51 »

Hi Thomas,

if I run your examples, I get an error "invalid memory access" on this point

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"
I use Beta 3 for testing.
michel51

Mac OS X Snow Leopard (10.6.8 ) Intel
PureBasic V 5.21(x64), V 5.22beta
Top
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

The two blob examples work fine here on PB 4.3 beta 3 x86. Are you testing with PB x86 or x64?
I may look like a mule, but I'm not a complete ass.
Top
User avatar
michel51
Enthusiast
Enthusiast
Posts: 290
Joined: Mon Nov 21, 2005 10:21 pm
Location: Germany

Post by michel51 »

srod wrote:The two blob examples work fine here on PB 4.3 beta 3 x86. Are you testing with PB x86 or x64?
Hmm..., PowerPC.
Is that the reason for the error?
michel51

Mac OS X Snow Leopard (10.6.8 ) Intel
PureBasic V 5.21(x64), V 5.22beta
Top
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Post by srod »

michel51 wrote:
srod wrote:The two blob examples work fine here on PB 4.3 beta 3 x86. Are you testing with PB x86 or x64?
Hmm..., PowerPC.
Is that the reason for the error?
Ah, I couldn't really say. It works here on Win and the comments in ts-soft's code states that he has tested on XUbuntu Linux and so... There does indeed seem to be a problem on PowerPC!! It could be a bug in PB's SQLite lib on that system?
I may look like a mule, but I'm not a complete ass.
Top
User avatar
ts-soft
Always Here
Always Here
Posts: 5756
Joined: Thu Jun 24, 2004 2:44 pm
Location: Berlin - Germany

Post by ts-soft »

i can't test on PowerPC, sry.
Normal it should run
PureBasic 5.73 | SpiderBasic 2.30 | Windows 10 Pro (x64) | Linux Mint 20.1 (x64)
Old bugs good, new bugs bad! Updates are evil: might fix old bugs and introduce no new ones.
Image
Top
User avatar
michel51
Enthusiast
Enthusiast
Posts: 290
Joined: Mon Nov 21, 2005 10:21 pm
Location: Germany

Post by michel51 »

srod wrote:... It could be a bug in PB's SQLite lib on that system?
I don't know. May be ts-soft can check this, because he has created the include for Mac OS X too.
michel51

Mac OS X Snow Leopard (10.6.8 ) Intel
PureBasic V 5.21(x64), V 5.22beta
Top
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4792
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

SqLite3 base functions

Post by Fangbeast »

Hello. I am getting random crashes in this routine. Specifically at this line "If Not sqlite3_prepare(hDB, Statement, -1, @Result)". Pb 4.3 final.

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
If I restart the program and go to the record that I was editing that crashed, the record is now okay.

Later on, it crashes at a different record in that same routine at that same line.

Anyone have any ideas why this should happen?
Top
jamba
Enthusiast
Enthusiast
Posts: 144
Joined: Fri Jan 15, 2010 2:03 pm
Location: Triad, NC
Contact:
Contact jamba

Re: SQLite3 BaseFunction-Include for Win + Lin + Unicode

Post by jamba »

just found this, awesome!
-Jon

Fedora user
But I work with Win7
Top
Post Reply

Return to “Tricks 'n' Tips”