Code: Alles auswählen
UseSQLiteDatabase()
Procedure CheckDatabaseUpdate(Database, Query$)
Result = DatabaseUpdate(Database, Query$)
If Result = 0
Debug DatabaseError()
EndIf
ProcedureReturn Result
EndProcedure
DatabaseFile$ = GetTemporaryDirectory()+"Database.sqlite"
If CreateFile(0, DatabaseFile$)
CloseFile(0)
If OpenDatabase(0, DatabaseFile$, "", "")
CheckDatabaseUpdate(0, "CREATE TABLE sportler (id int, swid int, aktiv int, adresseId int)")
CheckDatabaseUpdate(0, "CREATE TABLE adresse (id int, vorname text, name text)")
CheckDatabaseUpdate(0, "CREATE TABLE meldung (id int, sportlerId int, wettkampfId int)")
CheckDatabaseUpdate(0, "CREATE TABLE wettkampf (id int, veranstaltung text)")
CheckDatabaseUpdate(0, "INSERT INTO adresse (id, vorname, name) VALUES ('1', 'olli', 'humpi')")
CheckDatabaseUpdate(0, "INSERT INTO adresse (id, vorname, name) VALUES ('2', 'hubi', 'bubi')")
CheckDatabaseUpdate(0, "INSERT INTO adresse (id, vorname, name) VALUES ('3', 'popi', 'hobi')")
CheckDatabaseUpdate(0, "INSERT INTO sportler (id, aktiv, adresseId) VALUES ('1', '1', '1')")
CheckDatabaseUpdate(0, "INSERT INTO sportler (id, aktiv, adresseId) VALUES ('2', '0', '2')")
CheckDatabaseUpdate(0, "INSERT INTO sportler (id, aktiv, adresseId) VALUES ('3', '1', '3')")
CheckDatabaseUpdate(0, "INSERT INTO wettkampf (id, veranstaltung) VALUES (1, 'laufen')")
CheckDatabaseUpdate(0, "INSERT INTO wettkampf (id, veranstaltung) VALUES (2, 'essen')")
CheckDatabaseUpdate(0, "INSERT INTO meldung (id, sportlerId, wettkampfId) VALUES ('1', '1', '1')")
CheckDatabaseUpdate(0, "INSERT INTO meldung (id, sportlerId, wettkampfId) VALUES ('2', '2', '2')")
q.s = ""
q + "select *, count(meldung.id) as countMeldung from ";sportler.*, adresse.vorname, adresse.name from "
q + " sportler "
q + " left join meldung on meldung.sportlerId = sportler.id "
q + " left join wettkampf on wettkampf.id = meldung.wettkampfId "
q + ",adresse "
q + " where "
q + " sportler.aktiv = 1 "
q + " and sportler.adresseId = adresse.id "
q + " group by sportler.id "
If DatabaseQuery(0, q)
cols = DatabaseColumns(0)
s.s = ""
For i=0 To cols-1
s + " | " + LSet(DatabaseColumnName(0, i), 12, " ")
Next
Debug s
While NextDatabaseRow(0)
s.s = ""
For i=0 To cols-1
s + " | " + LSet(GetDatabaseString(0, i), 12 ," ")
Next
Debug s
Wend
FinishDatabaseQuery(0)
EndIf
CloseDatabase(0)
Else
Debug "Can't open database !"
EndIf
Else
Debug "Can't create the database file !"
EndIf