PureBasic is simply emulating the process structure of the underlying SQLite engine, which works on a similar query/fetch cycle. Assignments have to be done manually, according to the schemas and data types, which should be known beforehand. Here's a simple POC:
Code: Select all
UseSQLiteDatabase()
; custom structure according to database table structure
Structure people
id.i
name.s
address.s
telephone.s
EndStructure
#sqLite = 0
#mainWin = 0
#dataList = 0
; create array to hold retrieved record-sets
Dim dataSet.people(0)
; create sample in-memory database
Procedure createSampleDatabase()
Protected dbError = #False
If OpenDatabase(#sqLite, ":memory:", "", "")
If DatabaseUpdate(#sqLite, "CREATE TABLE people (id INTEGER PRIMARY KEY, " +
"name CHAR(100), address CHAR(200), telephone CHAR(50))")
Restore people
Dim queries.s(7)
Define insertQuery.s = "INSERT INTO people (name, address, telephone) VALUES ("
For i = 0 To 7
Read.s name$
Read.s address$
Read.s telephone$
queries(i) = insertQuery + "'" + name$ + "','" + address$ + "','" + telephone$ + "')"
Next
DatabaseUpdate(#sqLite, "BEGIN")
For i = 0 To 7
If Not DatabaseUpdate(#sqLite, queries(i))
AddGadgetItem(#dataList, -1, "error inserting data!")
dbError = #True
Break
EndIf
Next i
DatabaseUpdate(#sqLite, "COMMIT")
EndIf
; in-memory database must remain open after creation
; CloseDatabase(#sqLite) ; will be lost if closed
EndIf
If dbError
AddGadgetItem(#dataList, -1, "error creating in-memory database!")
EndIf
ProcedureReturn Bool(dbError = #False)
EndProcedure
; read database table into structure array
Procedure readTable(tableName.s)
Shared dataSet()
Protected tableCount, dbError = #True
; in-memory database must remain open after creation
If IsDatabase(#sqLite) ; instead of OpenDatabase(#sqLite...
If DatabaseQuery(#sqLite, "SELECT COUNT (*) FROM people")
NextDatabaseRow(#sqLite)
tableCount = GetDatabaseLong(#sqLite, 0)
FinishDatabaseQuery(#sqLite)
; re-size structure array according to table count
ReDim dataSet(tableCount - 1)
If DatabaseQuery(#sqLite, "SELECT * FROM people")
While NextDatabaseRow(#sqLite)
dataSet(i)\name = GetDatabaseString(#sqLite, 1)
dataSet(i)\address = GetDatabaseString(#sqLite, 2)
dataSet(i)\telephone = GetDatabaseString(#sqLite, 3)
i + 1
Wend
FinishDatabaseQuery(#sqLite)
CloseDatabase(#sqLite)
dbError = #False
EndIf
EndIf
EndIf
If dbError
AddGadgetItem(#dataList, -1, "error retrieving record!")
EndIf
ProcedureReturn Bool(dbError = #False)
EndProcedure
; display retrieved record-sets
Procedure displayTableRecords()
Shared dataSet()
For i = 0 To 7
With dataSet(i)
AddGadgetItem(0, -1, \name + ", " + \address + ", " + \telephone)
EndWith
Next i
EndProcedure
wFlags = #PB_Window_SystemMenu | #PB_Window_ScreenCentered
OpenWindow(#mainWin, 0, 0, 500, 300, "Database Example", wFlags)
ListViewGadget(#dataList, 10, 10, 480, 280)
If createSampleDatabase()
If readTable("")
displayTableRecords()
EndIf
EndIf
While WaitWindowEvent() ! #PB_Event_CloseWindow : Wend
; sample data for database
DataSection
people:
Data.s "Clark Kent", "1, DC Lane, Planet Krypton.", "5551234",
"Bruce Wayne", "2, DC Avenue, Planet Earth.", "5552345",
"Diana Prince", "3, Paradise Island, Planet Earth.", "5553456",
"David Banner", "4, Marvel Drive, Planet Earth.", "5554567",
"Peter Parker", "5, Marvel Boulevard, Planet Earth.", "5555678",
"Steve Rogers", "6, Marvel Heights, Planet Earth.", "5556789",
"Peter Parker", "11, Marvel Boulevard, Planet Earth.", "5555678",
"Steven Rogers", "6, Marvel Heights, Planet Earth.", "5556789"
EndDataSection