Die Datenfilterung mittels der GUI ist bereits bei der Dateneingabe in Tabellen beschrieben. Hier soll eine Lösung aufgezeigt werden, die bei vielen Nutzern gefragt ist: Mittels Listenfeldern werden Inhalte von Tabellenfeldern ausgesucht, die dann im darunterliegenden Formularteil herausgefiltert erscheinen und bearbeitet werden können.

Grundlage für diese Filterung ist neben einer bearbeitbaren Abfrage (siehe das Kapitel «Eingabemöglichkeit in Abfragen») eine weitere Tabelle, in der die zu filternden Daten abgespeichert werden. Die Abfrage zeigt aus der ihr zugrundeliegenden Tabelle nur die Datensätze an, die dem eingegebenen Filterwert entsprechen. Ist kein Filterwert angegeben, so zeigt die Abfrage alle Datensätze an.

Für das folgenden Beispiel wird von einer Tabelle "Medien" ausgegangen, die unter anderem die folgenden Felder beinhaltet: "ID" (Primärschlüssel), "Titel", "Kategorie".

Zuerst wird eine Tabelle "Filter" benötigt. Diese Tabelle erhält einen Primärschlüssel und 2 Filterfelder (das kann natürlich beliebig erweitert werden): "ID" (Primärschlüssel), "Filter_1", "Filter_2". Da die Felder der Tabelle "Medien", die gefiltert werden sollen, vom Typ VARCHAR sind, haben auch die Felder "Filter_1" und "Filter_2" diesen Typ. "ID" kann ein Ja/Nein-Feld sein. Die Tabelle "Filter" wird sowieso nur einen Datensatz abspeichern.

• Tipp 

Natürlich kann auch nach Feldern gefiltert werden, die in der Tabelle "Medien" nur über einen Fremdschlüssel vertreten sind. Dann müssen die entsprechenden Felder in der Tabelle "Filter" natürlich dem Typ des Fremdschlüssels entsprechen, in der Regel also «Integer» sein.

Folgende Abfrageform bleibt sicher editierbar:

1. 001 SELECT * FROM "Medien" 

Alle Datensätze der Tabelle "Medien" werden angezeigt, auch der Primärschlüssel.

1. 001 SELECT * FROM "Medien"  

2. 002 WHERE "Titel" = COALESCE(  

3. 003    ( SELECT "Filter_1" FROM "Filter" WHERE "ID" = TRUE), "Titel" ) 

Ist das Feld "Filter_1" nicht NULL, so werden die Datensätze angezeigt, bei denen der "Titel" gleich dem "Filter_1" ist. Wenn das Feld "Filter_1" NULL ist wird stattdessen der Wert des Feldes "Titel" genommen. Da "Titel" gleich "Titel" ist, werden so alle Datensätze angezeigt – sollte angenommen werden, trifft aber nicht zu, wenn im Feld "Titel" irgendwo ein leeres Feld 'NULL' enthalten ist. Das bedeutet, dass die Datensätze nie angezeigt werden, die keinen Titeleintrag haben. Hier muss in der Abfrage nachgebessert werden.

1. 001 SELECT * ,  

2. 002    COALESCE( "Titel", '' ) AS "T"  

3. 003 FROM "Medien"  

4. 004 WHERE "T" = COALESCE(  

5. 005    ( SELECT "Filter_1" FROM "Filter"  WHERE "ID" = TRUE), "T" ) 

Diese Variante würde zum Ziel führen. Statt "Titel" direkt zu filtern, wird ein Feld gefiltert, das den Alias-Namen "T" erhält. Dieses Feld ist zwar weiter ohne Inhalt, aber eben nicht NULL. In der Bedingung wird nur auf dieses Feld "T" Bezug genommen. Alle Datensätze werden angezeigt, auch wenn "Titel" NULL sein sollte.

Leider spielt hier die GUI nicht mit. Der Befehl ist nur direkt über SQL absetzbar. Um ihn mit der GUI editierbar zu machen, ist weitere Handarbeit erforderlich:

1. 001 SELECT "Medien".* ,  

2. 002    COALESCE( "Medien"."Titel", '' ) AS "T"  

3. 003 FROM "Medien"  

4. 004 WHERE "T" = COALESCE(  

5. 005    ( SELECT "Filter_1" FROM "Filter"  WHERE "ID" = TRUE), "T" ) 

Wenn jetzt der Tabellenbezug zu den Feldern hergestellt ist, ist die Abfrage auch in der GUI editierbar.

Zum Testen kann jetzt einfach ein Titel in "Filter"."Filter_1" eingegeben werden. Als "Filter"."ID" wird der Wert '0' gesetzt. Der Datensatz wird abgespeichert und die Filterung kann nachvollzogen werden. Wird "Filter"."Filter_1" wieder geleert, so macht die GUI daraus NULL. Ein erneuter Test ergibt, dass jetzt wieder alle Medien angezeigt werden. Bevor ein Formular erstellt und getestet wird, sollte auf jeden Fall ein Datensatz, aber wirklich nur einer, mit einem Primärschlüssel in der Tabelle "Filter" stehen. Nur ein Datensatz darf es sein, da Unterabfragen wie oben gezeigt nur einen Wert wiedergeben dürfen.

Die Abfrage wird jetzt erweitert, um auch ein 2. Feld zu filtern:

1. 001 SELECT "Medien".* ,  

2. 002    COALESCE( "Medien"."Titel", '' ) AS "T",  

3. 003    COALESCE( "Medien"."Kategorie", '' ) AS "K"  

4. 004 FROM "Medien"  

5. 005 WHERE "T" = COALESCE(  

6. 006    ( SELECT "Filter_1" FROM "Filter" WHERE "ID" = TRUE), "T" )  

7. 007    AND "K" = COALESCE(  

8. 008    ( SELECT "Filter_2" FROM "Filter" WHERE "ID" = TRUE), "K" ) 

Damit ist die Erstellung der editierbaren Abfrage abgeschlossen. Jetzt wird noch die Grundlage für die beiden Listenfelder als Abfrage zusammengestellt:

1. 001 SELECT DISTINCT "Titel", "Titel"  

2. 002 FROM "Medien" ORDER BY "Titel" ASC 

Das Listenfeld soll sowohl die "Titel" anzeigen als auch die "Titel" an die dem Formular zugrundeliegende Tabelle "Filter" in das Feld "Filter_1" weitergeben. Dabei sollen keine doppelten Werte angezeigt werden ( Anordnung «DISTINCT») . Und das Ganze soll natürlich richtig sortiert erscheinen. Dabei ist die Abfrage an die Standardeinstellung der Listenfelder angepasst, die dem gebundenen Feld eine '1' zuweist. Wird stattdessen unter Eigenschaften Listenfeld → Daten → Gebundenes Feld eine '0' zugewiesen, so braucht nur einmal das Feld "Titel" abgefragt werden.

Eine entsprechende Abfrage wird dann auch für das Feld "Kategorie" erstellt, die ihre Daten in der Tabelle "Filter" in das Feld "Filter_2" schreiben soll.

Handelt es sich bei einem der Felder um ein Fremdschlüsselfeld, so ist die Abfrage entsprechend so anzupassen, dass der Fremdschlüssel an die zugrundeliegende Tabelle "Filter" weitergegeben wird.

Das Formular besteht aus zwei Teilformularen. Formular 1 ist das Formular, dem die Tabelle "Filter" zugrunde liegt. Formular 2 ist das Formular, dem die Abfrage zugrunde liegt. Formular 1 hat keine Navigationsleiste und den Zyklus «Aktueller Datensatz». Die Eigenschaft «Daten hinzufügen» ist außerdem auf «Nein» gestellt. Der erste und einzige Datensatz existiert ja bereits.

Formular 1 enthält 2 Listenfelder mit entsprechenden Überschriften. Listenfeld 1 soll Werte für "Filter_1" liefern und wird mit der Abfrage für das Feld "Titel" versorgt. Listenfeld 2 soll Werte für "Filter_2" weitergeben und beruht auf der Abfrage für das Feld "Kategorie".

Formular 2 enthält ein Tabellenkontrollfeld, in dem alle Felder aus der Abfrage aufgelistet sein können – mit Ausnahme der Felder "T" und "K". Mit den Feldern wäre der Betrieb auch möglich – sie würden aber wegen der doppelten Feldinhalte nur verwirren. Außerdem enthält das Formular 2 noch einen Button, der die Eigenschaft «Formular aktualisieren» hat. Zusätzlich kann noch eine Navigationsleiste eingebaut werden, damit nicht bei jedem Formularwechsel der Bildschirm aufflackert, weil die Navigationsleiste in einem Formular ein-, in dem anderen ausgestellt ist.

Wenn das Formular fertiggestellt ist, geht es zur Testphase. Wird ein Listenfeld geändert, so reicht die Betätigung des Buttons aus dem Formular 2 aus, um zuerst diesen Wert zu speichern und dann das Formular 2 zu aktualisieren. Das Formular 2 bezieht sich jetzt auf den Wert, den das Listenfeld angibt. Die Filterung kann über die Wahl des im Listenfeld enthaltenen leeren Feldes rückgängig gemacht werden.

Der Hauptunterschied zwischen der Suche von Daten und der Filterung von Daten liegt in der Abfragetechnik. Schließlich soll zu frei eingegebenen Begriffen ein Ergebnis geliefert werden, das diese Begriffe auch nur teilweise beinhaltet.

Die Tabelle für die Suchinhalte kann die gleiche sein, in die bereits die Filterwerte eingetragen werden. Die Tabelle "Filter" wird einfach ergänzt um ein Feld mit der Bezeichnung "Suchbegriff". So kann gegebenenfalls auf die gleiche Tabelle zugegriffen werden und in Formularen gleichzeitig gefiltert und gesucht werden. "Suchbegriff" hat die Feldeigenschaft VARCHAR.

Das Formular wird wie bei der Filterung aufgebaut. Statt eines Listenfeldes muss für den Suchbegriff ein Texteingabefeld erstellt werden, zusätzlich vielleicht auch ein Beschriftungsfeld mit dem Titel «Suche». Das Feld für den Suchbegriff kann alleine in dem Formular stehen oder zusammen mit den Feldern für die Filterung, wenn eben beide Funktionen gewünscht sind.

Der Unterschied zwischen Filterung und Suche liegt in der Abfragetechnik. Während die Filterung bereits von einem Begriff ausgeht, den es in der zugrundeliegenden Tabelle gibt (schließlich baut das Listenfeld auf den Tabelleninhalten auf) geht die Suche von einer beliebigen Eingabe aus.

1. 001 SELECT * FROM "Medien"  

2. 002 WHERE "Titel" = ( SELECT "Suchbegriff" FROM "Filter" WHERE "ID" = TRUE) 

Diese Abfrage würde in der Regel ins Leere führen. Das hat mehrere Gründe:

• •Selten weiß jemand bei der Eingabe des Suchbegriffs den kompletten Titel fehlerfrei auswendig. Damit würde der Titel nicht angezeigt. Um das Buch «Per Anhalter durch die Galaxis» zu finden müsste es ausreichen, in das Suchfeld 'Anhalter' einzugeben, vielleicht auch nur 'Anh'. 

• •Ist das Feld "Suchbegriff" leer, so würde überhaupt kein Datensatz angezeigt. Die Abfrage gäbe NULL zurück und NULL kann in einer Bedingung nur mittels IS NULL erscheinen. 

• •Selbst wenn dies ignoriert würde, so würde die Abfrage dazu führen, dass alle die Datensätze angezeigt würden, die keine Eingabe im Feld "Titel" haben. 

Die letzten beiden Bedingungen könnten erfüllt werden, indem wie bei der Filterung vorgegangen würde:

1. 001 SELECT * FROM "Medien"  

