SQLite3 BaseFunction-Include for Win + Lin + Unicode

Share your advanced PureBasic knowledge/code with the community.
Post Reply
User avatar
ts-soft
Always Here
Always Here
Posts: 5756
Joined: Thu Jun 24, 2004 2:44 pm
Location: Berlin - Germany

SQLite3 BaseFunction-Include for Win + Lin + Unicode

Post by ts-soft »

This Source based on a example by MLK
Tested with WinXP and XUbuntu. Ansi and Unicode

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

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

PrototypeC Proto_sqlite3_close(hDB.l)
PrototypeC Proto_sqlite3_errcode(hDB.l)
PrototypeC Proto_sqlite3_errmsg(hDB.l)
PrototypeC Proto_sqlite3_exec(hDB.l, Statement.p-utf8, cb.l = 0, cbdata.l = 0, errmsg.l = 0)
PrototypeC Proto_sqlite3_exec_a(hDB.l, Statement.p-ascii, cb.l = 0, cbdata.l = 0, errmsg.l = 0)
PrototypeC Proto_sqlite3_free_table(*table.l)
PrototypeC Proto_sqlite3_get_table(hDB.l, Statement.p-utf8, *table, nRow.l, nColumn.l, errmsg.l = 0)
PrototypeC Proto_sqlite3_get_table_a(hDB.l, Statement.p-ascii, *table, nRow.l, nColumn.l, errmsg.l = 0)
PrototypeC Proto_sqlite3_libversion()
PrototypeC Proto_sqlite3_open(filename.s, *hDB.l)
PrototypeC Proto_sqlite3_last_insert_rowid(hDB.l)
PrototypeC Proto_sqlite3_prepare(hDB.l, Statement.p-utf8, nBytes.l, *hResult, *pzTail = 0)
PrototypeC Proto_sqlite3_prepare_a(hDB.l, Statement.p-ascii, nBytes.l, *hResult, *pzTail = 0)
PrototypeC Proto_sqlite3_prepare16(hDB.l, Statement.p-unicode, nBytes.l, *hResult, *pzTail = 0)
PrototypeC Proto_sqlite3_finalize(lStatement.l)
PrototypeC Proto_sqlite3_step(lStatement.l)
PrototypeC Proto_sqlite3_bind_blob(lStatement.l, lIndex.l, lBinaryBuffer.l, lBinaryBufferLength.l, *void = -1)
PrototypeC Proto_sqlite3_bind_double(lStatement.l, lIndex.l, dValue.d)
PrototypeC Proto_sqlite3_bind_int(lStatement.l, lIndex.l, lValue.l)
PrototypeC Proto_sqlite3_bind_int64(lStatement.l, lIndex.l, qValue.q)
PrototypeC Proto_sqlite3_bind_text(lStatement, lIndex, lTextBuffer, len.l = -1, type.l = #SQLITE3_STATIC)
PrototypeC Proto_sqlite3_column(lStatement, lCol.l)
PrototypeC.d Proto_sqlite3_column_double(lStatement, lCol.l)
PrototypeC.q Proto_sqlite3_column_quad(lStatement, lCol.l)

Structure SQ3_Functions
  DLL.l
  IsASCII.b
  PeekMode.l
  close.Proto_sqlite3_close
  errcode.Proto_sqlite3_errcode
  errmsg.Proto_sqlite3_errmsg
  errmsg16.Proto_sqlite3_errmsg
  exec.Proto_sqlite3_exec
  exec_a.Proto_sqlite3_exec_a
  free_table.Proto_sqlite3_free_table
  get_table.Proto_sqlite3_get_table
  get_table_a.Proto_sqlite3_get_table_a
  libversion.Proto_sqlite3_libversion
  open.Proto_sqlite3_open
  open16.Proto_sqlite3_open
  last_insert_rowid.Proto_sqlite3_last_insert_rowid
  prepare.Proto_sqlite3_prepare
  prepare_a.Proto_sqlite3_prepare_a
  prepare16.Proto_sqlite3_prepare16
  finalize.Proto_sqlite3_finalize
  step_.Proto_sqlite3_step
  bind_blob.Proto_sqlite3_bind_blob
  bind_double.Proto_sqlite3_bind_double
  bind_int.Proto_sqlite3_bind_int
  bind_int64.Proto_sqlite3_bind_int64
  bind_text.Proto_sqlite3_bind_text
  bind_text16.Proto_sqlite3_bind_text
  column_blob.Proto_sqlite3_column
  column_bytes.Proto_sqlite3_column
  column_bytes16.Proto_sqlite3_column
  column_double.Proto_sqlite3_column_double
  column_int.Proto_sqlite3_column
  column_int64.Proto_sqlite3_column_quad
  column_text.Proto_sqlite3_column
  column_text16.Proto_sqlite3_column
  column_type.Proto_sqlite3_column
EndStructure

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

Global SQ3.SQ3_Functions

Procedure.l SQLiteEnd()
  If SQ3\DLL
    CloseLibrary(SQ3\DLL)
    SQ3\DLL = #False
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteInit(sqlite3lib.s = "")
  If sqlite3lib <> ""
    SQ3\DLL = OpenLibrary(#PB_Any, sqlite3lib)
  Else
    CompilerSelect #PB_Compiler_OS
      CompilerCase #PB_OS_Windows
        SQ3\DLL = OpenLibrary(#PB_Any, "sqlite3.dll")
      CompilerCase #PB_OS_Linux
        SQ3\DLL = OpenLibrary(#PB_Any, "libsqlite3.so")
      CompilerCase #PB_OS_MacOS
        SQ3\DLL = OpenLibrary(#PB_Any, "/usr/lib/libsqlite3.dylib")
      CompilerDefault
        ProcedureReturn #False
    CompilerEndSelect
  EndIf
  If SQ3\DLL
    SQ3\close           = GetFunction(SQ3\DLL, "sqlite3_close")
    SQ3\errcode         = GetFunction(SQ3\DLL, "sqlite3_errcode")
    SQ3\errmsg          = GetFunction(SQ3\DLL, "sqlite3_errmsg")
    SQ3\errmsg16        = GetFunction(SQ3\DLL, "sqlite3_errmsg16")
    SQ3\exec            = GetFunction(SQ3\DLL, "sqlite3_exec")
    SQ3\exec_a          = GetFunction(SQ3\DLL, "sqlite3_exec")
    SQ3\free_table      = GetFunction(SQ3\DLL, "sqlite3_free_table")
    SQ3\get_table       = GetFunction(SQ3\DLL, "sqlite3_get_table")
    SQ3\get_table_a     = GetFunction(SQ3\DLL, "sqlite3_get_table")
    SQ3\libversion      = GetFunction(SQ3\DLL, "sqlite3_libversion")
    SQ3\open            = GetFunction(SQ3\DLL, "sqlite3_open")
    SQ3\open16          = GetFunction(SQ3\DLL, "sqlite3_open16")
    SQ3\last_insert_rowid = GetFunction(SQ3\DLL, "sqlite3_last_insert_rowid")
    SQ3\prepare         = GetFunction(SQ3\DLL, "sqlite3_prepare")
    SQ3\prepare_a       = GetFunction(SQ3\DLL, "sqlite3_prepare")
    SQ3\prepare16       = GetFunction(SQ3\DLL, "sqlite3_prepare16")
    SQ3\finalize        = GetFunction(SQ3\DLL, "sqlite3_finalize")
    SQ3\step_           = GetFunction(SQ3\DLL, "sqlite3_step")
    SQ3\bind_blob       = GetFunction(SQ3\DLL, "sqlite3_bind_blob")
    SQ3\bind_double     = GetFunction(SQ3\DLL, "sqlite3_bind_double")
    SQ3\bind_int        = GetFunction(SQ3\DLL, "sqlite3_bind_int")
    SQ3\bind_int64      = GetFunction(SQ3\DLL, "sqlite3_bind_int64")
    SQ3\bind_text       = GetFunction(SQ3\DLL, "sqlite3_bind_text")
    SQ3\bind_text16     = GetFunction(SQ3\DLL, "sqlite3_bind_text16")
    SQ3\column_blob     = GetFunction(SQ3\DLL, "sqlite3_column_blob")
    SQ3\column_bytes    = GetFunction(SQ3\DLL, "sqlite3_column_bytes")
    SQ3\column_bytes16  = GetFunction(SQ3\DLL, "sqlite3_column_bytes16")
    SQ3\column_double   = GetFunction(SQ3\DLL, "sqlite3_column_double")
    SQ3\column_int      = GetFunction(SQ3\DLL, "sqlite3_column_int")
    SQ3\column_int64    = GetFunction(SQ3\DLL, "sqlite3_column_int64")
    SQ3\column_text     = GetFunction(SQ3\DLL, "sqlite3_column_text")
    SQ3\column_text16   = GetFunction(SQ3\DLL, "sqlite3_column_text16")
    SQ3\column_type     = GetFunction(SQ3\DLL, "sqlite3_column_type")
    SQ3\PeekMode = #PB_UTF8
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteClose(hDB.l)
  If SQ3\Close
    If Not SQ3\Close(hDB)
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteOpen(FileName.s, AsciiMode.l = #False)
  Protected hDB.l
  CompilerIf #PB_Compiler_Unicode
  If SQ3\open16
    If Not SQ3\open16(FileName, @hDB)
      ProcedureReturn hDB
    EndIf
  EndIf
  CompilerElse
  If AsciiMode
    SQ3\IsASCII = #True
    SQ3\PeekMode = #PB_Ascii
  EndIf
  If SQ3\open
    If Not SQ3\open(FileName, @hDB)
      ProcedureReturn hDB
    EndIf
  EndIf
  CompilerEndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteErrorCode(hDB.l)
  If SQ3\errcode
    ProcedureReturn SQ3\errcode(hDB)
  EndIf
  ProcedureReturn -1
EndProcedure

Procedure.s SQLiteErrorMsg(hDB.l)
  CompilerIf #PB_Compiler_Unicode
  If SQ3\errmsg16
    ProcedureReturn PeekS(SQ3\errmsg16(hDB))
  EndIf
  CompilerElse
  If SQ3\errmsg
    ProcedureReturn PeekS(SQ3\errmsg(hDB))
  EndIf
  CompilerEndIf
  ProcedureReturn ""
EndProcedure

Procedure.s SQLiteLibversion()
  If SQ3\libversion
    ProcedureReturn PeekS(SQ3\libversion(), #PB_Any, #PB_Ascii)
  EndIf
EndProcedure

Procedure.l SQLiteExecute(hDB.l, Statement.s, callback.l = 0, cbpara.l = 0)
  If SQ3\IsASCII
    If SQ3\exec_a
      If Not SQ3\exec_a(hDB, Statement, callback, cbpara)
        ProcedureReturn #True
      EndIf
    EndIf
  Else
    If SQ3\exec
      If Not SQ3\exec(hDB, Statement, callback, cbpara)
        ProcedureReturn #True
      EndIf
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteLastInsertRowId(hDB.l)
  If SQ3\last_insert_rowid
    ProcedureReturn SQ3\last_insert_rowid(hDB)
  EndIf
  ProcedureReturn #False
EndProcedure

; table-functions
Procedure.l SQliteFreeTable(*table.SQ3_TABLEMAP)
  If SQ3\free_table And *table
    SQ3\free_table(*table\Table)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteGetTable(hDB.l, Statement.s, *table.SQ3_TABLEMAP)
  Protected nRow.l, nColumn.l, lResultPtr.l, result.l
  If SQ3\get_table
    If SQ3\IsASCII
      result = SQ3\get_table_a(hdb, Statement, @lResultPtr, @nRow, @nColumn)
    Else
      result = SQ3\get_table(hdb, Statement, @lResultPtr, @nRow, @nColumn)
    EndIf
    If Not result
      *table\Table = lResultPtr
      *table\Rows = nRow
      *table\Cols = nColumn
      *table\RowPos = 0
      *table\ColPos = 0
      ProcedureReturn *table
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

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

Procedure.l 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.l SQLiteSelectRow(*Table.SQ3_TABLEMAP, Row.l)
  If *Table
    If Row > 0 And Row <= *Table\Rows
      *Table\RowPos = Row
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

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

Procedure.l SQLiteSelectPos(*Table.SQ3_TABLEMAP, Row.l, Col.l)
  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.l 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.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

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

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

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

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

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

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

Code: Select all

EnableExplicit

XIncludeFile "SQLite3_Include.pbi"

;=====================
;|||    EXAMPLE    |||
;;;;;;;;;;;;;;;;;;;;;;

Define.l hDB, r, i
Define.SQ3_TABLEMAP Table

If SQLiteInit() = 0
  MessageRequester("Error", "Couldn't init SQLite3 lib") : End
EndIf

hDB = SQLiteOpen("test.db")
If Not hDB 
  MessageRequester("Error", SQliteErrorMsg(hDB)) : End
EndIf

If SQliteExecute(hDB, "CREATE TABLE testtable (spalte1, spalte2, spalte3)") = #False
  Debug SQliteErrorMsg(hDB)
Else
  For r = 1 To 5
    If SQliteExecute(hDB, "INSERT INTO testtable (spalte1, spalte2, spalte3) VALUES ('r" + Str(r) + "c1', 'r" + Str(r) + "c2', 'r" + Str(r) + "c3')") = #False
      Debug SQliteErrorMsg(hDB)
    EndIf
  Next
EndIf


Table.SQ3_TABLEMAP
SQliteGetTable(hDB, "SELECT * FROM testtable", Table)
If Table
  While SQLiteNextRow(Table)
    i + 1 : Debug "_row " + Str(i) + ":"
    While SQLiteNextCol(Table)
      Debug SQLiteValue(Table)
    Wend
  Wend
  ;;;;
  ;
  Debug "_row 1, col 1:"
  SQLiteResetPos(Table)
  SQLiteNextRow(Table)
  SQLiteNextCol(Table)
  Debug SQLiteValue(Table)
  ;
  Debug "_row 2, col 2:"
  SQLiteSelectRow(Table, 2)
  SQLiteSelectCol(Table, 2)
  Debug SQLiteValue(Table)
  ;
  Debug "_row 3, col 3:"
  Debug SQLiteSelectValue(Table, 3, 3)
  ;
  Debug "_row 1, col 3:"
  SQLiteSelectPos(Table, 1, 1)
  Debug SQLiteColValue(Table, SQLiteCols(Table))
  ;
  Debug "_row 2, col 3:"
  Debug SQLiteRowValue(Table, 2)
  ;
  Debug "_spaltennamen:"
  Debug SQLiteColName(Table, 1)
  Debug SQLiteColName(Table, 2)
  Debug SQLiteColName(Table, 3)
  ;
  ;;;;;;;;;;;;;;;;;;;;;;;
  SQliteFreeTable(Table)
EndIf

SQLiteClose(hDB)
SQLiteEnd()
Last edited by ts-soft on Thu May 31, 2007 1:25 am, edited 4 times 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 »

Nice, thanks for that. 8)

A request; could you add 'sqlite3_last_insert_rowid' as this is quite a useful function. I could of course add it myself, but it would be better to just have one version of the source.

Thanks again.
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: A request; could you add 'sqlite3_last_insert_rowid' as this is quite a useful function.
done :wink: , see first post
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 »

Now that's what I call a prompt service! :)

Thanks.
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 »

Added optional parameter callback for SQLiteExecute()
thx to pdwyer :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
netmaestro
PureBasic Bullfrog
PureBasic Bullfrog
Posts: 8434
Joined: Wed Jul 06, 2005 5:42 am
Location: Fort Nelson, BC, Canada

Post by netmaestro »

Fabulous work ts, thanks for sharing it 8)
BERESHEIT
Top
User avatar
Progi1984
Addict
Addict
Posts: 806
Joined: Fri Feb 25, 2005 1:01 am
Location: France > Rennes
Contact:
Contact Progi1984

Post by Progi1984 »

Tested with the last version of SQLite ?

Thanks you for your job !
Top
CSAUER
Enthusiast
Enthusiast
Posts: 188
Joined: Mon Oct 18, 2004 7:23 am
Location: Germany

Post by CSAUER »

I am not sure how to roll out to Mac OS X, but I know that Sqlite is still delivered since Mac OS X 10.4:
Introduced in Mac OS X version 10.4, the SQLite library lets you embed a SQL database engine into your applications. Programs that link with the SQLite library can access SQL databases without running a separate RDBMS process. You can create local database files and manage the tables and records in those files. The library is designed for general purpose use but is still optimized to provide fast access to database records.

The SQLite library is located at /usr/lib/libsqlite3.dylib and the sqlite3.h header file is in /usr/include. A command-line interface (sqlite3) is also available for communicating with SQLite databases using scripts. For details on how to use this command-line interface, see the man page for sqlite3. You can access this page from the command line or in Mac OS X Man Pages.
Please correct me if I am wrong, but this could be the key to enable SQlite with the above mentioned code on Mac:

Code: Select all

    CompilerSelect #PB_Compiler_OS
    CompilerCase #PB_OS_Windows
      SQ3\DLL = OpenLibrary(#PB_Any, "sqlite3.dll")
    CompilerCase #PB_OS_MacOS
      ; according to Apple Developer Connection:      
      SQ3\DLL = OpenLibrary(#PB_Any, "/usr/lib/libsqlite3.dylib")
    CompilerDefault    
      SQ3\DLL = OpenLibrary(#PB_Any, "libsqlite3.so")
    CompilerEndSelect
But I was not able to have a test. I will do next days.
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 »

When you have test it, i will add the directive

You can also simple use this:

Code: Select all

SQLiteInit("/usr/lib/libsqlite3.dylib")
to test of the right path.
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
Psychophanta
Addict
Addict
Posts: 4997
Joined: Wed Jun 11, 2003 9:33 pm
Location: Lípetsk, Russian Federation
Contact:
Contact Psychophanta

Post by Psychophanta »

Thank you 8)
http://www.zeitgeistmovie.com

While world=business:world+mafia:Wend
Will never leave this forum until the absolute bugfree PB :mrgreen:
Top
CSAUER
Enthusiast
Enthusiast
Posts: 188
Joined: Mon Oct 18, 2004 7:23 am
Location: Germany

... and for Mac!

Post by CSAUER »

Yeah, cool, it works. This is one of the first routines which works on PB4 Beta 1. :)
This Sqlite routine is really cross-plattform and it should in PBOSL - or better supported by the native Database-Lib.

Here you can see a screenshot:

Image

Maybe we can merge some ideas from this post to get a perfect cross-platform lib: http://www.purebasic.fr/english/viewtopic.php?t=27038
Top
Num3
PureBasic Expert
PureBasic Expert
Posts: 2810
Joined: Fri Apr 25, 2003 4:51 pm
Location: Portugal, Lisbon
Contact:
Contact Num3

Post by Num3 »

Yeah...

Purebasic is coming closer and closer to a fully mature and heavy weight competition to anything out there!!!
Top
Dare
Addict
Addict
Posts: 1965
Joined: Mon May 29, 2006 1:01 am
Location: Outback

Post by Dare »

Num3 wrote:Yeah...

Purebasic is coming closer and closer to a fully mature and heavy weight competition to anything out there!!!
Very true!
Dare2 cut down to size
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 »

For advanced using, i have add bindings-functions, all functions with unicode-support!
SQLitePrepare(hDB.l, Statement.s); Result is lStatement
SQLiteFinalize(lStatement.l)
SQLiteStep(lStatement.l)

SQLiteBindBlob(lStatement.l, lIndex.l, lBuffer.l, lBufferLength.l)
SQLiteBindDouble(lStatement.l, lIndex.l, dValue.d)
SQLiteBindLong(lStatement.l, lIndex.l, lValue.l)
SQLiteBindQuad(lStatement.l, lIndex.l, qValue.q)
SQLiteBindText(lStatement.l, lIndex.l, Text.s)

SQLiteColumnBlob(lStatement.l, lColumn.l)
SQLiteColumnByte(lStatement.l, lColumn.l)
SQLiteColumnDouble(lStatement.l, lColumn.l)
SQLiteColumnLong(lStatement.l, lColumn.l)
SQLiteColumnQuad(lStatement.l, lColumn.l)
SQLiteColumnText(lStatement.l, lColumn.l)
SQLiteColumnType(lStatement.l, lColumn.l)
This isn't test at all, if you found any bug, please tell me

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

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

PrototypeC Proto_sqlite3_close(hDB.l)
PrototypeC Proto_sqlite3_errcode(hDB.l)
PrototypeC Proto_sqlite3_errmsg(hDB.l)
PrototypeC Proto_sqlite3_exec(hDB.l, Statement.p-utf8, cb.l = 0, cbdata.l = 0, errmsg.l = 0)
PrototypeC Proto_sqlite3_exec_a(hDB.l, Statement.p-ascii, cb.l = 0, cbdata.l = 0, errmsg.l = 0)
PrototypeC Proto_sqlite3_free_table(*table.l)
PrototypeC Proto_sqlite3_get_table(hDB.l, Statement.p-utf8, *table, nRow.l, nColumn.l, errmsg.l = 0)
PrototypeC Proto_sqlite3_get_table_a(hDB.l, Statement.p-ascii, *table, nRow.l, nColumn.l, errmsg.l = 0)
PrototypeC Proto_sqlite3_libversion()
PrototypeC Proto_sqlite3_open(filename.s, *hDB.l)
PrototypeC Proto_sqlite3_last_insert_rowid(hDB.l)
PrototypeC Proto_sqlite3_prepare(hDB.l, Statement.p-utf8, nBytes.l, *hResult, *pzTail = 0)
PrototypeC Proto_sqlite3_prepare_a(hDB.l, Statement.p-ascii, nBytes.l, *hResult, *pzTail = 0)
PrototypeC Proto_sqlite3_prepare16(hDB.l, Statement.p-unicode, nBytes.l, *hResult, *pzTail = 0)
PrototypeC Proto_sqlite3_finalize(lStatement.l)
PrototypeC Proto_sqlite3_step(lStatement.l)
PrototypeC Proto_sqlite3_bind_blob(lStatement.l, lIndex.l, lBinaryBuffer.l, lBinaryBufferLength.l, *void = -1)
PrototypeC Proto_sqlite3_bind_double(lStatement.l, lIndex.l, dValue.d)
PrototypeC Proto_sqlite3_bind_int(lStatement.l, lIndex.l, lValue.l)
PrototypeC Proto_sqlite3_bind_int64(lStatement.l, lIndex.l, qValue.q)
PrototypeC Proto_sqlite3_bind_text(lStatement, lIndex, lTextBuffer, len.l = -1, type.l = #SQLITE3_STATIC)
PrototypeC Proto_sqlite3_column(lStatement, lCol.l)
PrototypeC.d Proto_sqlite3_column_double(lStatement, lCol.l)
PrototypeC.q Proto_sqlite3_column_quad(lStatement, lCol.l)

Structure SQ3_Functions
  DLL.l
  IsASCII.b
  PeekMode.l
  close.Proto_sqlite3_close
  errcode.Proto_sqlite3_errcode
  errmsg.Proto_sqlite3_errmsg
  errmsg16.Proto_sqlite3_errmsg
  exec.Proto_sqlite3_exec
  exec_a.Proto_sqlite3_exec_a
  free_table.Proto_sqlite3_free_table
  get_table.Proto_sqlite3_get_table
  get_table_a.Proto_sqlite3_get_table_a
  libversion.Proto_sqlite3_libversion
  open.Proto_sqlite3_open
  open16.Proto_sqlite3_open
  last_insert_rowid.Proto_sqlite3_last_insert_rowid
  prepare.Proto_sqlite3_prepare
  prepare_a.Proto_sqlite3_prepare_a
  prepare16.Proto_sqlite3_prepare16
  finalize.Proto_sqlite3_finalize
  step_.Proto_sqlite3_step
  bind_blob.Proto_sqlite3_bind_blob
  bind_double.Proto_sqlite3_bind_double
  bind_int.Proto_sqlite3_bind_int
  bind_int64.Proto_sqlite3_bind_int64
  bind_text.Proto_sqlite3_bind_text
  bind_text16.Proto_sqlite3_bind_text
  column_blob.Proto_sqlite3_column
  column_bytes.Proto_sqlite3_column
  column_bytes16.Proto_sqlite3_column
  column_double.Proto_sqlite3_column_double
  column_int.Proto_sqlite3_column
  column_int64.Proto_sqlite3_column_quad
  column_text.Proto_sqlite3_column
  column_text16.Proto_sqlite3_column
  column_type.Proto_sqlite3_column
EndStructure

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

Global SQ3.SQ3_Functions

Procedure.l SQLiteEnd()
  If SQ3\DLL
    CloseLibrary(SQ3\DLL)
    SQ3\DLL = #False
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteInit(sqlite3lib.s = "")
  If sqlite3lib <> ""
    SQ3\DLL = OpenLibrary(#PB_Any, sqlite3lib)
  Else
    CompilerSelect #PB_Compiler_OS
      CompilerCase #PB_OS_Windows
        SQ3\DLL = OpenLibrary(#PB_Any, "sqlite3.dll")
      CompilerCase #PB_OS_Linux
        SQ3\DLL = OpenLibrary(#PB_Any, "libsqlite3.so")
      CompilerCase #PB_OS_MacOS
        SQ3\DLL = OpenLibrary(#PB_Any, "/usr/lib/libsqlite3.dylib")
      CompilerDefault
        ProcedureReturn #False
    CompilerEndSelect
  EndIf
  If SQ3\DLL
    SQ3\close           = GetFunction(SQ3\DLL, "sqlite3_close")
    SQ3\errcode         = GetFunction(SQ3\DLL, "sqlite3_errcode")
    SQ3\errmsg          = GetFunction(SQ3\DLL, "sqlite3_errmsg")
    SQ3\errmsg16        = GetFunction(SQ3\DLL, "sqlite3_errmsg16")
    SQ3\exec            = GetFunction(SQ3\DLL, "sqlite3_exec")
    SQ3\exec_a          = GetFunction(SQ3\DLL, "sqlite3_exec")
    SQ3\free_table      = GetFunction(SQ3\DLL, "sqlite3_free_table")
    SQ3\get_table       = GetFunction(SQ3\DLL, "sqlite3_get_table")
    SQ3\get_table_a     = GetFunction(SQ3\DLL, "sqlite3_get_table")
    SQ3\libversion      = GetFunction(SQ3\DLL, "sqlite3_libversion")
    SQ3\open            = GetFunction(SQ3\DLL, "sqlite3_open")
    SQ3\open16          = GetFunction(SQ3\DLL, "sqlite3_open16")
    SQ3\last_insert_rowid = GetFunction(SQ3\DLL, "sqlite3_last_insert_rowid")
    SQ3\prepare         = GetFunction(SQ3\DLL, "sqlite3_prepare")
    SQ3\prepare_a       = GetFunction(SQ3\DLL, "sqlite3_prepare")
    SQ3\prepare16       = GetFunction(SQ3\DLL, "sqlite3_prepare16")
    SQ3\finalize        = GetFunction(SQ3\DLL, "sqlite3_finalize")
    SQ3\step_           = GetFunction(SQ3\DLL, "sqlite3_step")
    SQ3\bind_blob       = GetFunction(SQ3\DLL, "sqlite3_bind_blob")
    SQ3\bind_double     = GetFunction(SQ3\DLL, "sqlite3_bind_double")
    SQ3\bind_int        = GetFunction(SQ3\DLL, "sqlite3_bind_int")
    SQ3\bind_int64      = GetFunction(SQ3\DLL, "sqlite3_bind_int64")
    SQ3\bind_text       = GetFunction(SQ3\DLL, "sqlite3_bind_text")
    SQ3\bind_text16     = GetFunction(SQ3\DLL, "sqlite3_bind_text16")
    SQ3\column_blob     = GetFunction(SQ3\DLL, "sqlite3_column_blob")
    SQ3\column_bytes    = GetFunction(SQ3\DLL, "sqlite3_column_bytes")
    SQ3\column_bytes16  = GetFunction(SQ3\DLL, "sqlite3_column_bytes16")
    SQ3\column_double   = GetFunction(SQ3\DLL, "sqlite3_column_double")
    SQ3\column_int      = GetFunction(SQ3\DLL, "sqlite3_column_int")
    SQ3\column_int64    = GetFunction(SQ3\DLL, "sqlite3_column_int64")
    SQ3\column_text     = GetFunction(SQ3\DLL, "sqlite3_column_text")
    SQ3\column_text16   = GetFunction(SQ3\DLL, "sqlite3_column_text16")
    SQ3\column_type     = GetFunction(SQ3\DLL, "sqlite3_column_type")
    SQ3\PeekMode = #PB_UTF8
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteClose(hDB.l)
  If Not hDB : ProcedureReturn #False : EndIf
  If SQ3\close
    If Not SQ3\Close(hDB)
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure 

Procedure.l SQLiteOpen(FileName.s, AsciiMode.l = #False)
  Protected hDB.l
  CompilerIf #PB_Compiler_Unicode
  If SQ3\open16
    If Not SQ3\open16(FileName, @hDB)
      ProcedureReturn hDB
    EndIf
  EndIf
  CompilerElse
  If AsciiMode
    SQ3\IsASCII = #True
    SQ3\PeekMode = #PB_Ascii
  EndIf
  If SQ3\open
    If Not SQ3\open(FileName, @hDB)
      ProcedureReturn hDB
    EndIf
  EndIf
  CompilerEndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteErrorCode(hDB.l)
  If SQ3\errcode
    ProcedureReturn SQ3\errcode(hDB)
  EndIf
  ProcedureReturn -1
EndProcedure

Procedure.s SQLiteErrorMsg(hDB.l)
  If Not hDB : ProcedureReturn "Database handle missing" : EndIf
  CompilerIf #PB_Compiler_Unicode
  If SQ3\errmsg16
    ProcedureReturn PeekS(SQ3\errmsg16(hDB))
  EndIf
  CompilerElse
  If SQ3\errmsg
    ProcedureReturn PeekS(SQ3\errmsg(hDB))
  EndIf
  CompilerEndIf
  ProcedureReturn ""
EndProcedure


Procedure.s SQLiteLibversion()
  If SQ3\libversion
    ProcedureReturn PeekS(SQ3\libversion(), #PB_Any, #PB_Ascii)
  EndIf
EndProcedure

Procedure.l SQLiteExecute(hDB.l, Statement.s, callback.l = 0, cbpara.l = 0)
  If Not hDB : ProcedureReturn #False : EndIf
  If SQ3\IsASCII
    If SQ3\exec_a
      If Not SQ3\exec_a(hDB, Statement, callback, cbpara)
        ProcedureReturn #True
      EndIf
    EndIf
  Else
    If SQ3\exec
      If Not SQ3\exec(hDB, Statement, callback, cbpara)
        ProcedureReturn #True
      EndIf
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure 

Procedure.l SQLiteLastInsertRowId(hDB.l)
  If Not hDB : ProcedureReturn #False : EndIf
  If SQ3\last_insert_rowid
    ProcedureReturn SQ3\last_insert_rowid(hDB)
  EndIf
  ProcedureReturn #False
EndProcedure 

; table-functions
Procedure.l SQliteFreeTable(*table.SQ3_TABLEMAP)
  If SQ3\free_table And *table
    SQ3\free_table(*table\Table)
    ProcedureReturn #True
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteGetTable(hDB.l, Statement.s, *table.SQ3_TABLEMAP)
  Protected nRow.l, nColumn.l, lResultPtr.l, result.l
  If Not hDB : ProcedureReturn #False : EndIf ; <<<
  If SQ3\get_table
    If SQ3\IsASCII
      result = SQ3\get_table_a(hDB, Statement, @lResultPtr, @nRow, @nColumn)
    Else
      result = SQ3\get_table(hDB, Statement, @lResultPtr, @nRow, @nColumn)
    EndIf
    If Not result
      *table\Table = lResultPtr
      *table\Rows = nRow
      *table\Cols = nColumn
      *table\RowPos = 0
      *table\ColPos = 0
      ProcedureReturn *table
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

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

Procedure.l 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.l SQLiteSelectRow(*Table.SQ3_TABLEMAP, Row.l)
  If *Table
    If Row > 0 And Row <= *Table\Rows
      *Table\RowPos = Row
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

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

Procedure.l SQLiteSelectPos(*Table.SQ3_TABLEMAP, Row.l, Col.l)
  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.l 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.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

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

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

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

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

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

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

; binding-functions
Procedure.l SQLitePrepare(hDB.l, Statement.s); Result is lStatement
  Protected Result.l
  CompilerIf #PB_Compiler_Unicode
    If SQ3\prepare16
      If Not SQ3\prepare16(hDB, Statement, -1, @Result)
        ProcedureReturn Result
      EndIf
    EndIf
  CompilerElse
    If SQ3\prepare
      If SQ3\IsASCII
        If Not SQ3\prepare_a(hDB, Statement, -1, @Result)
          ProcedureReturn Result
        EndIf
      Else
        If Not SQ3\prepare(hDB, Statement, -1, @Result)
          ProcedureReturn Result
        EndIf
      EndIf
    EndIf
  CompilerEndIf
EndProcedure

Procedure.l SQLiteFinalize(lStatement.l)
  If SQ3\finalize
    If Not SQ3\finalize(lStatement)
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteStep(lStatement.l)
  If SQ3\step_
    If SQ3\step_(lStatement) = #SQLITE3_ROW
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteBindBlob(lStatement.l, lIndex.l, lBuffer.l, lBufferLength.l)
  If SQ3\bind_blob
    If Not SQ3\bind_blob(lStatement, lIndex, lBuffer, lBufferLength)
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteBindDouble(lStatement.l, lIndex.l, dValue.d)
  If SQ3\bind_double
    If Not SQ3\bind_double(lStatement, lIndex, dValue)
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteBindLong(lStatement.l, lIndex.l, lValue.l)
  If SQ3\bind_int
    If Not SQ3\bind_int(lStatement, lIndex, lValue)
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteBindQuad(lStatement.l, lIndex.l, qValue.q)
  If SQ3\bind_int64
    If Not SQ3\bind_int64(lStatement, lIndex, qValue)
      ProcedureReturn #True
    EndIf
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteBindText(lStatement.l, lIndex.l, Text.s)
  CompilerIf #PB_Compiler_Unicode
    If SQ3\bind_text16
      If Not SQ3\bind_text16(lStatement, lIndex, @Text)
        ProcedureReturn #True
      EndIf
    EndIf
  CompilerElse
    If SQ3\bind_text
      If Not SQ3\bind_text(lStatement, lIndex, @Text)
        ProcedureReturn #True
      EndIf
    EndIf
  CompilerEndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteColumnBlob(lStatement.l, lColumn.l)
  If SQ3\column_blob
    ProcedureReturn SQ3\column_blob(lStatement, lColumn)
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteColumnByte(lStatement.l, lColumn.l)
  CompilerIf #PB_Compiler_Unicode
    If SQ3\column_bytes16
      ProcedureReturn SQ3\column_bytes16(lStatement, lColumn)
    EndIf
  CompilerElse
    If SQ3\column_bytes
      ProcedureReturn SQ3\column_bytes(lStatement, lColumn)
    EndIf
  CompilerEndIf
  ProcedureReturn #False
EndProcedure

Procedure.d SQLiteColumnDouble(lStatement.l, lColumn.l)
  If SQ3\column_double
    ProcedureReturn SQ3\column_double(lStatement, lColumn)
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.l SQLiteColumnLong(lStatement.l, lColumn.l)
  If SQ3\column_int
    ProcedureReturn SQ3\column_int(lStatement, lColumn)
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.q SQLiteColumnQuad(lStatement.l, lColumn.l)
  If SQ3\column_int64
    ProcedureReturn SQ3\column_int64(lStatement, lColumn)
  EndIf
  ProcedureReturn #False
EndProcedure

Procedure.s SQLiteColumnText(lStatement.l, lColumn.l)
  CompilerIf #PB_Compiler_Unicode
    If SQ3\column_text16
      ProcedureReturn PeekS(SQ3\column_text16(lStatement, lColumn))
    EndIf
  CompilerElse
    If SQ3\column_text
      ProcedureReturn PeekS(SQ3\column_text(lStatement, lColumn))
    EndIf
  CompilerEndIf
  ProcedureReturn ""
EndProcedure

Procedure.l SQLiteColumnType(lStatement.l, lColumn.l)
  If SQ3\column_type
    ProcedureReturn SQ3\column_type(lStatement, lColumn)
  EndIf
  ProcedureReturn #False
EndProcedure
Last update: 07.08.2007
Last edited by ts-soft on Tue Aug 07, 2007 6:15 pm, edited 4 times 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
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 »

Here are two examples for using Images as blob
Tested with ASCII + Unicode in WinXP and XUbuntu

First, wie create a simple database with only a blob field:

Code: Select all

EnableExplicit

XIncludeFile "SQLite3_Include.pbi"

Define.l Mem, File, Size, hDB, lStatement

If SQLiteInit() = #False
  Debug "Can't initialisize SQLite3"
  End
EndIf

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)

hDB = SQLiteOpen("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

SQLiteClose(hDB)
SQLiteEnd()
FreeMemory(Mem)
we extract the image and write it as logo.bmp to disk

Code: Select all

EnableExplicit

XIncludeFile "SQLite3_Include.pbi"

Define.l Mem, File, Size, hDB, lStatement

If SQLiteInit() = #False
  Debug "Can't initialisize SQLite3"
  End
EndIf

hDB = SQLiteOpen("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
  EndIf
  SQLiteFinalize(lStatement)
EndIf

SQLiteClose(hDB)
SQLiteEnd()
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
Post Reply

Return to “Tricks 'n' Tips”