Page 3 of 9
Posted: Thu May 31, 2007 7:53 am
by michel
Hello Thomas,
Thank you very much for your answer, I will try this solution this evening
Michel
Re: More examples
Posted: Thu May 31, 2007 1:00 pm
by ts-soft
Fangbeast wrote: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
Yikes!
Posted: Thu May 31, 2007 1:32 pm
by 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!!!
Posted: Thu May 31, 2007 1:46 pm
by Dare
What fangs said!
Great. Thanks!
Posted: Thu May 31, 2007 4:32 pm
by 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
Posted: Thu May 31, 2007 8:40 pm
by michel
Hello Thomas,
It works!! Thank you for your solution.
My previous message is wrong
Michel
Posted: Thu May 31, 2007 8:51 pm
by ts-soft
michel wrote: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
Posted: Tue Aug 07, 2007 12:26 am
by 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
Posted: Tue Aug 07, 2007 8:21 am
by ts-soft
Thorsten1867 wrote: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)
Posted: Tue Aug 07, 2007 12:58 pm
by 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
Posted: Tue Aug 07, 2007 1:53 pm
by 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
Posted: Tue Aug 07, 2007 2:20 pm
by ABBKlaus
i would use Freak´s idea to hunt down memory problems :
http://www.purebasic.fr/english/viewtop ... 460#203460
Posted: Tue Aug 07, 2007 2:40 pm
by Thorsten1867
ts-soft wrote: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]
Posted: Tue Aug 07, 2007 2:47 pm
by 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.
Posted: Tue Aug 07, 2007 4:10 pm
by 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