2. 002 WHERE "Titel" = COALESCE(  

3. 003    ( SELECT "Suchbegriff" FROM "Filter" WHERE "ID" = TRUE), "Titel" ) 

Mit den entsprechenden Verfeinerungen aus der Filterung (was ist mit Titeln, die NULL sind?) würde das zum entsprechenden Ergebnis führen. Nur würde die erste Bedingung nicht erfüllt. Die Suche lebt ja schließlich davon, dass nur Bruchstücke geliefert werden. Die Abfragetechnik der Wahl müsste daher über den Begriff «LIKE» gehen:

1. 001 SELECT * FROM "Medien" 

2. 002 WHERE "Titel" LIKE  

3. 003    ( SELECT '%' || "Suchbegriff" || '%' FROM "Filter" WHERE "ID" = TRUE) 

oder besser:

1. 001 SELECT * FROM "Medien" 

2. 002 WHERE "Titel" LIKE COALESCE(  

3. 003    ( SELECT '%' || "Suchbegriff" || '%' FROM "Filter" WHERE "ID" = TRUE),  

4. 004    "Titel" ) 

LIKE, gekoppelt mit '%', bedeutet ja, dass alle Datensätze gezeigt werden, die an irgendeiner Stelle den gesuchten Begriff stehen haben. '%' steht als Joker für beliebig viele Zeichen vor und hinter dem Suchbegriff. Verschiedene Baustellen bleiben nach dieser Abfrageversion:

• •Besonders beliebt ist ja, in Suchformularen alles klein zu schreiben. Wie bekomme ich mit 'anhalter' statt 'Anhalter' auch noch ein Ergebnis? 

• •Welche anderen Schreibgewohnheiten gibt es noch, die vielleicht zu berücksichtigen wären? 

• •Wie sieht es mit Feldern aus, die nicht als Textfelder formatiert sind? Lassen sich auch Datumsanzeigen oder Zahlen mit dem gleichen Feld suchen? 

• •Und was ist, wenn, wie bei dem Filter, ausgeschlossen werden muss, dass NULL-Werte in dem Feld verhindern, dass alle Datensätze angezeigt werden? 

Die folgende Variante deckt ein paar mehr Möglichkeiten ab:

1. 001 SELECT * FROM "Medien"  

2. 002 WHERE LOWER("Titel") LIKE COALESCE(  

3. 003    ( SELECT '%' || LOWER("Suchbegriff") || '%'  

4. 004       FROM "Filter" WHERE "ID" = TRUE),  

5. 005    LOWER("Titel") ) 

Die Bedingung ändert den Suchbegriff und den Feldinhalt auf Kleinschreibweise. Damit werden auch ganze Sätze vergleichbar.

1. 001 SELECT * FROM "Medien"  

2. 002 WHERE LOWER("Titel") LIKE COALESCE(  

3. 003    ( SELECT '%' || LOWER("Suchbegriff") || '%'  

4. 004       FROM "Filter" WHERE "ID" = TRUE), LOWER("Titel") )  

5. 005    OR LOWER("Kategorie") LIKE ( SELECT '%' || LOWER("Suchbegriff") || '%'  

6. 006       FROM "Filter" WHERE "ID" = TRUE) 

Die COALESCE-Funktion muss nur einmal vorkommen, da bei dem "Suchbegriff" NULL ja dann LOWER("Titel") LIKE LOWER("Titel") abgefragt wird. Und da der Titel ein Feld sein soll, das nicht NULL sein darf, werden so auf jeden Fall alle Datensätze angezeigt. Für entsprechend viele Felder wird dieser Code natürlich entsprechend lang. Schöner geht so etwas mittels Makro, das dann den Code in einer Schleife über alle Felder erstellt.

Aber funktioniert der Code auch mit Feldern, die keine Textfelder sind? Obwohl die Bedingung LIKE ja eigentlich auf Texte zugeschnitten ist, brauchen Zahlen, Datums- oder Zeitangaben keine Umwandlung, um damit zusammen zu arbeiten. Allerdings können hierbei die Textumwandlungen unterbleiben. Nur wird natürlich ein Zeitfeld auf eine Mischung aus Text und Zahlen nicht mit einer Fundstelle reagieren können – es sei denn die Abfrage wird ausgeweitet, so dass der eine Suchbegriff an jeder Leerstelle unterteilt wird. Dies bläht allerdings die Abfrage noch wieder deutlich auf.

• Tipp 

Die Abfragen, die zur Filterung und zum Durchsuchen von Daten genutzt werden, lassen sich auch direkt in ein Formular einbauen.
Die gesamten obigen Bedingungen sind bei den Formular-Eigenschaften in der Zeile Filter eintragbar. Aus
SELECT * FROM "Medien" WHERE "Titel" = COALESCE( ( SELECT "Suchbegriff" FROM "Filter" WHERE "ID" = TRUE), "Titel" )
wird dann ein Formular, das als Inhalt die Tabelle "Medien" nutzt.
Unter «Filter» steht dann
("Medien"."Titel" = COALESCE( ( SELECT "Suchbegriff" FROM "Filter" WHERE "ID" = TRUE), "Medien"."Titel" ))
In den Filtereingaben ist darauf zu achten, dass die Bedingung in Klammern gesetzt wird und jeweils mit der Angabe "Tabelle"."Feld" arbeitet.
Vorteil dieser Variante ist, dass der Filter bei geöffnetem Formular ein- und wieder ausgeschaltet werden kann.

Die Suche mit LIKE ist in der Regel völlig ausreichend für Datenbanken mit Feldern, die Text in überschaubarem Maße enthalten. Was aber, wenn der Inhalt über Memo-Felder eingegeben wird, also ohne weiteres auch einmal mehrere Seiten Text enthalten kann? Dann geht die Suche erst einmal los, wo denn nun der Text zu finden ist.

Um Text genau zu finden, gibt es in der Hsqldb die Funktion LOCATE. LOCATE erwartet einen Suchbegriff sowie den Text, der durchsucht werden soll, als Parameter. Zusätzlich kann noch angegeben werden, ab welcher Position gesucht werden soll. Kurz also: LOCATE(Suchbegriff, Textfeld aus der Datenbank, Startposition der Suche).

In Firebird gibt es die Funktion LOCATE nicht. Hier muss auf POSITION zurückgegriffen werden: POSITION(Suchbegriff, Textfeld aus der Datenbank, Startposition der Suche). Die Startposition kann hier eingegeben werden, muss es aber nicht.

Auch die im weiteren verwendete Funktion SUBSTRING muss für Firebird in einer andern Syntax geschrieben werden. Statt SUBSTRING(Text,Startporition[,Länge]) ist dort SUBSTRING(Text FROM Startposition [ FOR Länge]) zu verwenden.

Die Datenbank benötigt eine Tabelle, die mindestens die folgenden Voraussetzungen erfüllt:

• Hinweis 

Bei Verwendung der internen Firebird Datenbank sollte nicht der Feldtyp Bild sondern der Feldtyp BLOB gewählt werden. Nur mit diesem Feldtyp werden Bilder auch im Formular angezeigt.

Beim Primärschlüssel ist natürlich nicht unbedingt der Integer-Feldtyp bestimmend. Ein Primärschlüssel ist aber unabdingbar. Andere Felder, die zumindest Informationen zu dem Bild enthalten, sollten noch hinzugefügt werden.

Daten, die irgendwann in das Bild-Feld eingetragen werden, sind in den Tabellen nicht lesbar. Dort erscheint als Anzeige nur <OBJECT>. Entsprechend sind Bilder auch nicht direkt in die Tabelle eingebbar. Sie müssen über ein Formular und dort mit Hilfe des grafischen Kontrollfeldes eingegeben werden. Das grafische Kontrollfeld öffnet beim Mausklick auf das Feld eine Dateiauswahl. Es zeigt hinterher das Bild an, das über die Dateiauswahl in die Datenbank eingelesen wurde.

Bilder, die direkt in die Datenbank eingefügt werden, sollten möglichst klein sein. Da Base ohne den Einsatz von Makros auch keine Möglichkeit bietet, die Bilder in Originalgröße wieder aus der Datenbank heraus zu befördern, macht es aus dieser Warte erst einmal Sinn, als Maßstab für die Größe z.B. einen möglichen Ausdruck im Bericht anzusehen. Originalbilder im Mega­pixelbereich sind hier völlig unnötig und blähen die Datenbank stark auf. Bereits nach wenigen Bildern meldet bei der internen HSQLDB Base eine Java.NullPointerException und kann den Datensatz nicht mehr speichern. Auch wenn die Bilder nicht ganz so groß sind, kann es irgendwann passieren, dass die Datenbankdatei nicht mehr bedienbar wird.

Bilder sollten außerdem möglichst nicht in Tabellen integriert werden, die als Suchgrundlage gedacht werden. Wird z.B. in einer Datenbank zur Personenverwaltung auch das Passbild mit abgespeichert, so ist es besser in einer separaten Tabelle über einen Fremdschlüssel mit der Haupttabelle verbunden. Die Suche in der Haupttabelle kann dann deutlich schneller erfolgen, da die Tabelle selbst nicht so viel Speicher beansprucht.

Mit einer entsprechend durchdachten Ordnerstruktur ist es günstiger, direkt auf die Dateien von außerhalb zuzugreifen. Dateien außerhalb der Datenbank können beliebig groß sein, ohne dass die Funktionen der Datenbank selbst beeinflusst werden. Leider bedeutet dies aber auch, dass eine Umbenennung von Ordnern auf dem eigenen Rechner oder im Internet dazu führt, dass der Zugriff auf die entsprechenden Dateien verloren geht.

• Hinweis 

Sollen die Bilder später über den Report-Designer ausgelesen werden, so dürfen die Dateinamen keine Sonderzeichen wie [ ] { } \ < > % " und Leerzeichen enthalten. Ein Bericht mit diesen Bildern wird nicht erstellt.

Um Bilder nicht in eine Datenbankdatei einzulesen, sondern nur zu verknüpfen, bedarf es nur einer kleinen Änderung gegenüber der vorhergehenden Tabelle:

Wird einfach statt des Feldtyps Bild der Feldtyp Text gewählt, so wird über das grafische Kontrollfeld der Pfad zu dem Bild eingetragen. Das Bild kann über das Kontrollfeld genauso betrachtet werden wie ein Bild, das in die Datenbank eingefügt wurde.

• Hinweis 

Seit LO 5.0 können, abhängig von der verwendeten Benutzeroberfläche, auch andere Dateien über das grafische Kontrollfeld eingebunden werden. Hier funktionierte das mit gtk3 allerdings erst ab der Version LO 6.3. Für diese Versionen ist der Einsatz einer Dateiauswahl also weitgehend überflüssig.
Bei *.pdf-Dateien wird die erste Seite in dem grafischen Kontrollfeld als Bild angezeigt.

Bei einem Bild kann über den Pfad wenigstens noch der Inhalt auf dem grafischen Kontrollfeld gelesen werden. Bei einem Dokument (mit Ausnahme des *.pdf-Dokumentes) kann aber keine Anzeige erfolgen, selbst wenn der Pfad in der Tabelle verzeichnet ist. Zuerst ist deshalb die Tabelle etwas zu erweitern, damit wenigstens ein geringfügiges Maß an Information zu dem Dokument sichtbar wird.

Damit der Pfad zur Datei sichtbar wird, muss in dem Formular ein Dateiauswahlfeld mit eingebaut werden.

Ein Dateiauswahlfeld hat in seinen Eigenschaften keinen Reiter für Daten, ist also auch nicht mit irgendeinem Feld der dem Formular zugrundeliegenden Tabelle verbunden.

Über das Dateiauswahlfeld kann zwar der Pfad angezeigt, aber nicht gespeichert werden. Hierfür ist eine gesonderte Prozedur erforderlich, die über Ereignisse → Text modifiziert ausgelöst wird:

