SQLite3 BaseFunction-Include for Win + Lin + Unicode

[michel]

Hello Thomas,

Thank you very much for your answer, I will try this solution this evening

Michel

[ts-soft]

Thomas, I can't see how to use blobs in a complex table from the example, feeling pretty dumb at the moment.

Could you combine your example that stores 5 records with your example that stores blobs so there are blobs in every record and show how to select a specific record including the blob for that record?

Sorry to bother you.

Sorry, only 3 records
Create:

Code: Select all

XIncludeFile "SQLite3_Include.pbi"
EnableExplicit

Procedure AddAdress(hDB.l, Name.s, FirstName.s, PicFile.s)
  Protected File.l = ReadFile(#PB_Any, PicFile)
  Protected Mem.l, Size.l, lStatement.l, last
  Size = Lof(File)
  If Size And File
    Mem = AllocateMemory(Size)
    If Mem
      ReadData(File, Mem, Size)
      CloseFile(File)
    EndIf
  EndIf
  SQLiteExecute(hDB, "Begin Transaction")
  SQLiteExecute(hDB, "INSERT INTO tblAdress (Name, FirstName) VALUES ('" + Name + "','" + FirstName + "')")
  last = SQLiteLastInsertRowId(hDB)
  lStatement = SQLitePrepare(hDB, "INSERT INTO tblPicture (AdrID, Picture) VALUES (?,?)")  
  If lStatement
    SQLiteBindLong(lStatement, 1, last)
    SQLiteBindBlob(lStatement, 2, Mem, Size)
    SQLiteStep(lStatement)
    SQLiteFinalize(lStatement)    
  Else
    Debug SQLiteErrorMsg(hDB)
  EndIf
  FreeMemory(Mem)
  SQLiteExecute(hDB, "Commit")
EndProcedure

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

Define.l hDB = SQLiteOpen("adress")

If hDB
  ; create table
  If SQliteExecute(hDB, "CREATE TABLE IF NOT EXISTS tblAdress (ID INTEGER Not NULL PRIMARY key, Name Text, FirstName Text)") = #False
    Debug SQLiteErrorMsg(hDB)
  EndIf
  If SQliteExecute(hDB, "CREATE TABLE IF NOT EXISTS tblPicture (ID INTEGER Not NULL PRIMARY key, AdrID Integer, Picture Blob)") = #False
    Debug SQLiteErrorMsg(hDB)
  EndIf  
  AddAdress(hDB, "Kent", "Clark", #PB_Compiler_Home + "examples/sources/data/PureBasicLogo.bmp")
  AddAdress(hDB, "Parker", "Peter", #PB_Compiler_Home + "examples/sources/data/Geebee2.bmp")
  AddAdress(hDB, "Wayne", "Bruce", #PB_Compiler_Home + "examples/sources/data/PureBasic.bmp")
  
  SQLiteClose(hDB)
EndIf

Show:

Code: Select all

EnableExplicit

XIncludeFile "SQLite3_Include.pbi"

Structure Adress
  ID.l
  Name.s
  FirstName.s
  Picture.l
EndStructure

NewList Adress.Adress()

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


Define.l hDB = SQLiteOpen("adress")
Define.SQ3_TABLEMAP Table
Define.l lStatement, Size, Mem

If hDB

  SQliteGetTable(hDB, "SELECT * FROM tblAdress", Table)
  While SQLiteNextRow(Table)
    AddElement(Adress())
    SQLiteSelectCol(Table, 1)
    Adress()\ID = Val(SQLiteValue(Table))
    SQLiteSelectCol(Table, 2)
    Adress()\Name = SQLiteValue(Table)
    SQLiteSelectCol(Table, 3)
    Adress()\FirstName = SQLiteValue(Table)
  Wend
  SQliteFreeTable(Table)

  ForEach Adress()
    lStatement = SQLitePrepare(hDB, "Select Picture From tblPicture Where AdrID = " + Str(Adress()\ID))
    If lStatement
      SQLiteStep(lStatement)
      Size = SQLiteColumnByte(lStatement, 0)
      Mem = SQLiteColumnBlob(lStatement, 0)
      If Mem
        Adress()\Picture = CatchImage(#PB_Any, Mem, Size)
      EndIf
      SQLiteFinalize(lStatement)
    EndIf
  Next
  SQLiteClose(hDB)
EndIf

ForEach Adress()
  If OpenWindow(0, #PB_Ignore, #PB_Ignore, 400, 200, Adress()\FirstName + " " + Adress()\Name)
    CreateGadgetList(WindowID(0))
    ImageGadget(0, 0, 0, 400, 200, ImageID(Adress()\Picture))
    While WaitWindowEvent() <> #PB_Event_CloseWindow : Wend
  EndIf
Next

[Fangbeast]

Great steaming CatFish Thomas, I never would have worked that out. Thank you for those examples, now all I have to do is find a couple of unpickled braincells to use with this.

Thank yoU!!!

[Dare]

What fangs said!

Great. Thanks!

[michel]

Hello Thomas,

I just tested your modified procedures with the example how to do it but unfortunately nothing happens with the db; it stays in the same state.

Michel

[michel]

Hello Thomas,

It works!! Thank you for your solution.
My previous message is wrong

Michel

[ts-soft]

Hello Thomas,

It works!! Thank you for your solution.
My previous message is wrong

Michel

This is SQL, a minimal mistake and all is wrong

[Thorsten1867]

I get every time an 'Invalid memory access':

Code: Select all

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) ; <<< Invalid memory access
    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

[ts-soft]

I get every time an 'Invalid memory access':

Code: Select all

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) ; <<< Invalid memory access
    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

Where is the source? (your Source, this source works)

[Thorsten1867]

If I extract the procedure from the program, it works fine.
But if I use it in the program (29000 lines ) it causes an error.

Code: Select all

XIncludeFile "E:\Entwicklung\KvGS_V410\SQLite3_Include.pbi" ; SQLite-Funktionen

; ===== SQLite initalisieren =====
If Not SQLiteInit() ;{ SQLite initalisieren
  MessageRequester("SQLite - Fehler", "Konnte SQLite3 nicht initalisieren.")
  End
EndIf ;}

Procedure.l SQLiteOpenDatabase(dbfile.s) ; SQLite-Datenbank öffnen
  Protected hDB.l = #False
  If FileSize(dbfile) > -1 ; Datenbank vorhanden
    hDB = SQLiteOpen(dbfile)
  ElseIf FileSize(GetPathPart(dbfile)+GetFileNamePart(dbfile)+".kvs") = -1 ; Neue Datenbank
    hDB = SQLiteOpen(dbfile)
  EndIf
  ProcedureReturn hDB
EndProcedure

Procedure.b SQLiteCreateTable(hDB.l, Table.s)
  Protected sql.s
  ; --- Tabelle erstellen (SQL) ---
  Select Table
    Case "schuljahr"
      sql = "CREATE TABLE schuljahr (id INTEGER PRIMARY KEY, sj TEXT);"
    Case "klassen"
      sql = "CREATE TABLE klassen (id INTEGER PRIMARY KEY, sj TEXT, klasse TEXT, lehrer TEXT, lges TEXT, passw TEXT);"
    Case "schueler"
      sql = "CREATE TABLE schueler (sid INTEGER PRIMARY KEY, vname TEXT, vname2 TEXT, name TEXT, gebtag TEXT, gebort TEXT, ges TEXT, staat TEXT, bek TEXT, rel TEXT, jgang TEXT, str TEXT, ort TEXT, tel TEXT, tel2 TEXT, tel3 TEXT, email TEXT, erzb1 TEXT, erzb2 TEXT, bem TEXT, kl TEXT);" 
    Default ; Tabelle nicht gefunden
      ProcedureReturn #False  
  EndSelect
  ; --- SQL-Statment ausführen ---
  Debug "Erzeuge neue Tabelle für '"+Table+"'"
  ProcedureReturn SQLiteExecute(hDB, sql)
EndProcedure

Procedure.w ListeSJ(hDB.l) ; Liste Schuljahre (Combobox) -> AuswahlKlasseFenster()
  Debug "ListeSJ("+Str(hDB)+", "+Str(GID)+")"
  Define.SQ3_TABLEMAP TableSJ
  Define.l Rows = #Null
  ;ClearGadgetItemList(GID)
  ; --- Tabelle laden ---
  sql.s = "Select sj From schuljahr ORDER BY sj DESC"
  SQLiteGetTable(hDB, sql, TableSJ)
  If Not TableSJ ;{ Table 'schuljahr' laden
    If SQLiteCreateTable(hDB, "schuljahr")  ; Table neu erstellen
      SQLiteGetTable(hDB, sql, TableSJ) ; Table Nochmal  laden
    EndIf
  EndIf ;}
  ; --- Tabelle auslesen ---
  If TableSJ
    While SQLiteNextRow(TableSJ)
      Rows + 1
      Debug SQLiteColValue(TableSJ, 1)
      ;AddGadgetItem(GID, -1, SQLiteColValue(TableSJ, 1))
    Wend
    SQliteFreeTable(TableSJ)
  Else ; SQLite Error
    MessageRequester(" SQLite3: ListeSJ() ", SQLiteErrorMsg(hDB))
  EndIf
  ProcedureReturn Rows
EndProcedure

[ts-soft]

If the function works in a 100 line code and in a 20000 line code not, so i
think there is a bug in your code. Or in other words: What should i do?

Can't help, sry

[ABBKlaus]

i would use Freak´s idea to hunt down memory problems :

http://www.purebasic.fr/english/viewtop ... 460#203460

[Thorsten1867]

If the function works in a 100 line code and in a 20000 line code not, so i
think there is a bug in your code. Or in other words: What should i do?

Can't help, sry

[German]
Ich weiß, hast du aber irgendeine Idee, wonach ich suchen kann?
Mit PBOSL_SQLite3 lief das Programm?!?
[/German]

[ts-soft]

Check the Database with a SQLite Browser/Manager. If the Database created
with PBOSL_SQLite it can't work. You have to convert the Database to UTF-8
You can found some infos in this thread to convert the Database.

The PBOL_SQLite uses only ANSI, this is only while PB3.94 UTF-8 not
supported, but UTF-8 is the stringformat for SQLite.

[Thorsten1867]

I found it! (wrong database handle)
I've changed some procedures. Now it seems more bulletproof.

Code: Select all

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

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