PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

SQLite3 BaseFunction-Include for Win + Lin + Unicode

https://www.purebasic.fr/english/viewtopic.php?t=27036

Page 1 of 9

SQLite3 BaseFunction-Include for Win + Lin + Unicode

Posted: Sun May 13, 2007 1:14 pm
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()

Posted: Sun May 13, 2007 1:29 pm
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.

Posted: Sun May 13, 2007 1:45 pm
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

Posted: Sun May 13, 2007 1:52 pm
by srod
Now that's what I call a prompt service! :)

Thanks.

Posted: Sun May 13, 2007 2:29 pm
by ts-soft
Added optional parameter callback for SQLiteExecute()
thx to pdwyer :wink:

Posted: Sun May 13, 2007 4:42 pm
by netmaestro
Fabulous work ts, thanks for sharing it 8)

Posted: Mon May 14, 2007 8:21 am
by Progi1984
Tested with the last version of SQLite ?

Thanks you for your job !

Posted: Mon May 14, 2007 3:34 pm
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.

Posted: Mon May 14, 2007 3:54 pm
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.

Posted: Mon May 14, 2007 9:27 pm
by Psychophanta
Thank you 8)

... and for Mac!

Posted: Mon May 14, 2007 9:47 pm
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

Posted: Tue May 15, 2007 9:58 am
by Num3
Yeah...

Purebasic is coming closer and closer to a fully mature and heavy weight competition to anything out there!!!

Posted: Tue May 15, 2007 10:20 am
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!

Posted: Tue May 15, 2007 12:37 pm
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

Posted: Tue May 15, 2007 5:46 pm
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()
All times are UTC+01:00
Page 1 of 9

Powered by phpBB® Forum Software © phpBB Limited