1. 001 SUB Pfad_Einlesen(oEvent AS OBJECT) 

2. 002    DIM oForm AS OBJECT 

3. 003    DIM oFeld AS OBJECT 

4. 004    DIM oFeld2 AS OBJECT 

5. 005    DIM stUrl AS STRING 

6. 006    oFeld = oEvent.Source.Model 

7. 007    oForm = oFeld.Parent 

8. 008    oFeld2 = oForm.getByName("GraphischesFeld") 

9. 009    IF oFeld.Text <> "" THEN 

10. 010       stUrl = ConvertToUrl(oFeld.Text) 

11. 011       oFeld2.BoundField.updateString(stUrl) 

12. 012    END IF 

13. 013 END SUB 

Das auslösende Ereignis wird beim Aufruf der Prozedur mitgeliefert und hilft dabei, das Formular und auch das Feld zu finden, in dem der Pfad gespeichert werden soll. Mit oEvent AS OBJECT ist der Zugriff vor allem dann einfacher, wenn ein anderer User ein gleichlautendes Makro in irgendeinem Unterformular verwenden will. Das Dateiauswahlfeld ist von dort aus über oEvent.Source.Model zu erreichen. Das Formular ist Parent (Elternteil) zu dem Dateiauswahlfeld. Der Name des Formulars spielt also keine Rolle. Vom Formular aus geht dann der Zugriff auf das Feld mit dem Namen «GraphischesFeld». Über dieses Feld werden die Dateipfade der Bilder abgespeichert. Jetzt wird in das Feld die URL der ausgesuchten Datei geschrieben. Damit die URL betriebssystemunabhängig verwendet werden kann, wird der in dem Dateiauswahlfeld stehende Text vorher über ConvertToUrl in eine allgemeingültige URL-Schreibweise überführt.

In der Tabelle der Datenbank steht jetzt ein Pfad mit absoluter Schreibweise: file:///... .

Werden allerdings über ein graphisches Kontrollfeld Pfadeingaben eingelesen, so erfolgt die Aufnahme in relativer Pfadangabe. Dies ist von Vorteil, wenn die Datenbank in ein anderes System übertragen werden soll. So können z.B. in einem Unterverzeichnis der Datenbankdatei alle Bilder liegen und die Verbindung dazu wird auch auf anderen Rechner gefunden. Um dies mit der Dateiauswahl zu bewerkstelligen, müsste nachgebessert werden. Die Prozedur für diesen Zweck sieht wesentlich umfangreicher aus, da ein Vergleich der Pfadeingabe mit der aktuellen Speicherposition der Datenbankdatei nötig ist.

Verknüpfte Bilder können direkt in dem kleinen graphischen Kontrollfeld angezeigt werden. Besser wäre aber ein größere Anzeige, um auch Details erkennen zu können.

Dokumente lassen sich standardmäßig in Base überhaupt nicht anzeigen.

Bei dem obigen Verfahren wurde kurz erwähnt, dass der Name der Datei bei der Eingabe über das graphische Kontrollfeld so nicht ermittelt werden kann. Hier jetzt kurz ein Makro zur Ermittlung des Dateinamens, das zum obigen Formular passt. Der Dateiname lässt sich nicht sicher durch ein Ereignis ermitteln, das mit dem grafischen Kontrollfeld direkt verbunden ist. Deswegen wird das Makro über Formular-Eigenschaften → Ereignisse → Vor der Datensatzaktion gestartet.

1. 001 SUB BildnamenAuslesen(oEvent AS OBJECT) 

2. 002    oForm = oEvent.Source 

3. 003    IF InStr(oForm.ImplementationName, "ODatabaseForm") THEN 

4. 004       oFeld = oForm.getByName("GraphischesFeld") 

5. 005       oFeld2 = oForm.getByName("DateiName") 

6. 006       IF oFeld.ImageUrl <> "" THEN 

7. 007          stUrl = ConvertToUrl(oFeld.ImageUrl) 

8. 008          ar = split(stUrl,"/") 

9. 009          stName = ar(UBound(ar)) 

10. 010          oFeld2.BoundField.updateString(stName) 

11. 011       END IF 

12. 012    END IF 

13. 013 END SUB 

Vor der Datensatzaktion werden zwei Implementationen mit unterschiedlichem Implementationsnamen ausgeführt. Das Formular ist am einfachsten über die Implementation erreichbar, das in seinem Namen als ODatabaseForm bezeichnet wird.

In dem graphischen Kontrollfeld ist die URL der Datenquelle über die ImageUrl erreichbar. Diese URL wird ausgelesen, der Dateinamen wie in der vorhergehenden Prozedur «DateiEinlesen_mitName» ausgelesen und in das Feld «DateiName» übertragen.

Wird nach dem Ablauf des obigen Makros zum nächsten Datensatz gewechselt, so ist der Pfad zu dem ursprünglichen Bild weiter vorhanden. Würde jetzt eine allgemeine Datei über das Dateiauswahlfeld eingelesen, so würde der Name der Datei ohne das folgende Makro einfach durch den Namen der zuletzt eingelesenen Bilddatei überschrieben.

Der Pfad kann leider nicht mit dem vorhergehenden Makro entfernt werden, da das Einlesen der Bilddatei erst beim Abspeichern erfolgt. Eine Entfernung des Pfades vor der Abspeicherung löscht das Bild.

Das Makro wird über Formular-Eigenschaften → Ereignisse → Nach der Datensatzaktion gestartet.

1. 001 SUB BildnamenZuruecksetzen(oEvent AS OBJECT) 

2. 002    oForm = oEvent.Source 

3. 003    IF InStr(oForm.ImplementationName, "ODatabaseForm") THEN 

4. 004       oFeld = oForm.getByName("GraphischesFeld") 

5. 005       IF oFeld.ImageUrl <> "" THEN 

6. 006          oFeld.ImageUrl = "" 

7. 007       END IF 

8. 008    END IF 

9. 009 END SUB 

Es wird, wie in der Prozedur «BildnamenAuslesen», auf das graphische Kontrollfeld zugegriffen. Existiert dort noch ein Eintrag in der ImageUrl, dann wird dieser geleert.

Für Dateien wie für die Bilder in Originalgröße gilt, dass der Button Datei mit externem Programm betrachten ausgelöst werden muss. Dann werden die Dateien in das temporäre Verzeichnis ausgelesen und anschließend über das mit der Endung verknüpfte Programm des Betriebssystems angezeigt.

Das Makro wird über Eigenschaften: Schaltfläche → Ereignisse → Aktion ausführen gestartet.

1. 001 SUB DateiAuslesen_mitName(oEvent AS OBJECT) 

2. 002    DIM oForm AS OBJECT 

3. 003    DIM oFeld AS OBJECT 

4. 004    DIM oFeld2 AS OBJECT 

5. 005    DIM oStream AS OBJECT 

6. 006    DIM oShell AS OBJECT 

7. 007    DIM oPath AS OBJECT 

8. 008    DIM oSimpleFileAccess AS OBJECT 

9. 009    DIM stName AS STRING 

10. 010    DIM stPfad AS STRING 

11. 011    DIM stFeld AS STRING 

12. 012    oForm = oEvent.Source.Model.Parent 

13. 013    oFeld = oForm.getByName("GraphischesFeld") 

14. 014    oFeld2 = oForm.getByName("DateiName") 

15. 015    stName = oFeld2.Text 

16. 016    IF stName = "" THEN 

17. 017       stName = "Datei" 

18. 018    END IF 

19. 019    oStream = oFeld.BoundField.getBinaryStream 

20. 020    oPath = createUnoService("com.sun.star.util.PathSettings") 

21. 021    stPfad = oPath.Temp & "/" & stName 

22. 022    oSimpleFileAccess = createUnoService("com.sun.star.ucb.SimpleFileAccess") 

23. 023    oSimpleFileAccess.writeFile(stPfad, oStream) 

24. 024    oShell = createUnoService("com.sun.star.system.SystemShellExecute") 

25. 025    stFeld = convertToUrl(stPfad) 

26. 026    oShell.execute(stFeld,,0) 

27. 027 END SUB 

Die Lage der anderen betroffenen Felder im Formular wird abhängig von dem auslösenden Button ermittelt. Fehlt ein Dateiname, so wird der ausgelesenen Datei einfach der Name «Datei» zugeschrieben.

Der Inhalt des Formularfeldes «GraphischesFeld» entspricht dem Inhalt, der in dem Feld "Datei" der Tabelle liegt. Er wird als Datenstrom ausgelesen. Als Pfad für die ausgelesene Datei wird der Pfad zum temporären Verzeichnis genutzt, der in LibreOffice über Extras → Optionen → LibreOffice → Pfade eingestellt werden kann. Soll also die Datei anschließend noch anderweitig verwendet und nicht nur angezeigt werden, so ist sie aus diesem Pfad heraus auch kopierbar. Innerhalb des Makros wird die Datei direkt nach dem erfolgreichen Auslesen mit dem Programm geöffnet, das in der grafischen Benutzeroberfläche des Betriebssystems mit dem Dateityp verbunden ist.

Das Einbinden von Diagrammen in Formulare ist von der GUI her nicht vorgesehen. Mit einem Umweg über Writer, etwas Makrohilfe und allgemein verarbeitbaren Ansichten lässt sich so ein Diagramm dennoch in ein Formular integrieren und darüber hinaus beständig an die momentane Datenlage anpassen.

Zuerst wird ein neues Writer-Dokument erstellt: Datei → Neu → Textdokument. Anschließend wird über die Symbolleiste oder über Einfügen → Objekt → Diagramm ein Diagramm erstellt. Standardmäßig wird hier zuerst einmal ein Säulendiagramm in das Dokument eingefügt.

Das Diagramm wird kopiert und in ein Formular der Datenbank eingefügt. Alle weiteren Einstellungen des Diagramms können auch im Base-Formular über das Kontextmenü vorgenommen werden.

Daten für das Diagramm werden später automatisch eingelesen. Es ist also nicht notwendig, hier irgendeine Voreinstellung vorzunehmen.

Für die Darstellung der verschiedenen Diagrammtypen werden Zeilenbeschreibungen (RowDescriptions), Spaltenbeschreibungen (ColumnDescriptions) und Daten (Data) benötigt. Um Probleme bei der Darstellung von XY-Diagrammen zu umgehen, wird zusätzlich eine Information zum Diagrammtyp in den Abfragen dargestellt.

Würde von vornherein sichergestellt, dass nur ein Eintrag pro Monat erstellt würde und das Diagramm nur nach allen Einträgen für den Monat aktualisiert werden soll, so könnte für ein Säulendiagramm einfach die Tabelle direkt abgefragt werden:

1. 001 SELECT "Kategorie", "Datum", "Betrag", 'BC' AS "Type" FROM "Kategorien"  

Die Kategorien stellen die Zeilenbeschriftungen dar. Die Zeilenbeschriftungen erscheinen unter den Säulen des Diagramms. Für jeden Monat wird eine neue Säule dargestellt. Die Monatsbezeichnungen erscheinen beim Säulendiagramm als Spaltenbeschriftungen rechts vom Diagramm in der Legende. Der Betrag gibt als Dezimalzahl die Höhe der jeweiligen Säule an. Die Information zum Diagrammtypen wird später nur für das XY-Diagramm separat ausgewertet. 'BC' steht hier schlicht für «bar chart» (Säulendiagramm).

Damit auch mehrere Einträge pro Monat angefertigt werden können, sind die Einträge für alle gleichen Kategorien innerhalb eines Monats zusammen zu fassen. Die Darstellung des Monats soll in dem Diagramm z.B. als «Monat 2016/02» erfolgen. Außerdem sollen die Einträge in dem Diagramm auch dann aktualisiert werden, wenn für eine Kategorie eventuell noch gar kein Monatseintrag existiert.

Um all dies zu bewerkstelligen, wird zuerst einmal eine Kombination aller Kategorien mit allen in der Tabelle "Kategorien" vorhandenen Monaten erstellt:

1. 001 SELECT  

2. 002    "a"."Kategorie",  

3. 003    "b"."Monat" 

4. 004 FROM "Kategorien" AS "a",  

5. 005    ( SELECT 'Monat ' || YEAR( "Datum" ) || '/' || RIGHT( '0'  

6. 006       || MONTH( "Datum" ), 2 ) AS "Monat" FROM "Kategorien" ) AS "b"  

7. 007 ORDER BY "Kategorie", "Monat" 

Diese Konstruktion kann allerdings den Datenbestand deutlich aufblähen. Hat die Tabelle "Kategorien" 5 Einträge, so werden bei dieser Abfrage 5 Einträge zu "Kategorie" mit 5 Einträgen zu der Monatsdarstellung kombiniert. Es entstehen zwangsläufig 25 Einträgen, von denen dann gewiss viele Einträge mehrfach vorkommen.

• Hinweis 

Für Firebird muss statt MONTH("Datum") EXTRACT(MONTH FROM "Datum") usw. eingefügt werden. Firebird kennt die hier verwendeten Kurzformen nicht.

Mit Hilfe des Zusatzes DISTINCT kann die Ausgabe der Einträge eingeschränkt werden. Dies sollte möglichst schon bei den Datenquellen für die Abfrage ansetzen, damit nicht unnötig erst Daten geladen und wieder aus dem Speicher entfernt werden. Deshalb wird auf die Tabelle "Kategorien" zweimal mit einer Abfrage und dem Zusatz DISTINCT zugegriffen:

1. 001 SELECT  

2. 002    "a"."Kategorie",  

3. 003    "b"."Monat" 

4. 004 FROM ( SELECT DISTINCT "Kategorie" FROM "Kategorien" ) AS "a",  

5. 005    ( SELECT DISTINCT 'Monat ' || YEAR( "Datum" ) || '/' || RIGHT( '0'  

6. 006       || MONTH( "Datum" ), 2 ) AS "Monat" FROM "Kategorien" ) AS "b"  

7. 007 ORDER BY "Kategorie", "Monat" 

Mit Hilfe einer korrelierenden Unterabfrage wird jetzt zu jeder Kombination von "Monat" und "Kategorie" der entsprechende "Betrag" aufsummiert. So werden auch mehrere Einträge im Monat bei der Darstellung im Diagramm berücksichtigt:

1. 001 SELECT 

2. 002    "a"."Kategorie" AS "RowDescription",  

3. 003    "b"."Monat" AS "ColumnDescription",  

4. 004    ( SELECT SUM( "Betrag" ) FROM "Kategorien" WHERE 'Monat ' ||  

5. 005       YEAR( "Datum" ) || '/' || RIGHT( '0' || MONTH( "Datum" ), 2 ) =  

6. 006       "b"."Monat" AND "Kategorie" = "a"."Kategorie" ) AS "Data",  

7. 007    'BC' AS "Type"  

8. 008 FROM ( SELECT DISTINCT "Kategorie" FROM "Kategorien" ) AS "a",  

9. 009    ( SELECT DISTINCT 'Monat ' || YEAR( "Datum" ) || '/' || RIGHT( '0'  

10. 010       || MONTH( "Datum" ), 2 ) AS "Monat" FROM "Kategorien" ) AS "b"  

11. 011 ORDER BY "Kategorie", "Monat" 

Diese Abfrage wird abgespeichert, mit der rechten Maustaste angeklickt und über Abfrage → Als Ansicht erstellen unter der Bezeichnung «Chart_Kategorie» als Ansicht gespeichert.

Für die Darstellung eines Kreisdiagramms ist es erforderlich, dass statt der einzelnen Monatsdarstellungen die Summe über alle Monate erstellt wird. Hier wird der Einfachheit halber direkt auf die eben erstellte Ansicht zugegriffen:

1. 001 SELECT  

2. 002    "RowDescription",  

3. 003    '' AS "ColumnDescription",  

4. 004    SUM( "Data" ) AS "Data",  

5. 005    'CC' AS "Type"  

6. 006 FROM "Chart_Kategorie"  

7. 007 GROUP BY "RowDescription" 

Nach der "Kategorie", hier unter dem Alias "RowDescription", wird die Abfrage gruppiert. Für das Feld "Data" wird die Summe des Feldes "Data" aus der Ansicht "Chart_Kategorie", bezogen auf diese Gruppierung, erstellt.

Eine Spaltenbeschreibung ist nicht erforderlich und wird mit einem leeren Text versehen. Als Typ des Diagramms wird 'CC' für «circle chart» (Kreisdiagramm) angegeben.

Die Abfrage wird abgespeichert, mit der rechten Maustaste angeklickt und über Abfrage → Als Ansicht erstellen unter der Bezeichnung «Chart_Kategorie_Circle» als Ansicht gespeichert.

Daten für ein XY-Diagramm liegen in der Regel in anderer Weise vor als für Säulendiagramme oder Kreisdiagramme. Statt einen Wert in einer Zeile zu speichern, werden hier Wertepaare gespeichert. Damit dennoch auf die Daten mit der gleichen Makrokonstruktion zugegriffen werden kann, ist eine besondere Abfragekonstruktion vorgesehen, die wieder in der ersten Spalte die Zeilenbeschreibung, in der zweiten Spalte die Spaltenbeschreibung, in der dritten Spalte die Daten und in der vierten Spalte den Diagrammtyp wiedergeben kann.

1. 001 SELECT  

2. 002    "Zeit" AS "RowDescription",  

3. 003    '' AS "ColumnDescription",  

4. 004    "Zeit" AS "Data",  

5. 005    'XY' AS "Type"  

6. 006 FROM "Temperaturverlauf" 

7. 007 UNION 

8. 008 SELECT  

9. 009    "Zeit" AS "RowDescription",  

10. 010    'Temperatur [°C]' AS "ColumnDescription",  

11. 011    "Temperatur" AS "Data",  

12. 012    'XY' AS "Type"  

13. 013 FROM "Temperaturverlauf" 

14. 014 ORDER BY "RowDescription" ASC, "ColumnDescription" ASC 

Zuerst werden alle Zeiten ausgelesen. Das Ergebnis dieser Abfrage wird mit der Folgeabfrage über UNION kombiniert, die jetzt alle Temperaturdaten ausliest. Leider funktioniert diese Kombination nicht wie gewünscht, da die Zeit einen anderen Datentypen hat als die Temperatur. Es kommt noch hinzu, dass mit dem Datentypen für die Zeit leider auch keine kontinuierliche Darstellung in einem XY-Diagramm möglich ist. Aus der Zeit muss durch Umformung ein Dezimalwert erstellt werden. Dies erfolgt, indem der Tag als Grundmaß angesehen wird. Stunden, Minuten und Sekunden werden als Bruchteile des Tages errechnet und addiert.

1. 001 SELECT  

2. 002    HOUR( "Zeit" ) / 24.00000 + MINUTE( "Zeit" ) / 1440.00000 +  

3. 003       SECOND( "Zeit" ) / 86400.00000 AS "RowDescription",  

4. 004    '' AS "ColumnDescription",  

5. 005    HOUR( "Zeit" ) / 24.00000 + MINUTE( "Zeit" ) / 1440.00000 +  

6. 006       SECOND( "Zeit" ) / 86400.00000 AS "Data",  

7. 007    'XY' AS "Type"  

8. 008 FROM "Temperaturverlauf" 

9. 009 UNION 

10. 010 SELECT  

11. 011    HOUR( "Zeit" ) / 24.00000 + MINUTE( "Zeit" ) / 1440.00000 +  

12. 012       SECOND( "Zeit" ) / 86400.00000 AS "RowDescription",  

13. 013    'Temperatur [°C]' AS "ColumnDescription",  

14. 014    "Temperatur" AS "Data",  

15. 015    'XY' AS "Type"  

16. 016 FROM "Temperaturverlauf" 

17. 017 ORDER BY "RowDescription" ASC, "ColumnDescription" ASC 

• Hinweis 

Für Firebird muss statt HOUR( "Zeit" ) / 24.00000 + MINUTE( "Zeit" ) / 1440.00000 +SECOND( "Zeit" ) / 86400.00000 einfach ("Zeit" – TIME '00:00')/86400.00000 eingefügt werden. Firebird kennt die hier verwendeten Kurzformen nicht, kann aber Datumswerte und Zeitwerte voneinander subtrahieren.

Die Zeilenbeschriftung "RowDescription" wird für das XY-Diagramm gar nicht benötigt. Hier stehen schließlich die Daten der X-Achse. Diese Spalte wird über die zu einer Dezimalzahl umformatierten Inhalte dazu genutzt, die Zeilen in der korrekten Reihenfolge zu sortieren.

Das Diagramm stellt lediglich die Werte der Y-Achse als 'Temperatur [°C]' dar. Deswegen wird für die Zeilen, die jetzt die Zeitangaben als Daten wiedergeben, eine leere Spaltenbeschriftung "ColumnDescription" ausgegeben.

Der gesamte Inhalt wird nach den Zeiten in "RowDescription" und den Einträgen in "ColumnDescription" sortiert, so dass die Werte für X- und Y-Achse direkt aufeinander folgen und durch gleiche "RowDescription"-Werte einander zugeordnet werden können.

Um dem Makro mitteilen zu können, welche Ansicht in dem jeweiligen Diagramm dargestellt werden soll, wird in den Formularen ein verstecktes Steuerelement eingebaut. Über den Formularnavigator wird das Formular, das als Auslöser des Diagramms genutzt werden soll, mit der rechten Maustaste angeklickt. Im Kontextmenü wird Neu → Verstecktes Steuerelement aufgerufen.

Das Element wird über das Makro mit dem Namen 'Chart' gesucht. Entsprechend wird das versteckte Steuerelement erst einmal umbenannt: rechte Maustaste → Kontextmenü → Umbenennen. Anschließend wird in dem Steuerelement über rechte Maustaste → Kontextmenü → Eigenschaften → Allgemein → Zusatzinformation der Name für die Ansicht vermerkt.

Jetzt ist nur noch notwendig, das folgende Makro mit den Ereignissen des Formulars Beim Laden bzw. Nach der Datensatzaktion zu verknüpfen.

1. 001 SUB ChangeData(oEvent AS OBJECT) 

2. 002    DIM oDiag AS OBJECT 

3. 003    DIM oDatasource AS OBJECT 

4. 004    DIM oConnection AS OBJECT 

5. 005    DIM oSQL_Command AS OBJECT 

6. 006    DIM oResult AS OBJECT 

7. 007    DIM oForm AS OBJECT 

8. 008    DIM oHiddenControl AS OBJECT 

9. 009    DIM stSql AS STRING 

10. 010    DIM stRow AS STRING 

11. 011    DIM stType AS STRING 

12. 012    DIM i AS INTEGER 

13. 013    DIM k AS INTEGER 

14. 014    DIM x AS INTEGER 

15. 015    DIM n AS INTEGER 

16. 016    DIM aNewData(0) 

17. 017    DIM aNewRowDescription(0) 

18. 018    DIM aTmp() AS DOUBLE 

19. 019    DIM aNewColumnDescription() 

20. 020    DIM aType() 

21. 021    DIM arView() 

22. 022    DIM arDiag() 

23. 023    oForm = oEvent.Source 

24. 024    oHiddenControl = oForm.getByName("Chart") 

25. 025    arView = Split(oHiddenControl.Tag,",") 

26. 026    arDiag = Split(oHiddenControl.HiddenValue,",") 

27. 027    FOR n = LBound(arView()) TO UBound(arView()) 

28. 028       stView = oHiddenControl.Tag 

29. 029       stSql = "SELECT * FROM """+arView(n)+"""" 

30. 030       oDatasource = ThisComponent.Parent.CurrentController 

31. 031       IF NOT (oDatasource.isConnected()) THEN 

32. 032          oDatasource.connect() 

33. 033       END IF 

34. 034       oConnection = oDatasource.ActiveConnection() 

35. 035       oSQL_Command = oConnection.createStatement() 

36. 036       oResult = oSQL_Command.executeQuery(stSql) 

37. 037       i = 0 

38. 038       k = 0 

39. 039       x = 0 

40. 040       WHILE oResult.next 

41. 041          stRow = oResult.getString(1) 

42. 042          stType = oResult.getString(4) 

43. 043          IF aNewRowDescription(i) = stRow THEN 

44. 044             ReDim Preserve aNewColumnDescription(k) 

45. 045             ReDim Preserve aTmp(k) 

46. 046          ELSE 

47. 047             IF x > 0 THEN 

48. 048                i = i + 1 

49. 049             ELSE 

50. 050                x = 1 

51. 051             END IF 

52. 052             ReDim Preserve aNewRowDescription(i) 

53. 053             ReDim Preserve aNewData(i) 

54. 054             k = 0 

55. 055             ReDim Preserve aNewColumnDescription(k) 

56. 056             ReDim aTmp(k) 

57. 057          END IF 

58. 058          aNewRowDescription(i) = stRow 

59. 059          aNewColumnDescription(k) = oResult.getString(2) 

60. 060          aTmp(k) = oResult.getDouble(3) 

61. 061          aNewData(i) = aTmp() 

62. 062          k = k + 1 

63. 063       WEND 

64. 064       oDiag = thisComponent.EmbeddedObjects.getByName(arDiag(n)) 

65. 065       oXCOEO = oDiag.ExtendedControlOverEmbeddedObject 

66. 066       oXCOEO.changeState(4) 

67. 067       IF stType <> "XY" THEN 

68. 068          oDiag.model.Data.setData(aNewData) 

69. 069       END IF 

70. 070       oDiag.model.DataProvider.setData(aNewData) 

71. 071       oDiag.model.DataProvider.setRowDescriptions(aNewRowDescription) 

72. 072       oDiag.model.DataProvider.setColumnDescriptions(aNewColumnDescription) 

73. 073       oDiag.Component.setmodified(true) 

74. 074       oDiag.Component.update() 

75. 075       oXCOEO.changeState(1) 

76. 076    NEXT 

77. 077 END SUB 

Nach der Deklaration der Variablen wird zuerst aus den Zusatzinformationen (Tag) des versteckten Kontrollfeldes der Name für die Ansicht ausgelesen. Da hier mehrere Namen für mehrere Diagramme gespeichert werden können werden die Namen durch Komma getrennt und über Split in ein Array eingelesen. Gleiches gilt für die Namen der Diagramme, die im HiddenValue in der entsprechenden Reihenfolge eingetragen sind. Anschließend läuft eine Schleife über das erste Array ab, das ja genau so viele Elemente enthält wie das zweite Array. Der erforderliche SQL-Code zum Auslesen der gesamten Ansicht wird formuliert, die Verbindung zur Datenbank, falls erforderlich, hergestellt und der SQL-Code an die Datenbank weitergeleitet. In oResult wird das Ergebnis der Abfrage gespeichert.

Vor dem Start der Schleife zur Ermittlung des Inhaltes aus oResult werden noch einige Zahlenvariablen auf '0' gesetzt, die im Laufe der Schleife verändert werden sollen.

Innerhalb der Schleife werden die Inhalte der verschiedenen Spalten der Ansicht ausgelesen. Die Inhalte werden in unterschiedliche Arrays zur Weitergabe an den DataProvider abgespeichert. Hier werden wieder Daten (Data), Zeilenbeschriftungen (RowDescriptions) und Spaltenbeschriftungen (ColumnDescriptions) unterschieden.

Mit oResult.getString(1) wird das Ergebnis zum jeweiligen Datensatz aus der ersten Spalte als Text ausgelesen. Hier handelt es sich um Zeilenbeschriftungen, die eben einfach Text sind. Solange die Abfrage nacheinander gleiche Zeilenbeschriftungen liefert, werden alle weitere Inhalte dem gleichen Datenbestand zugeordnet. Um dies zu gewährleisten, erfolgt zuerst eine Abfrage, ob der entsprechende Eintrag von stRow bereits als letzter Eintrag von aNewRowDescriptions() vorhanden ist.

Ist dies nicht der Fall, wie z.B. direkt beim Einlesen des ersten Datensatzes, dann erfolgt das Vorgehen, das unter ELSE beschrieben ist. Nur wenn der Zähler x größer als '0' ist, wird der Zähler für i heraufgesetzt. Dies soll vermeiden, dass der erste Eintrag des zu erzeugenden Arrays später leer ist. Für alle späteren Eintritte in diese Schleife wird allerdings dann x auf '1' gesetzt, so dass i heraufgesetzt wird und die Arrays mit einem weiteren Datensatz beschrieben werden können.

Redim Preserve sichert den bisherigen Inhalt eines Arrays und eröffnet gleichzeitig die Möglichkeit, einen zusätzlichen Eintrag ans Ende des Arrays anzufügen.

aNewData und aNewRowDescriptions werden immer zusammen abgespeichert. Deswegen haben die Arrays den gemeinsamen Zähler i. aNewColumnDescriptions sowie die in einem temporären Array aTmp gespeicherten Dateninhalte werden bei jedem neuen Durchgang durch die WHILE-NEXT–Schleife mit einem zusätzlichen Datensatz versehen. Auch sie haben deshalb einen gemeinsamen Zähler, hier k. Dieser Zähler wird bei jeder Änderung von aNewRowDescriptions neu mit '0' gestartet. Dabei wird das temporäre Array nicht gesichert, da es zwischenzeitig in dem Array aNewData abgespeichert wurde.

Die Inhalte für das Array aTmp werden immer als Dezimalzahlen aus der Abfrage ausgelesen. Daher der Eintrag oResult.getDouble(3).

Damit ein Diagramm im Formular kontinuierlich geändert werden kann, muss es zuerst einmal aktiviert werden. Das Diagramm selbst ist dem Formular sonst völlig unbekannt. Das Diagramm wird über den Namen ausgesucht. Anschließend wird der Controller für das eingebettete Diagramm angesprochen.Zuerst wird mit '4' die UI aktiviert. Die möglichen Parameter sind unter com.sun.star.embed.EmbedStates: LOADED = 0, RUNNING = 1, ACTIVE = 2, INPLACE_ACTIVE = 3, UI_ACTIVE = 4.

Nachdem alle Daten ausgelesen wurden, werden diese in den DataProvider übertragen. Mit oDiag.model.Data.setData(aNewData) werden nicht nur die Daten neu geschrieben, sondern z.B. auch die Anzahl der Säulen in einem Spaltendiagramm aktualisiert. Dieser Eintrag führt allerdings bei einem XY-Diagramm dazu, dass hier die Grundstruktur des Diagramms zerstört und der erste Dateneintrag nicht als X-Achsenwert gesehen wird. Deshalb ist dieser Eintrag für XY-Diagramme ausgeschlossen.

Nach der Änderung wird der Status des Diagramms auf '1' gesetzt, da ansonsten das Diagramm die ganze Zeit im Bearbeitungsmodus erscheint.

Die Codeschnipsel erwachsen aus Anfragen innerhalb von Mailinglisten. Bestimmte Problemstellungen tauchen dort auf, deren Lösungen vielleicht gut innerhalb der eigenen Datenbankentwürfe genutzt werden können.

Aus einem Datum soll mittels Abfrage das aktuelle Alter ermittelt werden. Siehe hierzu die Funktionen im Anhang zu diesem Base-Handbuch.

1. 001 SELECT DATEDIFF('yy',"Geburtsdatum",CURDATE()) AS "Alter" FROM "Person" 

• Hinweis 

Für Firebird muss der Code entsprechend angepasst werden. CURDATE() als Kurzform ist hier unbekannt. Es muss schon CURRENT_DATE sein. Und statt 'yy' muss an dieser Stelle ein String ohne Hochkommata angegeben werden: year.
Auch die Kurzform DAYOFYEAR kennt Firebird nicht. Hier muss wieder EXTRACT(YEARDAY FROM "Geburtsdatum") usw. geschrieben werden.

Die Abfrage gibt das Alter als Jahresdifferenz aus. Das Alter eines Kindes, das am 31.12.2011 geboren ist, wird am 1.1.2012 mit 1 Jahr angegeben. Es muss also die Lage des Tages im Jahr berücksichtigt werden. Dies ist mit der Funktion 'DAYOFYEAR()' ermittelbar. Mittels einer Funktion wird der Vergleich durchgeführt.

1. 001 SELECT CASEWHEN  

2. 002 ( DAYOFYEAR("Geburtsdatum") > DAYOFYEAR(CURDATE()) ,  

3. 003 DATEDIFF ('yy',"Geburtsdatum",CURDATE())-1,  

4. 004 DATEDIFF ('yy',"Geburtsdatum",CURDATE()))  

5. 005 AS "Alter" FROM "Person" 

Jetzt wird das aktuelle Alter in Jahren ausgegeben.

Über 'CASEWHEN' könnte dann auch in einem weiteren Feld der Text 'Heute Geburtstag' ausgegeben werden, wenn DAYOFYEAR("Geburtsdatum") = DAYOFYEAR(CURDATE()).

Spitzfindig könnte jetzt der Einwand kommen: «Wie steht es mit Schaltjahren?». Für Personen, die nach dem 28. Februar geboren wurden, kann es zu Abweichungen um einen Tag kommen. Für den Hausgebrauch nicht weiter schlimm, aber wo bleibt der Ehrgeiz, es möglichst doch genau zu machen?

Mit

1. 001 SELECT CASEWHEN (  

2. 002 (MONTH("Geburtsdatum") > MONTH(CURDATE())) OR  

3. 003    ((MONTH("Geburtsdatum") = MONTH(CURDATE()))  

4. 004    AND (DAY("Geburtsdatum") > DAY(CURDATE()))) , 

5. 005 DATEDIFF('yy',"Geburtsdatum",CURDATE())-1,  

6. 006 DATEDIFF('yy',"Geburtsdatum",CURDATE())) 

7. 007 AS "Alter" FROM "Person" 

wird das Ziel erreicht. Solange der Monat des Geburtsdatums größer ist als der aktuelle Monat wird auf jeden Fall von der Jahresdifferenz 1 Jahr abgezogen. Ebenfalls 1 Jahr abgezogen wird, wenn zwar der Monat gleich ist, der Tag im Monat des Geburtsdatums aber größer ist als der Tag im aktuellen Monat. Leider ist diese Eingabe für die GUI nicht verständlich. Erst 'SQL-Kommando direkt ausführen' lässt die Abfrage erfolgreich absetzen. So ist also unsere Abfrage nicht mehr editierbar. Die Abfrage soll aber weiter editierbar sein; also gilt es die GUI zu überlisten:

1. 001 SELECT CASE  

2. 002 WHEN MONTH("Geburtsdatum") > MONTH(CURDATE())  

3. 003 THEN DATEDIFF('yy',"Geburtsdatum",CURDATE())-1  

4. 004 WHEN (MONTH("Geburtsdatum") = MONTH(CURDATE())  

5. 005    AND DAY("Geburtsdatum") > DAY(CURDATE()))  

6. 006 THEN DATEDIFF('yy',"Geburtsdatum",CURDATE())-1  

7. 007 ELSE DATEDIFF('yy',"Geburtsdatum",CURDATE())  

8. 008 END 

9. 009 AS "Alter" FROM "Person" 

Auf diese Formulierung reagiert die GUI nicht mit einer Fehlermeldung. Das Alter wird jetzt auch in Schaltjahren genau ausgegeben und die Abfrage bleibt editierbar.

Mit Hilfe von einigen kleinen Berechnungskniffen lässt sich auch aus einer Tabelle z.B. auslesen, wer in den nächsten 8 Tagen Geburtstag hat.

1. 001 SELECT * 

2. 002 FROM "Tabelle" 

3. 003 WHERE 

4. 004    DAYOFYEAR("Datum") BETWEEN DAYOFYEAR(CURDATE()) AND  

5. 005       DAYOFYEAR(CURDATE()) + 7 

6. 006    OR DAYOFYEAR("Datum") < 7 -  

7. 007       DAYOFYEAR(CAST(YEAR(CURDATE())||'-12-31' AS DATE)) +  

8. 008       DAYOFYEAR(CURDATE()) 

Die Abfrage zeigt alle Datensätze an, deren Datumseintrag zwischen dem momentanen Jahrestag und den folgenden 7 Tagen liegt.

Damit auch am Jahresende entsprechend 8 Tage angezeigt werden müssen die Anfangstage des Jahres grundsätzlich auch überprüft werden. Diese Überprüfung findet aber nur für die Jahrestage statt, die höchstens den Wert 7 abzüglich des Jahrestages des letzten Datumswertes des aktuellen Jahres (meist 365) zuzüglich des Jahrestages des aktuellen Datumswertes. Liegt das aktuelle Datum mehr als 7 Tage vom Jahresende entfernt, so ist der Gesamtwert also < 1. Kein Eintrag in der Tabelle hat so ein Datum. Diese Teilbedingung wird dann nicht erfüllt.

In der oben stehenden Formulierung werden bei Schaltjahren noch Unstimmigkeiten hervortreten, da sich dann ab dem 29.2. die Jahrestagzählung verschiebt. Der Code, um dieses zu berücksichtigen, gestaltet sich um einiges umfangreicher:

1. 001 SELECT * 

2. 002 FROM "Tabelle" 

3. 003 WHERE 

4. 004       CASE  

5. 005          WHEN  

6. 006             DAYOFYEAR(CAST(YEAR("Datum")||'-12-31' AS DATE)) = 366  

7. 007             AND DAYOFYEAR("Datum") > 60 THEN DAYOFYEAR("Datum") - 1 

8. 008          ELSE  

9. 009             DAYOFYEAR("Datum") 

10. 010       END 

11. 011    BETWEEN  

12. 012       CASE 

13. 013          WHEN  

14. 014             DAYOFYEAR(CAST(YEAR(CURDATE())||'-12-31' AS DATE)) = 366  

15. 015             AND DAYOFYEAR(CURDATE()) > 60 THEN DAYOFYEAR(CURDATE()) - 1 

16. 016          ELSE  

17. 017             DAYOFYEAR(CURDATE()) 

18. 018       END 

19. 019    AND 

20. 020       CASE 

21. 021          WHEN  

22. 022             DAYOFYEAR(CAST(YEAR(CURDATE())||'-12-31' AS DATE)) = 366  

23. 023             AND DAYOFYEAR(CURDATE()) > 60 THEN DAYOFYEAR(CURDATE()) + 6 

24. 024          ELSE  

25. 025             DAYOFYEAR(CURDATE()) + 7 

26. 026       END 

27. 027    OR DAYOFYEAR("Datum") < 7 -  

28. 028       DAYOFYEAR(CAST(YEAR(CURDATE())||'-12-31' AS DATE)) +  

29. 029       DAYOFYEAR(CURDATE()) 

Schaltjahre sind daran zu erkennen, dass das gesamte Jahr 366 und nicht 365 Tage hat. Dies wird für die entsprechenden Unterscheidungen genutzt.

Zum einen muss jeder Datumswert darauf überprüft werden, ob er in einem Schaltjahr liegt und außerdem für die korrekte Zählung der 60. Tag im Jahr ist (31 Tage im Januar und 29 Tage im Februar). Für diesen Fall werden alle darauffolgende DAYOFYEAR – Werte für das Datum um 1 herabgestuft. Dann trifft ein 1.3. eines Schaltjahres wieder exakt auf einen 1.3. eines normalen Jahres.

Zum anderen muss auch beim aktuellen Jahr (CURDATE()) überprüft werden, ob es sich um ein Schaltjahr handelt. Auch hier wird entsprechend die Tageszahl um 1 herabgestuft.

Auch eine Anzeige von Terminen für die nächsten 8 Tage ist so noch nicht einwandfrei möglich, da das Jahr in der Abfrage noch ausgeklammert ist. Dies wäre aber über eine einfache zusätzliche Bedingung YEAR("Datum") = YEAR(CURDATE()) für das aktuelle bzw. YEAR("Datum") = YEAR(CURDATE()) + 1 für das zukünftige Jahr zu erfüllen.

Bei der Ausleihe von Medien möchte vielleicht der Entleiher genau wissen, an welchem Tag denn die Rückgabe des Mediums erfolgen soll. Leider bietet die interne Hsqldb dazu nicht die Funktion DATEADD() an, wie dies in vielen der externen Datenbanken und auch bei Firebird der Fall ist.

• Hinweis 

Für die interne Hsqldb lässt sich für einen begrenzten Zeitraum der folgende Umweg nutzen:

Zuerst wird eine Tabelle mit dem Datumsverlauf über den gewünschten Zeitraum erstellt. Hierzu wird einfach Calc geöffnet, in das Feld A1 die Bezeichnung "ID" und in das Feld B1 die Bezeichnung "Datum" geschrieben. In Feld A2 wird eine 1 eingetragen, in Feld B2 das gewünschte Startdatum, z.B. 1.1.15. A2 und B2 werden markiert und weiter nach unten gezogen, so dass daraus eine fortlaufende Nummer und ein fortlaufendes Datum entstehen.

Anschließend wird diese Tabelle mit den Spaltenüberschriften zusammen markiert und in Base eingefügt: rechte Maustaste → Einfügen → Tabellenname → Datum. Bei den Optionen wird Definition und Daten sowie Erste Zeile als Spaltennamen verwenden angeklickt. Alle Spalten werden übernommen. Anschließend ist nur noch darauf zu achten, dass bei den Typformatierungen das Feld "ID" dem Typ Integer [INTEGER] zugeordnet werden soll und dem Datumsfeld auch der Typ Datum [DATE] zugeordnet wird. Ein Primärschlüssel ist nicht erforderlich, da die Daten später nicht geändert werden sollen. Dadurch, dass eben der Primärschlüssel nicht definiert wird, wird die Tabelle gleichzeitig schreibgeschützt.

• Tipp 

In MySQL gibt es die Funktion TIMESTAMPADD(). Eine vergleichbare Funktion existiert in der Hsqldb nicht. Aber auch hier kann über den internen Zahlenwert, den so ein Zeitstempel einnimmt, mittels eines formatierten Feldes im Formular auch die Addition oder Subtraktion einer Zeit dargestellt werden.

• Hinweis 

Im Gegensatz zur Addition von Tagen zu einem Datum tritt bei den Zeiten allerdings ein Problem auf, das anfangs vielleicht gar nicht auffällt:

1. 001 SELECT "DatumZeit" 

2. 002    DATEDIFF( 'ss', '1899-12-30', "DatumZeit" ) / 86400.0000000000 + 36/24 

3. 003       AS "DatumZeit+36Stunden" 

4. 004 FROM "Tabelle" 

Für die neue Zeitberechnung wird der Unterschied zur Startzeit '0' des Systems genommen. Das ist, wie bei der Datumsberechnung bereits angewandt, der Datumsstempel vom 30.12.1899.

• Hinweis 

Das Nulldatum am 30.12.1899 ist vermutlich deshalb entstanden, weil das Jahr 1900 im Gegensatz zur üblichen 4-Jahres-Rechnung kein Schaltjahr war. So ist der Tag '1' der internen Rechnung auf den 31.12.1899 vorverlegt worden und eben nicht der 1.1.1900.

Der Unterschied wird hier allerdings in Sekunden ausgedrückt. Die interne Zahl rechnet allerdings die Tage als Stellen vor dem Komma, die Stunden, Minuten und Sekunden als Stellen nach dem Komma. Da in einen Tag 60*60*24 Sekunden passen, muss die entsprechend ermittelte Sekundenzahl durch 86400 geteilt werden, um schließlich vor dem Komma mit den entsprechenden Tagen und hinter dem Komma mit den Bruchteilen rechnen zu können. Damit die interne Hsqldb überhaupt Nachkommastellen bei dieser Berechnung ausgibt, müssen diese auch in der Rechnung vorkommen. Statt durch 86400 zu teilen, wurde deshalb durch 86400,0000000000 geteilt. Dezimalstellen erscheinen in der Abfrage durch einen Punkt getrennt. Das Ergebnis hat letztlich also 10 Dezimalstellen hinter dem Komma.

Zu diesem Ergebnis wird die Stundenzahl als Bruchteil eines Tages hinzugezählt. Die berechnete Ziffer lässt sich bei entsprechender Formatierung in der Anfrage darstellen. Dort wird die Formatierung aber leider nicht gespeichert. Innerhalb eines Formulars mit formatierbarem Feld oder innerhalb eines Berichts kann sie aber dauerhaft mit entsprechender Formatierung übernommen werden.

Sollen Minuten oder Sekunden addiert werden, so ist hier auch immer darauf zu achten, dass die Angaben der Minuten und Sekunden als Tagesbruchteile angegeben werden.

Liegt das Datum in den Monaten November, Dezember, Januar usw., dann fällt bei der Rechnung erst einmal nichts auf. Die Darstellung ist stimmig, zum Zeitstempel von 20.01.2015 13:00:00 eine Zeit von 36 Stunden addiert ergibt den neuen (nur dargestellten) Stempel 22.01.2015 01:00:00. Anders verhält es sich bei 20.04.2015 13:00:00. Da wird anschließend der 22.04.2015 00:00:00 ausgegeben. Das liegt an der Sommerzeit, die der Berechnung hier in die Quere kommt. Die gerade bei der Zeitumstellung «verlorene» oder «gewonnene» Stunde lässt sich bei einem Übergang nicht berücksichtigen. Innerhalb einer Zeitzone kann allerdings auf verschiedene Weise eine entsprechende Berechnung mit «korrektem» Ergebnis durchgeführt werden. Hier die dafür einfachere Variante:

1. 001 SELECT "DatumZeit" 

2. 002    DATEDIFF( 'dd', '1899-12-30', "DatumZeit" ) +  

3. 003       HOUR( "DatumZeit" ) / 24.0000000000 +  

4. 004       MINUTE( "DatumZeit" ) / 1440.0000000000 +  

5. 005       SECOND( "DatumZeit" ) / 86400.0000000000 +  

6. 006       36/24  

7. 007          AS "DatumZeit+36Stunden" 

8. 008 FROM "Tabelle" 

Statt die Stunden, Minuten und Sekunden aus dem Ursprungsdatum zu beziehen, werden sie hier im Verhältnis zum aktuellen Datum dargestellt. Am 20.05.2015 steht die Uhr auf 13:00 Uhr, würde aber ohne die Sommerzeit 12:00 Uhr anzeigen. Die Funktion HOUR berücksichtigt die Sommerzeit und gibt 13 Stunden als Stundenanteil aus. Damit kann dann der Stundenanteil korrekt zum Tagesanteil addiert werden. Auf gleiche Art und Weise geschieht dies mit Hilfe des Minutenanteils und des Sekundenanteils. Anschließend werden die zu addierenden Stunden wieder als Tagesbruchteil addiert und das Ganze mit Hilfe der Zellenformatierung als berechneter Zeitstempel ausgegeben.

Zwei Dinge sollten aber bei diesen Berechnungen nie aus den Augen verloren werden:

1. 1.Bei Übergängen von Winterzeit zu Sommerzeit lassen sich die Stundenwerte nicht korrekt berechnen. Hier könnte mit Hilfe einer Zusatztabelle nachgeholfen werden, die Beginn und Ende der Sommerzeiten aufnimmt und dann mit einer Stundenkorrektur einspringt. Ein etwas aufwändiges Verfahren. 

2. 2.Die Ausgabe der Zeitangabe ist nur über formatierbare Felder zu erreichen. Das Ergebnis ist ein Dezimalwert, kein Zeitstempelwert, der z.B. wiederum in einer Datenbank direkt gespeichert werden könnte. Hier müsste entweder innerhalb des Formular kopiert werden oder über aufwändige Abfragen aus dem Dezimalwert zu einem Zeitstempelwert portiert werden. Der Knackpunkt bei dieser Portierung ist der Datumswert, da es eben Schaltjahre und Monate mit unterschiedlicher Tagesanzahl gibt. 

Statt eines Haushaltsbuches wird eine Datenbank im PC die leidigen Aufsummierungen von Ausgaben für Lebensmittel, Kleidung, Mobilität usw. erleichtern. Ein Großteil dieser Angaben sollte natürlich auf Anhieb in der Datenbank sichtbar sein. Dabei wird in dem Beispiel davon ausgegangen, dass Einnahmen und Ausgaben in einem Feld "Betrag" mit Vorzeichen abgespeichert werden. Prinzipiell lässt sich das Ganze natürlich auf getrennte Felder und eine Summierung hierüber erweitern.

1. 001 SELECT "ID", "Betrag",  

2. 002    ( SELECT SUM( "Betrag" ) FROM "Kasse" WHERE "ID" <= "a"."ID" )  

         AS "Saldo" 

3. 003 FROM "Kasse" AS "a" ORDER BY "ID" ASC 

Mit dieser Abfrage wird bei jedem neuen Datensatz direkt ausgerechnet, welcher Kontostand jetzt erreicht wurde. Dabei bleibt die Abfrage editierbar, da das Feld "Saldo" durch eine korrelierende Unterabfrage erstellt wurde. Die Abfrage gibt den Kontostand in Abhängigkeit von dem automatisch erzeugten Primärschlüssel "ID" an. Kontostände werden aber eigentlich täglich ermittelt. Es muss also eine Datumsabfrage her.

1. 001 SELECT "ID", "Datum", "Betrag",  

2. 002    ( SELECT SUM( "Betrag" ) FROM "Kasse" WHERE "Datum" <= "a"."Datum"  

3. 003       ) AS "Saldo"  

4. 004 FROM "Kasse" AS "a" ORDER BY "Datum", "ID" ASC 

Die Ausgabe erfolgt jetzt nach dem Datum sortiert und nach dem Datum summiert. Bleibt noch die Kategorie, nach der entsprechende Salden für die einzelnen Kategorien dargestellt werden sollen.

1. 001 SELECT "ID", "Datum", "Betrag", "Konto_ID",  

2. 002    ( SELECT "Konto" FROM "Konto" WHERE "ID" = "a"."Konto_ID" )  

3. 003       AS "Kontoname",  

4. 004    ( SELECT SUM( "Betrag" ) FROM "Kasse" WHERE "Datum" <= "a"."Datum"  

5. 005       AND "Konto_ID" = "a"."Konto_ID" ) AS "Saldo",  

6. 006    ( SELECT SUM( "Betrag" ) FROM "Kasse" WHERE "Datum" <= "a"."Datum" 

7. 007       ) AS "Saldo_gesamt"  

8. 008 FROM "Kasse" AS "a" ORDER BY "Datum", "ID" ASC 

Hiermit wird eine editierbare Abfrage erzeugt, in der neben den Eingabefeldern (Datum, Betrag, Konto_ID) der Kontoname, der jeweilige Kontostand und der Saldo insgesamt erscheinen. Da sich die korrelierenden Unterabfragen teilweise auch auf vorhergehende Eingaben stützen ("Datum" <= "a"."Datum") werden nur Neueingaben reibungslos dargestellt. Änderungen eines vorhergehenden Datensatzes machen sich zuerst einmal nur in diesem Datensatz bemerkbar. Die Abfrage muss aktualisiert werden, damit auch die davon abhängigen späteren Berechnungen neu durchgeführt werden.

Automatisch hoch zählende Felder sind etwas feines. Nur sagen sie nicht unbedingt etwas darüber aus, wie viele Datensätze denn nun in der Datenbank oder dem Abfrageergebnis wirklich vorhanden sind. Häufig werden Datensätze gelöscht und manch ein User versucht verzweifelt dahinter zu kommen, welche Nummer denn nun nicht mehr vorhanden ist, damit die laufenden Nummern wieder stimmen.

1. 001 SELECT "ID",  

2. 002    ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE "ID" <= "a"."ID" )  

3. 003       AS "lfdNr."  

4. 004 FROM "Tabelle" AS "a" 

Das Feld "ID" wird ausgelesen, im zweiten Feld wird durch eine korrelierende Unterabfrage festgestellt, wie viele Feldinhalte von "ID" kleiner oder gleich dem aktuellen Feldinhalt sind. Daraus wird dann die laufende Zeilennummer erstellt.

• Hinweis 

Wird einer Abfrage eine Bedingung hinzugefügt oder beruht eine Abfrage aus einer Verbindung von mehreren Tabellen, so ist diese Bedingung bzw. die Verbindung von mehreren Tabellen auch in der korrelierenden Unterabfrage hinzuzufügen. Andernfalls stimmt die Zählung nicht:

1. 001 SELECT "Name",  

2. 002    ( SELECT COUNT( "ID" ) FROM "Name" WHERE "GebDat" > '1995-12-31' 

         AND "ID" <= "a"."ID" ) AS "lfdNr."  

3. 003 FROM "Name" AS "a" WHERE "GebDat" > '1995-12-31' 

Die gesamte Bedingung der äußeren Abfrage muss in der korrelierenden Unterabfrage wiederholt werden. Hinzu kommt in der korrelierenden Unterabfrage noch die Bedingung, mit der sich die korrelierende Unterabfrage auf den aktuellen Datensatz bezieht.

Auch eine Nummerierung für entsprechende Gruppierungen ist möglich:

1. 001 SELECT "ID",  

2. 002    ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE "ID" <= "a"."ID" 

         AND "RechnungsID" = "a"."RechnungsID" ) AS "lfdNr."  

3. 003 FROM "Tabelle" AS "a" 

Hier gibt es in einer Tabelle verschiedene Rechnungsnummern ("RechnungsID"). Für jede Rechnungsnummer wird separat die Anzahl der Felder "ID" aufsteigend nach der Sortierung des Feldes "ID" wiedergegeben. Das erzeugt für jede Rechnung die Nummerierung von 1 an aufwärts.

Soll die aktuelle Sortierung der Abfrage mit der Zeilennummer übereinstimmen, so ist die Art der Sortierung entsprechend abzubilden. Dabei muss die Sortierung allerdings einen eindeutigen Wert ergeben. Sonst liegen 2 Nummern auf dem gleichen Wert.

1. 001 SELECT "Name",  

2. 002    ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE "Name" <= "a"."Name" )  

3. 003       AS "lfdNr."  

4. 004 FROM "Tabelle" AS "a"  

5. 005 ORDER BY "Name" 

Nur wenn ein eindeutigen Index für das Feld "Name" definiert wurde, ist hier eine eindeutige Sortierung und auch eine klare Nummerierung zu erwarten. Tauchen aber zwei gleiche Namen auf, so erhalten beide die gleiche Nummer.

1. 001 SELECT "Name",  

2. 002    ( SELECT COUNT( "ID" ) FROM "Tabelle"  

3. 003       WHERE "Name"||"ID" <= "a"."Name"||"a"."ID" ) AS "lfdNr."  

4. 004 FROM "Tabelle" AS "a"  

5. 005 ORDER BY "Name"||"ID" 

Hier wird der Inhalt des Feldes "Name" mit dem Inhalt des Feldes "ID" zusammengefügt. Die Sortierung ist jetzt eindeutig, die laufende Nummer entsprechend auch. Bei genauerer Betrachtung fällt allerdings auf, dass die Sortierung der "ID" natürlich jetzt nicht der Zahlensortierung folgt. Stattdessen wird z.B. die Zahl '9' nach der Zahl '10' einsortiert, da 9 größer als 1 ist. Wer dem noch abhelfen will muss entsprechend mit Leerstellen oder Nullen auffüllen:

1. 001 SELECT "Name",  

2. 002    ( SELECT COUNT( "ID" ) FROM "Tabelle"  

3. 003       WHERE "Name"||RIGHT('000000000'||"ID",10) <= "a"."Name"|| 

4. 004       RIGHT('000000000'||"a"."ID",10) ) AS "lfdNr."  

5. 005 FROM "Tabelle" AS "a"  

6. 006 ORDER BY "Name"||RIGHT('000000000'||"ID",10) 

Das Feld "ID" ist in diesem Beispiel ein INTEGER-Feld. INTEGER-Zahlen haben maximal 10 Stellen. Der Wert des Feldes wird mit 9 führenden Nullen aufgefüllt. Anschließend werden die 10 rechts stehenden Zeichen für die weitere Verarbeitung genutzt. '0000000009' ist nun kleiner als '0000000010'. Die Sortierung ist eindeutig und erfolgt bei gleichem Namen schließlich in gewohnter Reihenfolge der Schlüsselnummerierung.

Sollen Zeilen für eine Abfrage nummeriert werden, die sich nicht nur auf eine Tabelle bezieht oder mit einer zusätzlichen Bedingung versehen ist, so muss in der korrelierenden Unterabfrage die komplette Bedingung mit allen betroffenen Tabellen enthalten sein. Nur dann kann die Unterabfrage die gleichen Daten verarbeiten wie die äußere Abfrage.

Gleiche Werte können natürlich genutzt werden, wenn z.B. die Platzierung bei einem Wettkampf wiedergegeben werden soll, da hier gleiche Wettkampfergebnisse auch zu einer gleichen Platzierung führen. Damit die Platzierung allerdings so wiedergegeben wird, dass bei einer gleichen Platzierung der nachfolgende Wert ausgelassen wird, ist die Abfrage etwas anders zu konstruieren:

1. 001 SELECT "ID",  

2. 002    ( SELECT COUNT( "ID" ) + 1 FROM "Tabelle" WHERE "Zeit" < "a"."Zeit" )  

3. 003       AS "Platz"  

4. 004 FROM "Tabelle" AS "a" 

Es werden alle Einträge ausgewertet, die in dem Feld "Zeit" einen kleineren Eintrag haben. Damit werden alle Sportler erfasst, die vor dem aktuellen Sportler ins Ziel gekommen sind. Zu diesem Wert wird der Wert 1 addiert. Damit ist der Platz des aktuellen Sportlers bestimmt. Ist dieser zeitgleich mit einem anderen Sportler, so ist auch der Platz gleich. Damit sind Platzierungen wie 1. Platz, 2. Platz, 2. Platz, 4. Platz usw. möglich.

Schwieriger wird es, wenn neben der Platzierung auch eine Zeilennummerierung erfolgen soll. Dies kann z.B. sinnvoll sein, um mehrere Datensätze in einer Zeile zusammen zu fassen.

1. 001 SELECT "ID",  

2. 002    ( SELECT COUNT( "ID" ) + 1 FROM "Tabelle" WHERE "Zeit" < "a"."Zeit" )  

3. 003       AS "Platz",  

4. 004    CASE WHEN 

5. 005       ( SELECT COUNT( "ID" ) + 1 FROM "Tabelle"  

6. 006       WHERE "Zeit" = "a"."Zeit" ) = 1 

7. 007    THEN  

8. 008       ( SELECT COUNT( "ID" ) + 1 FROM "Tabelle"  

9. 009       WHERE "Zeit" < "a"."Zeit" ) 

10. 010    ELSE (SELECT  

11. 011       (SELECT COUNT( "ID" ) + 1 FROM "Tabelle" WHERE "Zeit" < "a"."Zeit") 

12. 012       + COUNT( "ID" ) FROM "Tabelle"  

13. 013       WHERE "Zeit" = "a"."Zeit" "ID" < "a"."ID" ) 

       END AS "Zeilennummer" 

14. 014 FROM "Tabelle" AS "a" 

Die zweite Spalte gibt weiterhin die Platzierung wieder. In der 3. Spalte wird zuerst nachgefragt, ob auch wirklich nur eine Person mit der gleichen Zeit durchs Ziel gekommen ist. Wenn dies erfüllt ist, wird die Platzierung auf jeden Fall direkt als Zeilennummer übernommen. Wenn dies nicht erfüllt ist, wird zu der Platzierung ein weiterer Wert addiert. Bei gleicher Zeit ("Zeit" = "a"."Zeit") wird dann mindestens 1 addiert, wenn es eine weitere Person mit dem Primärschlüssel ID gibt, deren Primärschlüssel kleiner ist als der aktuelle Primärschlüssel des aktuellen Datensatzes ("ID" < "a"."ID"). Diese Abfrage gibt also solange identische Werte zur Platzierung heraus, wie keine zweite Person mit der gleichen Zeit existiert. Existiert eine zweite Person mit der gleichen Zeit, so wird nach der ID entschieden, welche Person die geringere Zeilennummer enthält.

Diese Zeilensortierung entspricht übrigens der, die die Datenbanken anwenden. Wird z.B. eine Reihe Datensätze nach dem Namen sortiert, so erfolgt die Sortierung bei gleichen Datensätzen nicht nach dem Zufallsprinzip, sondern aufsteigend nach dem Primärschlüssel, der ja schließlich eindeutig ist. Es lässt sich auf diese Weise also über die Nummerierung eine Sortierung der Datensätze abbilden.

Die Zeilennummerierung ist auch eine gute Voraussetzung, um einzelne Datensätze als einen Datensatz zusammen zu fassen. Wird eine Abfrage zur Zeilennummerierung als Ansicht erstellt, so kann darauf mit einer weiteren Abfrage problemlos zugegriffen werden. Als einfaches Beispiel hier noch einmal die erste Abfrage zur Nummerierung, nur um ein Feld ergänzt:

1. 001 SELECT "ID", "Name",  

2. 002    ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE "ID"         <= "a"."ID" )  

3. 003       AS "lfdNr."  

4. 004 FROM "Tabelle" AS "a" 

Aus dieser Abfrage wird jetzt die Ansicht 'Ansicht1' erstellt. Die Abfrage, mit der z.B. die ersten 3 Namen zusammen in einer Zeile erscheinen können, lautet:

1. 001 SELECT "Name" AS "Name_1",  

2. 002    ( SELECT "Name" FROM "Ansicht1" WHERE "lfdNr." = 2 ) AS "Name_2",  

3. 003    ( SELECT "Name" FROM "Ansicht1" WHERE "lfdNr." = 3 ) AS "Name_3"  

4. 004 FROM "Ansicht1" WHERE "lfdNr." = 1 

Auf diese Art und Weise können mehrere Datensätze nebeneinander als Felder dargestellt werden. Allerdings läuft diese Nummerierung einfach vom ersten bis zum letzten Datensatz durch.

Sollen alle Personen zu einem Nachnamen zugeordnet werden, so ließe sich das folgendermaßen realisieren:

1. 001 SELECT "ID", "Name", "Nachname",  

2. 002    ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE "ID" <= "a"."ID"  

3. 003       AND "Nachname" = "a"."Nachname") AS "lfdNr."  

4. 004 FROM "Tabelle" AS "a" 

Jetzt kann über die erstellte Ansicht eine entsprechende Familienzusammenstellung erfolgen:

1. 001 SELECT "Nachname", "Name" AS "Name_1",  

2. 002    ( SELECT "Name" FROM "Ansicht1" WHERE "lfdNr." = 2  

3. 003       AND "Nachname" = "a"."Nachname") AS "Name_2",  

4. 004    ( SELECT "Name" FROM "Ansicht1" WHERE "lfdNr." = 3  

5. 005       AND "Nachname" = "a"."Nachname") AS "Name_3"  

6. 006 FROM "Ansicht1" AS "a"  

7. 007 WHERE "lfdNr." = 1 

In einem Adressbuch ließen sich so alle Personen einer Familie ("Nachnamen") zusammenfassen, damit jede Adresse nur einmal für ein Anschreiben berücksichtigt würde, aber alle Personen, an die das Anschreiben gehen soll, aufgeführt würden.

Da es sich um keine fortwährende Schleifenfunktion handelt, ist hier allerdings Vorsicht geboten. Schließlich wird die Grenze der parallel als Felder angezeigten Datensätze durch die Abfrage im obigen Beispiel z.B. auf 3 begrenzt. Diese Grenze wurde willkürlich gesetzt. Weitere Namen tauchen nicht auf, auch wenn die Nummerierung der "lfdNr." größer als 3 ist.

In seltenen Fällen ist so eine Grenze aber auch klar nachvollziehbar. Soll z.B. ein Kalender erstellt werden, so können mit diesem Verfahren die Zeilen die Wochen des Jahres darstellen, die Spalten die Wochentage. Da im ursprünglichen Kalender nur das Datum über den Inhalt entscheidet, werden durch die Zeilennummerierung immer die Tage einer Woche durchnummeriert und nach Wochen im Jahr als Datensatz später ausgegeben. Spalte 1 gibt dann Montag wieder, Spalte 2 Dienstag usw. Die Unterabfrage endet also jeweils bei der "lfdNr." = 7. Damit lassen sich dann im Bericht alle sieben Wochentage nebeneinander anzeigen und eine entsprechende Kalenderübersicht erstellen.

Manchmal ist es sinnvoll, durch eine Abfrage verschiedene Felder zusammenzufassen und mit einem Zeilenumbruch zu trennen. So ist es z.B. einfacher eine Adresse in einen Bericht komplett einzulesen.

Der Zeilenumbruch innerhalb einer Abfrage erfolgt durch Char(13). Beispiel:

1. 001 SELECT "Vorname"||' '||"Nachname"||Char(13)||"Straße"||Char(13)||"Ort"  

2. 002 FROM "Tabelle" 

Dies erzeugt nachher:

1. Vorname Nachname
   Straße
   Ort

• Hinweis 

Für Firebird muss statt CHAR(13) ASCII_CHAR(13) eingefügt werden. Firebird kennt die hier verwendete Kurzform nicht.

Mit so einer Abfrage, zusammen mit einer Nummerierung jeweils bis zur Nummer 3, lassen sich auch dreispaltige Etikettendrucke von Adressetiketten über Berichte realisieren. Eine Nummerierung ist in diesem Zusammenhang nötig, damit drei Adressen nebeneinander in einem Datensatz erscheinen. Nur so sind sie auch nebeneinander im Bericht einlesbar.

Je nach Betriebssystem kann es auch notwendig sein, zusätzlich zu Char(13) auch Char(10) in den Code mit aufzunehmen:

1. 001 SELECT "Vorname"||' '||"Nachname"||Char(13)||Char(10)|| 

2. 002    "Straße"||Char(13)||Char(10)||"Ort"  

3. 003 FROM "Tabelle" 

Solche Zeilenumbrüche werden allerdings nicht in der Abfrage angezeigt. Die Steuerzeichen werden in einem VARCHAR-Feld nicht umgesetzt. Entsprechend rückt der Text dort ohne Leerzeichen aneinander. Soll der Zeilenumbruch in der Abfrage angezeigt werden, so muss der zusammengefügte Text von VARCHAR nach LONGVARCHAR umgewandelt werden:

1. 001 SELECT CAST("Vorname"||' '||"Nachname"||Char(13)||Char(10)|| 

2. 002    "Straße"||Char(13)||Char(10)||"Ort" AS LONGVARCHAR) AS "Adresse"  

3. 003 FROM "Tabelle" 

Damit wird dann die Adresse auch in einer Abfrage mehrzeilig unter dem Alias "Adresse" dargestellt.

Für andere Datenbanken, auch neuere Versionen der Hsqldb, ist der Befehl 'Group_Concat()' verfügbar. Mit ihm können einzelne Felder einer Datensatzgruppe zusammengefasst werden. So ist es z.B. möglich, in einer Tabelle Vornamen und Nachnamen zu speichern und anschließend die Daten so darzustellen, dass in einem Feld die Nachnamen als Familiennamen erscheinen und in dem 2. Feld alle Vornamen hintereinander, durch z.B. Komma getrennt, aufgeführt werden.

• Hinweis 

Dieses Beispiel entspricht in vielen Teilen dem der Zeilennummerierung. Die Gruppierung zu einem gemeinsamen Feld stellt hier eine Ergänzung dar.

Wird nach der Abfrage zu:

Dieses Verfahren kann in Grenzen auch in der HSQLDB nachgestellt werden. Das folgende Beispiel bezieht sich auf eine Tabelle "Name" mit den Feldern "ID", "Vorname" und "Nachname". Folgende Abfrage wird zuerst an die Tabelle gestellt und als Ansicht "Ansicht_Gruppe" gespeichert:

1. 001 SELECT "Nachname", "Vorname",  

2. 002    ( SELECT COUNT( "ID" ) FROM "Name" WHERE "ID" <= "a"."ID"  

3. 003       AND "Nachname" = "a"."Nachname" ) AS "GruppenNr"  

4. 004 FROM "Name" AS "a" 

Im Kapitel «Abfragen» ist nachzulesen, wie diese Abfrage über die «Korrelierte Unterabfrage» auf Feldinhalte in der gleichen Abfragezeile zugreift. Dadurch wird eine aufsteigende Nummerierung, gruppiert nach den "Nachnamen", erzeugt. Diese Nummerierung wird in der folgenden Abfrage benötigt, so dass in dem Beispiel maximal 5 Vornamen aufgeführt werden.

1. 001 SELECT DISTINCT "Nachname",  

2. 002    ( SELECT "Vorname" FROM "Ansicht_Gruppe"  

3. 003       WHERE "Nachname" = "a"."Nachname" AND "GruppenNr" = 1 ) ||  

4. 004       COALESCE( ( SELECT ', ' || "Vorname" FROM "Ansicht_Gruppe"  

5. 005       WHERE "Nachname" = "a"."Nachname" AND "GruppenNr" = 2 ), '' ) ||  

6. 006       COALESCE( ( SELECT ', ' || "Vorname" FROM "Ansicht_Gruppe"  

7. 007       WHERE "Nachname" = "a"."Nachname" AND "GruppenNr" = 3 ), '' ) ||  

8. 008       COALESCE( ( SELECT ', ' || "Vorname" FROM "Ansicht_Gruppe"  

9. 009       WHERE "Nachname" = "a"."Nachname" AND "GruppenNr" = 4 ), '' ) ||  

10. 010       COALESCE( ( SELECT ', ' || "Vorname" FROM "Ansicht_Gruppe"  

11. 011       WHERE "Nachname" = "a"."Nachname" AND "GruppenNr" = 5 ), '' )  

12. 012 AS "Vornamen" 

13. 013 FROM "Ansicht_Gruppe" AS "a" 

Durch Unterabfragen werden nacheinander die Vornamen zu Gruppenmitglied 1, 2 usw. abgefragt und zusammengefasst. Ab der 2. Unterabfrage muss abgesichert werden, dass 'NULL'-Werte nicht die Zusammenfassung auf 'NULL' setzen. Deshalb wird bei einem Ergebnis von 'NULL' stattdessen '' angezeigt.