Base

Kapitel 8
Datenbankaufgaben

Dokumentationen zu LibreOffice unter de.libreoffice.org

Copyright
Dieses Dokument unterliegt dem Copyright © 2015. Die Beitragenden sind unten aufgeführt. Sie
dürfen dieses Dokument unter den Bedingungen der GNU General Public License (http://www.gnu.org/licenses/gpl.html), Version 3 oder höher, oder der Creative Commons Attribution License(http://creativecommons.org/licenses/by/3.0/), Version 3.0 oder höher, verändern und/oder weitergeben.
Warennamen werden ohne Gewährleistung der freien Verwendbarkeit benutzt.
Fast alle Hardware- und Softwarebezeichnungen und weitere Stichworte und sonstige Angaben,
die in diesem Buch verwendet werden, sind als eingetragene Marken geschützt.
Da es nicht möglich ist, in allen Fällen zeitnah zu ermitteln, ob ein Markenschutz besteht, wird das
Symbol (R) in diesem Buch nicht verwendet.

Mitwirkende/Autoren
Robert Großkopf

Jost Lange

Jochen Schiffers

Michael Niedermair

Rückmeldung (Feedback)Kommentare oder Vorschläge zu diesem Dokument können Sie in deutscher Sprache an die
Adresse discuss@de.libreoffice.org senden.

Vorsicht

Alles, was an eine Mailingliste geschickt wird, inklusive der E-Mail-Adresse undanderer persönlicher Daten, die die E-Mail enthält, wird öffentlich archiviert und kannnicht gelöscht werden. Also, schreiben Sie mit Bedacht!

Datum der Veröffentlichung und Softwareversion
Veröffentlicht am 1.8.2015. Basierend auf der LibreOffice Version 5.0.

Anmerkung für Macintosh Nutzer
Einige Tastenbelegungen (Tastenkürzel) und Menüeinträge unterscheiden sich zwischen derMacintosh Version und denen für Windows- und Linux-Rechnern. Die unten stehende Tabelle gibt
Ihnen einige grundlegende Hinweise dazu. Eine ausführlichere Aufstellung dazu finden Sie in der
Hilfedatei des jeweiligen Moduls.
Windows/Linux

entspricht am Mac

EffektMenü-Auswahl Extras →Optionen

LibreOffice → EinstellungenZugriff auf die Programmoptionen

Rechts-Klick

Control+Klick
ControlÖffnen eines Kontextmenüs

Ctrl (Control)oder Strg (Steuerung)⌘ (Command)

Tastenkürzel in Verbindung mit anderen
TastenF5Shift+⌘
F5
Shift ⌘+F5

Öffnen des Dokumentnavigator-Dialogs

F11

⌘+T
T

Öffnen des Formatvorlagen-Dialogs

Datenbankaufgaben

2Inhalt
Allgemeines zu Datenbankaufgaben .............................................................................................. 4
Datenfilterung ................................................................................................................................. 4
Datensuche .................................................................................................................................... 6
Suche mit LIKE .......................................................................................................................... 6
Suche mit LOCATE .................................................................................................................... 8
Bilder und Dokumente mit Base verarbeiten ................................................................................ 12
Bilder in die Datenbank einlesen .............................................................................................. 13
Bilder und Dokumente verknüpfen ........................................................................................... 13
Dokumente mit absoluter Pfadangabe verknüpfen .............................................................. 14
Dokumente mit relativer Pfadangabe verknüpfen ................................................................ 15
Verknüpfte Bilder und Dokumente anzeigen ............................................................................ 16
Dokumente in die Datenbank einlesen ..................................................................................... 17
Bildnamen ermitteln ............................................................................................................. 19
Bildnamen aus dem Speicher entfernen .............................................................................. 20
Bilder und Dokumente auslesen und anzeigen ........................................................................ 20
Codeschnipsel .............................................................................................................................. 21
Aktuelles Alter ermitteln ........................................................................................................... 21
Geburtstage in den nächsten Tagen anzeigen ......................................................................... 22
Tage zu Datumswerten addieren ............................................................................................. 23
Zeiten zu Zeitstempeln addieren .............................................................................................. 25
Laufenden Kontostand nach Kategorien ermitteln ................................................................... 26
Zeilennummerierung ................................................................................................................ 27
Zeilenumbruch durch eine Abfrage erreichen .......................................................................... 30
Gruppieren und Zusammenfassen ........................................................................................... 30

3

Allgemeines zu Datenbankaufgaben
Hier werden einige Lösungen für Problemstellungen vorgestellt, die im Laufe der Zeit viele Datenbankuser beschäftigen werden. Anfragen dazu kamen vor allem aus den Mailinglisten, insbesondere users@de.libreoffice.org,
sowie aus den Foren http://de.openoffice.info/viewforum.php?f=8
und http://www.libreoffice-forum.de/viewforum.php?f=10 .

Datenfilterung
Die Datenfilterung mittels der GUI ist bereits bei der Dateneingabe in Tabellen beschrieben. Hiersoll 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 dem kleinsten
Zahlentyp, TINYINT, entsprechen. Die Tabelle "Filter" wird sowieso nur einen Datensatz
abspeichern.
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:
SELECT * FROM "Medien"
Alle Datensätze der Tabelle "Medien" werden angezeigt, auch der Primärschlüssel.
SELECT * FROM "Medien" WHERE "Titel" = IFNULL( ( SELECT "Filter_1"
FROM "Filter" ), "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.
SELECT * , IFNULL( "Titel", '' ) AS "T" FROM "Medien" WHERE "T" =IFNULL( ( SELECT "Filter_1" FROM "Filter" ), "T" )Diese Variante würde zum Ziel führen. Statt "Titel" direkt zu filtern wird ein Feld gefiltert, dasden 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.

Datenfilterung4

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:
SELECT "Medien".* , IFNULL( "Medien"."Titel", '' ) AS "T" FROM
"Medien" WHERE "T" = IFNULL( ( SELECT "Filter_1" FROM "Filter" ),"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 Filterungkann 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:
SELECT "Medien".* , IFNULL( "Medien"."Titel", '' ) AS "T",IFNULL( "Medien"."Kategorie", '' ) AS "K" FROM "Medien" WHERE "T" =IFNULL( ( SELECT "Filter_1" FROM "Filter" ), "T" ) AND "K" = IFNULL( (SELECT "Filter_2" FROM "Filter" ), "K" )Damit ist die Erstellung der editierbaren Abfrage abgeschlossen. Jetzt wird noch die Grundlage für
die beiden Listenfelder als Abfrage zusammengestellt:
SELECT DISTINCT "Titel", "Titel" FROM "Medien" ORDER BY "Titel" ASC
Das Listenfeld soll sowohl die "Titel" anzeigen als auch die "Titel" an die dem Formularzugrundeliegende 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" weitergegebenwird.
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 2noch 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.

Datenfilterung5

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

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

Suche mit LIKEDie 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 Labelfeld 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 bautdas Listenfeld auf den Tabelleninhalten auf) geht die Suche von einer beliebigen Eingabe aus.
SELECT * FROM "Medien" WHERE "Titel" = ( SELECT "Suchbegriff" FROM
"Filter" )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:
SELECT * FROM "Medien" WHERE "Titel" = IFNULL( ( SELECT "Suchbegriff"
FROM "Filter" ), "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:
SELECT * FROM "Medien" WHERE "Titel" LIKE ( SELECT '%' ||"Suchbegriff" ||'%' FROM "Filter" )oder besser:

Datensuche

6SELECT * FROM "Medien" WHERE "Titel" LIKE IFNULL( ( SELECT '%' ||"Suchbegriff" ||'%' FROM "Filter" ), "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 undhinter 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 an Möglichkeiten ab:
SELECT * FROM "Medien" WHERE
LOWER("Titel") LIKE IFNULL( ( SELECT '%' || LOWER("Suchbegriff") ||'%'FROM "Filter" ), LOWER("Titel") )Die Bedingung ändert den Suchbegriff und den Feldinhalt auf Kleinschreibweise. Damit werden
auch ganze Sätze vergleichbar.
SELECT * FROM "Medien" WHERE
LOWER("Titel") LIKE IFNULL( ( SELECT '%' || LOWER("Suchbegriff") ||'%'FROM "Filter" ), LOWER("Titel") ) OR
LOWER("Kategorie") LIKE ( SELECT '%' || LOWER("Suchbegriff") ||'%'FROM "Filter" )Die IFNULL-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 entsprechendviele 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 Textumwandlungenunterbleiben. 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.

Datensuche

7

Die Abfragen, die zur Filterung und zum Durchsuchen von Daten genutzt werden,
lassen sich auch direkt in ein Formular einbauen.

Tipp

Die gesamten obigen Bedingungen sind bei den Formular-Eigenschaften in der Zeile
Filter eintragbar. Aus
SELECT * FROM "Medien" WHERE "Titel" = IFNULL( ( SELECT"Suchbegriff" FROM "Filter" ), "Titel" )wird dann ein Formular, das als Inhalt die Tabelle "Medien" nutzt.
Unter «Filter» steht dann("Medien"."Titel" = IFNULL( ( SELECT "Suchbegriff" FROM
"Filter" ), "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.

Suche mit LOCATEDie 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 nochangegeben, ab welcher Position gesucht werden soll. Kurz also: LOCATE(Suchbegriff,
Textfeld aus der Datenbank, Position).
Für die folgende Erklärung wird eine Tabelle genutzt, die den Namen "Tabelle" hat. Der Primärschlüssel heißt "ID" und muss lediglich einzigartig sein. Zusätzlich gibt es noch ein Feld "Memo",
das als Feld des Typs Memo (LONGVARCHAR) erstellt wurde. In dem Feld "Memo" sind ein paar
Absätze dieses Handbuchs gespeichert.1

Die Beispielabfragen sind als Parameterabfragen angelegt. Der einzugebende Suchtext ist jeweils'office'.

1

Die Screenshots zu diesem Kapitel entstammen der Datenbank«Beispiel_Autotext_Suchmarkierung_Rechtschreibung.odb», die dem Handbuch beiliegt.

Datensuche8Zuerst ein Zugriff über LIKE. LIKE kann nur in der Bedingung stehen. Wird der Suchtext irgendwo
gefunden, dann wird der entsprechende Datensatz angezeigt. Durch den Vergleich von der Kleinschreibung des Feldinhaltes über LOWER("Memo") mit der Kleinschreibung des Suchtextes überLOWER(:Suchtext) werden die Inhalte unabhängig von der Schreibweise gefunden. Je länger
der Text in dem Memo-Feld ist, desto schwerer wird es, den begriff dann tatsächlich zu sehen.

LOCATE gibt genauer wieder, an welcher Stelle sich der Suchbegriff befindet. In Datensatz 1 und 2
ist der Suchbegriff nicht vorhanden. LOCATE gibt als Position hier '0' aus. Leicht nachzählen lässt
sich das Ergebnis am Datensatz 5: Mit dem 6. Buchstaben beginnt hier die Textfolge 'Office'.
Natürlich wäre es auch möglich, das entsprechende Ergebnis wie bei LIKE auch über LOCATE zu
erhalten:
SELECT "ID", "Memo"
FROM "Tabelle"
WHERE LOCATE(LOWER(:Suchtext),LOWER("Memo")) > 0
Das Auffinden der Position allein ist im obigen Beispiel auch mit einem Blick auf das Feld "Memo"schon recht einfach. Komplizierter wird es aber, wenn der Inhalt eben nicht gerade, wie hier zur
Demonstration, in den ersten 70 Zeichen enthalten ist. Dann wird es sinnvoll, Textstücke mit dem
Inhalt direkt zu finden.

In der Spalte «Treffer» wird das Suchergebnis genauer dargestellt. Die vorherige Abfrage ist einfach als Basis für diese Abfrage genommen worden. Dies bewirkt, dass in der äußeren Abfrage

Datensuche

9nicht jedes Mal LOCATE(LOWER(:Suchtext),LOWER("Memo")) sondern einfach "Position" eingegeben werden kann. Vom Prinzip her ist dieses Verfahren nicht anders, als wenn die vorhergehenden Abfrage gespeichert würde und diese Abfrage als Quellenangabe auf die vorherige
Abfrage zugreift.
"Position" = 0 bedeutet, dass kein Suchergebnis vorhanden ist. In dem Fall also die Ausgabe'**keine Fundstelle**'.
"Position" < 10 bedeutet, dass sich der Suchbegriff direkt am Anfang des Textes befindet. 10
Zeichen können leicht überblickt werden. Es wird also der gesamte Text wiedergegeben. Hier
könnte also auch statt SUBSTRING("Memo",1) direkt "Memo" stehen.
Für alle anderen Treffer wird ab 10 Zeichen vor der Position des Treffers nach einer Leerstelle ' 'gesucht. Der Text soll nicht mitten in einem Wort starten, sondern nach so einer Leerstelle beginnen. Über SUBSTRING("Memo",LOCATE(' ',"Memo","Position"-10)+1) wird erreicht,
dass der Text mit dem Beginn eines Wortes startet, das maximal 10 Zeichen vor dem Begriff 'office'erscheint.
In der Praxis dürften hier mehr Zeichen erforderlich sein, da doch sehr viele Worte die Anzahl von
10 Zeichen übersteigen und selbst der Suchbegriff ja in einem Wort liegen kann, das noch 10 Zeichen vor dem eigentlich Begriff hat. 'LibreOffice' wird bei Suchbegriff 'office' so noch dargestellt, dadas 'O' an der 6. Stelle steht. Stellen wir uns aber z.B. den Begriff 'hand' vor, so würde im 4. Datensatz bereits das Aus für die Darstellung stehen. 'LibreOffice-Handbücher' hat, von 'hand' aus nachlinks gezählt, 12 Zeichen. Wird aber höchsten 10 Zeichen nach links gesucht, so wird als erstes
Leerzeichen das Zeichen hinter dem Komma gefunden. Die Darstellung in «Treffer» würde mit 'daseingebaute Hilfesystem …' beginnen.

Die Abfragetechnik ist gegenüber der vorhergehenden Abfrage gleich geblieben. Lediglich die
Länge des auszugebenden Treffers ist beschränkt worden. In diesem Falle erforderte dieBeschränkung hart auf 25 Zeichen. Die Funktion SUBSTRING erfordert als erstes die Angabe deszu durchsuchenden Textes, als zweites dann die Startposition der Ausgabe und als drittes optional
die Länge des auszugebenden Textes. Natürlich hier auch reichlich kurz gehalten, aber eben nur
zu Demonstrationszwecken. Vorteil der Verkürzung ist natürlich ein deutlich geringerer Speicherverbrauch bei entsprechend großen Datenmengen und ein direkter Blick auf die Fundstelle. Sichtbarer Nachteil dieser Form der Verkürzung ist aber, dass der Schnitt rigoros nach dem 25 Zeichen
gemacht wird – ohne Rücksicht auf einen Wortbeginn.

Datensuche

10Hier wird ab dem 25. Zeichen in dem darzustellenden «Treffer» nach dem nächsten Leerzeichen
gesucht. Der auszugebende Inhalt wird dann durch die gefundene Position begrenzt.
Recht einfach gestaltet sich dies noch, wenn der Treffer am Anfang liegt. Hier gibt
LOCATE(' ',"Memo",25) genau die Position vom Anfang des gesamten Textes an wieder. Sie
entspricht, da der Text von Anfang an ausgegeben werden soll, auch genau der Länge des auszugebenden Begriffes.
Die Suche der dem Suchbegriff folgenden Leerzeichen ist auch bei einem weiter hinten liegenden
Treffer nicht weiter kompliziert. Die Suche beginnt einfach an der Position des Treffers. Hinzugezählt werden noch 20 Zeichen, die auf jeden Fall folgen sollen. Danach wird das nächste Leerzeichen ausgemacht: LOCATE(' ',"Memo","Position"+20). Hiermit ist aber nur die Position im
Gesamtfeld ausgemacht. Die ermittelte Position gibt also auf keinen Fall die Länge das auszugebenden Textes wieder. Von dem ermittelten Positionswert muss hingegen der Positionswert abgezogen werden, bei dem die Ausgabe des Treffers starten soll. Dies wurde durch
LOCATE(' ',"Memo","Position"-10)+1 vorher bereits einmal abgefragt. Erst so kann danndie korrekte Länge des Textes dargestellt werden.

Datensuche

11Mit der gleichen Technik können mehrere Abfragen hintereinander geschachtelt erfolgen. Die vorhergehende Abfrage ist jetzt die Datenquelle dieser Abfrage. Sie ist komplett unterhalb des Begriffes FROM in Klammern eingefügt worden. Lediglich die Felder wurden etwas umbenannt, da ja jetzt
mehrere Positionen und Treffer angegeben werden. Außerdem wurde die nächste Position einer
Fundstelle über LOCATE(LOWER(:Suchtext),LOWER("Memo"),"Position01"+1) ermittelt.
Es wird also mit dem Suchen der nächsten Stelle einfach eine Stelle hinter dem vorhergehenden
Treffer gestartet.
Die äußerste Abfrage stellt die entsprechenden Felder der anderen beiden Abfragen dar und fügt
zusätzlich «Treffer02» auf die gleiche Weise hinzu, mit der vorher «Treffer01» ermittelt wurde.
Außerdem wird in der äußeren Abfrage schon ermittelt, ob es vielleicht noch weitere Treffer gibt.
Die entsprechende Position wird in «Position03» ausgegeben. Lediglich Datensatz 5 hat noch weitere Trefferpositionen vorzuweisen und könnte also in einer weiteren Nachfrage noch weitere Treffer ermöglichen.
Die Staffelung der Abfragen ist hier beliebig weit möglich. Allerdings werden sie mit jeder weiterenäußeren Abfrage natürlich für das System immer belastender. Hier sind entsprechende Test notwendig, was denn nun sinnvoll und was realistisch machbar ist. Wie mit Hilfe von Makros über ein
Formular sogar eine Abfragetechnik machbar ist, die gleichzeitig alle Fundstellen im Text markiert,
ist im Kapitel «Makros» erklärt.

Bilder und Dokumente mit Base verarbeiten
Base-Formulare bieten für die Verarbeitung von Bildern grafische Kontrollfelder an. Nur über diesegrafischen Kontrollfelder ist ohne Einsatz von Makros bei der internen HSQLDB möglich, Bilder in
Bilder und Dokumente mit Base verarbeiten

12die Datenbank ein zu lesen. Das grafische Kontrollfeld kann aber auch dazu genutzt werden, nur
die Verknüpfung zu Bildern außerhalb der Datenbankdatei zu ermöglichen.2

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

Feldtyp

Beschreibung

ID

Integer

Die ID ist Primärschlüssel dieser Tabelle.

Bild

Bild

Nimmt das Bild als Binärdatenstrom auf.

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 derDatenbank 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 Megapixelbereichsind 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 unbedienbar 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 derHaupttabelle verbunden. Die Suche in der Haupttabelle kann dann deutlich schneller erfolgen, da
die Tabelle selbst nicht so viel Speicher beansprucht.

Bilder und Dokumente verknüpfen
Mit einer entsprechend durchdachten Ordnerstruktur ist es günstiger, direkt auf die Dateien vonaußerhalb zuzugreifen. Dateien außerhalb der Datenbank können beliebig groß sein, ohne dassdie Funktionen der Datenbank selbst beeinflusst werden. Leider bedeutet dies aber auch, dasseine Umbenennung von Ordnern auf dem eigenen Rechner oder im Internet dazu führt, dass derZugriff auf die entsprechenden Dateien verloren geht.
Um Bilder nicht in eine Datenbankdatei einzulesen, sondern nur zu verknüpfen, bedarf es nur einer
kleinen Änderung gegenüber der vorhergehenden Tabelle:
Feldname

Feldtyp

Beschreibung

ID

Integer

Die ID ist Primärschlüssel dieser Tabelle.

Bild

Text

Nimmt den Pfad zu dem Bild auf.

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. Leider lässt sich aber nicht auf die glei2

Die Datenbank «Beispiel_Dokumente_einlesen_auslesen.odb» liegt diesem Handbuch bei.

Bilder und Dokumente mit Base verarbeiten

13che Art und Weise auch die Verbindung zu einem Dokument herstellen. Schon das Einlesen desPfades ist nicht möglich, da das grafische Kontrollfeld von der Konstruktion her erst einmal nur für
Bilder gedacht ist und auch entsprechend nur Bildtypen zur Auswahl anbietet, die es selbst auchanzeigen kann.
Bei einem Bild kann über den Pfad wenigstens noch der Inhalt auf dem grafischen Kontrollfeld
gelesen werden. Bei einem Dokument 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.
Feldname

Feldtyp

Beschreibung

ID

Integer

Die ID ist Primärschlüssel dieser Tabelle.

Beschreibung

Text

Beschreibung des Dokumentes, Suchbegriffe ...

Datei

Text

Nimmt den Pfad zu dem Bild auf.

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.

Dokumente mit absoluter Pfadangabe verknüpfen
Ü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:
SUB Pfad_Einlesen(oEvent AS OBJECT)
DIM oForm AS OBJECTDIM oFeld AS OBJECTDIM oFeld2 AS OBJECT
DIM stUrl AS STRING
oFeld = oEvent.Source.ModeloForm = oFeld.Parent
oFeld2 = oForm.getByName("GraphischesFeld")
IF oFeld.Text <> "" THEN
stUrl = ConvertToUrl(oFeld.Text)oFeld2.BoundField.updateString(stUrl)
END IF
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 inirgendeinem Unterformular verwenden will. Das Dateiauswahlfeld ist von dort aus überoEvent.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 Bil-

Bilder und Dokumente mit Base verarbeiten

14der 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. Um dies zu bewerkstelligen müsste nachgebessert werden. Die
Prozedur für diesen Zweck sieht wesentlich umfangreicher aus, da ein Vergleich der Pfadeingabe
der aktuell.

Dokumente mit relativer Pfadangabe verknüpfen
Das folgende Makro ist an die Eigenschaft «Text modifiziert» des Dateiauswahlfeldes gebunden.
SUB PfadAuslesen
DIM oDoc AS OBJECTDIM oDrawpage AS OBJECTDIM oForm AS OBJECTDIM oFeld AS OBJECTDIM oFeld2 AS OBJECT
DIM arUrl_Start()
DIM ar()
DIM ar1()
DIM ar2()
DIM stText AS STRING
DIM stUrl_gesamt AS STRING
DIM stUrl_Text AS STRING
DIM stUrl AS STRING
DIM stUrl_weg AS STRING
DIM ink AS INTEGER
DIM i AS INTEGER
oDoc = thisComponent
oDrawpage = oDoc.Drawpage
oForm = oDrawpage.Forms.getByName("Formular")oFeld = oForm.getByName("GraphischesFeld")oFeld2 = oForm.getByName("Dateiauswahl")

Zuerst werden, wie bei allen Prozeduren, die Variablen deklariert. Anschließend werden die entsprechenden Felder aufgesucht, die für die Aufnahme des Pfades wichtig sind. Der gesamteanschließende Code wird nur dann ausgeführt, wenn in der Dateiauswahl auch Inhalt steht, dasFeld also z.B. nach einem Datensatzwechsel nicht einfach geleert wird.
IF oFeld2.Text <> "" THEN
arUrl_Start = split(oDoc.Parent.Url,oDoc.Parent.Title)ar = split(ConvertToUrl(oFeld2.Text),"/")
stText = ""Der Pfad zur Datenbankdatei wird ausgelesen. Dies erfolgt, wie vorher schon erklärt, indem zuerst
die gesamte Url gelesen wird und anschließend der Dateiname als Trenner eines Arrays dafürsorgt, dass im ersten teil des Arrays nur der direkte Pfad steht.
Anschließend werden alle Elemente des Pfades, der über die Dateiauswahl ausgesucht wurde, in
das Array ar eingelesen. Als Trenner dient /. Das gelingt unter Linux direkt. Unter Windows mussder Inhalt von oFeld2.Text auf jeden Fall noch in eine URL umgewandelt werden, die mit dem
Slash und nicht mit dem Backslash \ arbeitet.
Die Trennung dient dazu, für die Ermittlung des Pfades zu der Datei einfach nur den Dateinamen
hinten abzuschneiden. Daher wird im nächsten Schritt der Pfad zu der aufzurufenden Datei in der
Variablen stText wieder zusammengesetzt. Die Schleife endet allerdings nicht mit dem höchsten
Wert des vorher erstellten Arrays ar, sondern schon beim erreichen der vorhergehenden Wertes.
FOR i = LBound(ar()) TO UBound(ar()) - 1
stText = stText & ar(i) & "/"NEXT
stText = Left(stText,Len(stText)-1)

Bilder und Dokumente mit Base verarbeiten

15

arUrl_Start(0) = Left(arUrl_Start(0),Len(arUrl_Start(0))-1)

Das zuletzt angehängte / wird wieder entfernt, da sonst im folgenden Array zum Schluss ein leerer
Arraywert erzeugt wird, der den Pfadvergleich stören würde. Für einen richtigen Vergleich ist jetzt
der Text noch zu einer tatsächlichen Url, beginnend mit file:///, umzuwandeln. Schließlich sollmit dem Pfad der Datenbankdatei verglichen werden, der genau auf dieser Basis zusammengesetzt ist.
stUrl_Text = ConvertToUrl(stText)
ar1 = split(stUrl_Text,"/")
ar2 = split(arUrl_Start(0),"/")
stUrl = ""ink = 0stUrl_weg = ""In ar1 werden alle Elemente gespeichert, die den Pfad zu der aufzurufenden Datei wieder geben.
In ar2 sind alle Elemente gespeichert, die den Pfad zur Datenbankdatei wieder geben. Anschließend wird das gesamte Array ar2 schrittweise für einen Vergleich in einer Schleife durchlaufen.
FOR i = LBound(ar2()) TO UBound(ar2())
IF i <= UBound(ar1()) THEN

Nur wenn die Zahl i nicht größer ist als die Anzahl der in ar1 enthaltenen Elemente wird der folgende Code ausgeführt. Ist der Wert aus ar2 gleich dem entsprechenden Wert aus ar1 und ist
bisher noch kein unterschiedlicher Wert aufgetaucht, dann wird der gemeinsame Inhalt in einer
Variablen gespeichert, die zum Schluss von der Pfadangabe abgeschnitten werden kann.
IF ar2(i) = ar1(i) AND ink = 0 THEN
stUrl_weg = stUrl_weg & ar1(i) & "/"ELSE

Ist irgendwann ein Unterschied zwischen den beiden Arrays aufgetaucht, so wird für jeden unterschiedlichen Wert die Bezeichnung für «setze ein Verzeichnis höher an» in der Variablen stUrlgespeichert.
stUrl = stUrl & "../"
ink = 1
END IF

Sobald der Index, der durch die Variable i dargestellt wird, größer wird als die Anzahl der Elemente von ar1, wird für jeden weiteren Wert von ar2 wieder ein weiteres ../ in der Variablen
stUrl gespeichert.
ELSE
stUrl = stUrl & "../"END IFNEXT
stUrl_gesamt = ConvertToUrl(oFeld2.Text)oFeld.boundField.UpdateString(stUrl & Right(stUrl_gesamt,Len(stUrl_gesamt)Len(stUrl_weg)))
END IF
END SUB

Ist die Schleife durch ar2 komplett durchlaufen, so steht fest, ob und wie viele Verzeichnisse
höher von der Datenbankdatei aus gesehen das Verzeichnis für die aufzurufende Datei liegt. Jetzt
wird einmal die stUrl_gesamt aus dem Text im Dateiauswahlfeld erstellt. Diese enthält auch den
Dateinamen. Anschließend wird in das graphische Feld der Wert für die Url übertragen. Der Wert
für die Url beginnt mit stUrl, in der ../ in der erforderlichen Anzahl stehen. Dann wird vonstUrl_gesamt der vordere Teil abgeschnitten, der bei den Pfaden zur Datenbankdatei und zur
externen Datei gleich war. Der weg zu schneidende Inhalt wurde in stUrl_weg gespeichert.

Verknüpfte Bilder und Dokumente anzeigen
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.

Bilder und Dokumente mit Base verarbeiten

16

Dokumente lassen sich standardmäßig in Base überhaupt nicht anzeigen.
Um dennoch eine Anzeige zu ermöglichen bedarf es wieder einer Makrolösung. Dieses Makro wirdüber einen Button in dem Formular gestartet, in dem das graphische Kontrollfeld liegt.
SUB Betrachten(oEvent AS OBJECT)
DIM oDoc AS OBJECTDIM oForm AS OBJECTDIM oFeld AS OBJECTDIM oShell AS OBJECT
DIM stUrl AS STRING
DIM stFeld AS STRING
DIM arUrl_Start()oDoc = thisComponent
oForm = oEvent.Source.Model.Parent
oFeld = oForm.getByName("GraphischesFeld")
stUrl = oFeld.BoundField.getString

Das graphische Kontrollfeld im Formular wird aufgesucht. Da in der Tabelle nicht das Bild selbst,
sondern nur der Pfad als Text gespeichert wird, wird hier über getString dieser Text ausgelesen.
Anschließend wird der Pfad zu der Datenbankdatei ermittelt. Mit oDoc.Parent wird die *.odbDatei erreicht. Sie ist der Container für die Formulare. Über oDoc.Parent.Url wird schließlich
die gesamte URL incl. Dateinamen ausgelesen. Der Dateiname ist auch zu sehen in
oDoc.Parent.Title. Der Text wird jetzt mit der Funktion split aufgetrennt, wobei als Trenner
der Dateiname benutzt wird. Die Auftrennung gibt so nur als erstes und einziges Element desArrays den Pfad zur *.odb-Datei wieder.
arUrl_Start = split(oDoc.Parent.Url,oDoc.Parent.Title)
oShell = createUnoService("com.sun.star.system.SystemShellExecute")
stFeld = convertToUrl(arUrl_Start(0) + stUrl)
oShell.execute(stFeld,,0)
END SUB

Externe Programme können über das Struct com.sun.star.system.SystemShellExecute
gestartet werden. Dem externen Programm wird hier nur der Pfad zur Datei mitgegeben, der aus
dem Pfad zur Datenbankdatei und dem intern gespeicherten relativen Pfad von der Datenbankdatei aus zusammengesetzt wurde. Die grafische Benutzeroberfläche des Betriebssystems entscheidet jetzt darüber, mit welchem Programm die entsprechende Datei zu öffnen ist.
Mit dem Kommando oShell.execute werden 3 Parameter übergeben. Als erstes wird eine ausführbare Datei oder der Pfad zu einer Datei aufgeführt, die im System mit einem Programm verbunden sind. Als zweites werden Parameter aufgeführt, mit denen das Programm gestartet werdensoll. Als drittes wird über eine Ziffer mitgeteilt, wie mit Fehlermeldungen des Systems bei missglückter Ausführung umzugehen ist. Hier stehen 0 (Standard), 1 (keine Meldung anzeigen) und 2(nur das Öffnen von absoluten URLs erlauben) zur Verfügung.

Dokumente in die Datenbank einlesen
Beim Einlesen der Dokumente sollten folgende Bedingungen immer im Auge behalten werden:• Je größer die Dokumente sind, desto schwerfälliger wird die Datenbank. Bei entsprechend
großen Dokumenten, vor allem Bildern, ist deshalb eine externe Datenbank der internen
Datenbank vorzuziehen.
• In Dokumenten kann ebenso wenig wie in Bildern recherchiert werden. Sie werden als
Binärdaten gespeichert und können in einem Feld gespeichert werden, das einem Bildfeld
entspricht..
• Dokumente, die in die HSQLDB-Datenbank eingelesen werden, können nur über den
Makroweg auch wieder ausgelesen werden. Ein SQL-Weg ist für die interne Datenbank
nicht erreichbar.

Bilder und Dokumente mit Base verarbeiten

17Die folgenden Makros zum Ein-und Auslesen bauen dabei auf eine Tabelle auf, die neben derDatei im Binärformat eine Beschreibung der Datei und den ursprünglichen Dateinamen enthalten
soll. Schließlich wird der Dateiname ja nicht mit abgespeichert und sollte beim Auslesen der Datei
aber darauf Schlüsse zulassen, um welchen Dateityp es sich denn handelt. Nur dann kann die
Datei auch zum Lesen durch andere Programme einwandfrei erkannt werden.
Die Tabelle enthält die folgenden Felder:
Feldname

Feldtyp

Beschreibung

ID

Integer

Die ID ist Primärschlüssel dieser Tabelle.

Beschreibung

Text

Beschreibung des Dokumentes, Suchbegriffe ...

Datei

Bild

Nimmt den binären Inhalt des Bildes oder der Datei auf.

DateiName

Text

Soll die Bezeichnung der Datei mit Dateiendung abspeichern.
Wichtig für das spätere Auslesen.

Das Formular zum Einlesen und wieder Ausgeben der Dateien sieht so aus:

Solange sich Bilddateien in der Datenbank befinden können diese Dateien auch in dem graphischen Kontrollfeld angesehen werden. Alle anderen Dateien werden in dem Kontrollfeld nicht sichtbar.
Das folgende Makro für das Einlesen der Dateien wird über Eigenschaften: Dateiauswahl →Ereignisse → Text modifiziert ausgelöst.
SUB DateiEinlesen_mitName(oEvent AS OBJECT)
DIM oForm AS OBJECTDIM oFeld AS OBJECTDIM oFeld2 AS OBJECTDIM oFeld3 AS OBJECTDIM oStream AS OBJECTDIM oSimpleFileAccess AS OBJECT
DIM stUrl AS STRING
DIM stName AS STRING
oFeld = oEvent.Source.ModeloForm = oFeld.Parent
oFeld2 = oForm.getByName("DateiName")oFeld3 = oForm.getByName("GraphischesFeld")
IF oFeld.Text <> "" THEN
stUrl = ConvertToUrl(oFeld.Text)
ar = split(stUrl,"/")
stName = ar(UBound(ar))

Bilder und Dokumente mit Base verarbeiten

18

oFeld2.BoundField.updateString(stName)oSimpleFileAccess = createUnoService("com.sun.star.ucb.SimpleFileAccess")oStream = oSimpleFileAccess.openFileRead(stUrl)oFeld3.BoundField.updateBinaryStream(oStream, oStream.getLength())
END IF
END SUB

Da das Makro über das auslösende Ereignis die Position der anderen Formularfelder ermittelt,muss nicht besonders überprüft werden, ob die Felder nun in einem Formular oder Unterformularliegen. Alle Felder müssen lediglich im gleichen Formular positioniert sein.
Das Feld «DateiName» speichert den Namen der Datei ab, die ausgesucht wird. Bei Bildern muss
dieser Name ohne ein zusätzliches Makro händisch eingegeben werden. Hier wird stattdessen derDateiname aus der URL ermittelt und automatisch beim Einlesen der Datei mit eingefügt.
Das Feld «GraphischesFeld» speichert die Daten in dem gemeinsamen Feld für Bilder undDateien ab.
Aus dem Dateiauswahlfeld wird über oFeld.Text der Pfad komplett mit Dateinamen ausgelesen.
Damit die URL-Schreibweise nicht an systemspezifischen Bedingungen scheitert wird der ausgelesene Text mit ConvertToUrl in eine allgemeingültige URL umgewandelt. Die so erstellte allgemeingültige URL wird in ein Array aufgeteilt. Der Trenner ist das /. Letztes Element dieser Pfadangabe ist der Dateiname. Ubound(ar) gibt die Nummer für das letzte Element an. Daher kann derDateiname über ar(Ubound(ar)) direkt ausgelesen und anschließend als String an das Feldübergeben werden.
Um die Datei selbst einzulesen muss der UnoService
com.sun.star.ucb.SimpleFileAccess bemüht werden. Über diesen Service kann der Inhalt
der Datei als Datenstrom ausgelesen werden. Anschließend wird der so in dem Objekt oStream
zwischengespeicherte Inhalt wiederum als Datenstrom in das Feld eingefügt, das mit dem Feld
"Datei" der Tabelle verbunden ist. Dabei muss neben dem Objekt oStream auch die Länge desDatenstromes als Parameter angegeben werden.
Die Daten sind jetzt wie eine normale Eingabe in die Formularfelder eingefügt. Wird das Formulareinfach geschlossen, so sind die Daten noch nicht abgespeichert. Die Speicherung erfolgt erst bei
Betätigung des Speicherbuttons in der Navigationsleiste oder automatisch bei der Navigation zum
nächsten Datensatz.

Bildnamen ermitteln
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 desDateinamens, 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.
SUB BildnamenAuslesen(oEvent AS OBJECT)
oForm = oEvent.Source
IF InStr(oForm.ImplementationName, "ODatabaseForm") THEN
oFeld = oForm.getByName("GraphischesFeld")oFeld2 = oForm.getByName("DateiName")
IF oFeld.ImageUrl <> "" THEN
stUrl = ConvertToUrl(oFeld.ImageUrl)ar = split(stUrl,"/")
stName = ar(UBound(ar))
oFeld2.BoundField.updateString(stName)END IF
END IF
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.
Bilder und Dokumente mit Base verarbeiten

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

Bildnamen aus dem Speicher entfernen
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 Abspeicherungsvorgang erfolgt. Eine Entfernung des Pfades vor der Abspeicherung löscht das Bild.
Das Makro wird über Formular-Eigenschaften → Ereignisse → Nach der Datensatzaktion
gestartet.
SUB BildnamenZuruecksetzen(oEvent AS OBJECT)
oForm = oEvent.Source
IF InStr(oForm.ImplementationName, "ODatabaseForm") THEN
oFeld = oForm.getByName("GraphischesFeld")
IF oFeld.ImageUrl <> "" THEN
oFeld.ImageUrl = ""END IF
END IF
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.

Bilder und Dokumente auslesen und anzeigen
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.
SUB DateiAuslesen_mitName(oEvent AS OBJECT)
DIM oDoc AS OBJECTDIM oDrawpage AS OBJECTDIM oForm AS OBJECTDIM oFeld AS OBJECTDIM oFeld2 AS OBJECTDIM oStream AS OBJECTDIM oShell AS OBJECTDIM oPath AS OBJECTDIM oSimpleFileAccess AS OBJECT
DIM stName AS STRING
DIM stPfad AS STRING
DIM stFeld AS STRINGoForm = oEvent.Source.Model.Parent
oFeld = oForm.getByName("GraphischesFeld")oFeld2 = oForm.getByName("DateiName")
stName = oFeld2.Text
IF stName = "" THEN
stName = "Datei"
END IFoStream = oFeld.BoundField.getBinaryStream
oPath = createUnoService("com.sun.star.util.PathSettings")
stPfad = oPath.Temp & "/" & stName
oSimpleFileAccess = createUnoService("com.sun.star.ucb.SimpleFileAccess")oSimpleFileAccess.writeFile(stPfad, oStream)

Bilder und Dokumente mit Base verarbeiten

20

oShell = createUnoService("com.sun.star.system.SystemShellExecute")
stFeld = convertToUrl(stPfad)
oShell.execute(stFeld,,0)
END SUBDie 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 derPfad 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.

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

Aktuelles Alter ermitteln
Aus einem Datum soll mittels Abfrage das aktuelle Alter ermittelt werden. Siehe hierzu die Funktionen im Anhang zu diesem Base-Handbuch.
SELECT DATEDIFF('yy',"Geburtsdatum",CURDATE()) AS "Alter" FROM
"Person"
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.
SELECT CASEWHEN( DAYOFYEAR("Geburtsdatum") > DAYOFYEAR(CURDATE()) ,DATEDIFF ('yy',"Geburtsdatum",CURDATE())-1,
DATEDIFF ('yy',"Geburtsdatum",CURDATE()))
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, dienach 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
CASEWHEN ((MONTH("Geburtsdatum") > MONTH(CURDATE())) OR((MONTH("Geburtsdatum") = MONTH(CURDATE())) AND (DAY("Geburtsdatum") >DAY(CURDATE()))) ,DATEDIFF('yy',"Geburtsdatum",CURDATE())-1,
DATEDIFF('yy',"Geburtsdatum",CURDATE()))
Codeschnipsel

21

wird das Ziel erreicht. Solange der Monat des Geburtsdatum 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 imaktuellen 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:
CASE
WHEN MONTH("Geburtsdatum") > MONTH(CURDATE())
THEN DATEDIFF('yy',"Geburtsdatum",CURDATE())-1
WHEN (MONTH("Geburtsdatum") = MONTH(CURDATE()) AND DAY("Geburtsdatum")> DAY(CURDATE()))
THEN DATEDIFF('yy',"Geburtsdatum",CURDATE())-1
ELSE DATEDIFF('yy',"Geburtsdatum",CURDATE())
END
Auf diese Formulierung reagiert die GUI nicht mit einer Fehlermeldung. Das Alter wird jetzt auch in
Schaltjahren genau ausgegeben und die Abfrage bleibt editierbar.

Geburtstage in den nächsten Tagen anzeigen
Mit Hilfe von einigen kleinen Berechnungsgriffen lässt sich auch aus einer Tabelle z.B. auslesen,
wer in den nächsten 8 Tagen Geburtstag hat.
SELECT *FROM "Tabelle"
WHERE
DAYOFYEAR("Datum") BETWEEN DAYOFYEAR(CURDATE()) AND
DAYOFYEAR(CURDATE()) + 7
OR DAYOFYEAR("Datum") < 7 DAYOFYEAR(CAST(YEAR(CURDATE())||'-12-31' AS DATE)) +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 desJahres 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 inder 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:
SELECT *FROM "Tabelle"
WHERE
CASE
WHEN
DAYOFYEAR(CAST(YEAR("Datum")||'-12-31' AS DATE)) = 366
AND DAYOFYEAR("Datum") > 60 THEN DAYOFYEAR("Datum") - 1
ELSE
DAYOFYEAR("Datum")END
BETWEEN
CASE

Codeschnipsel

22

WHEN
DAYOFYEAR(CAST(YEAR(CURDATE())||'-12-31' AS DATE)) = 366
AND DAYOFYEAR(CURDATE()) > 60 THEN DAYOFYEAR(CURDATE()) - 1
ELSE
DAYOFYEAR(CURDATE())
END
AND
CASE
WHEN
DAYOFYEAR(CAST(YEAR(CURDATE())||'-12-31' AS DATE)) = 366
AND DAYOFYEAR(CURDATE()) > 60 THEN DAYOFYEAR(CURDATE()) + 6
ELSE
DAYOFYEAR(CURDATE()) + 7
END
OR DAYOFYEAR("Datum") < 7 DAYOFYEAR(CAST(YEAR(CURDATE())||'-12-31' AS DATE)) +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 undaußerdem für die korrekte Zählung der 60. Tag im Jahr ist (31 Tage im Januar und 29 Tage imFebruar). 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, dadas 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.

Tage zu Datumswerten addieren
Bei der Ausleihe von Medien möchte vielleicht der Entleiher genau wissen, an welchem Tag denndie 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.
Hier 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 darauseine fortlaufende Nummer und ein fortlaufendes Datum entstehen.
Anschließend wird diese Tabelle mit den Spaltenüberschriften zusammen markiert und in Baseeingefü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.

Codeschnipsel

23

Tipp

Auch über Abfragetechniken ist die Erstellung so einer Übersicht möglich. DieseÜbersicht kann, wenn eine Filtertabelle genutzt wird, in Startdatum und ihrem
Umfang an Datumswerten sogar gesteuert werden:
SELECT DISTINCT CAST
( "Y"."Nr" + (SELECT "Jahr" FROM "Filter" WHERE "ID" = True) - 1 || '-' ||CASEWHEN( "M"."Nr" < 10, '0' || "M"."Nr", '' || "M"."Nr" ) || '-' ||CASEWHEN( "D"."Nr" < 10, '0' || "D"."Nr", '' || "D"."Nr" )AS DATE ) AS "Datum"
FROM "NrBis31" AS "D", "NrBis31" AS "M", "NrBis31" AS "Y"
WHERE "Y"."Nr" <= (SELECT "Jahre" FROM "Filter" WHERE "ID" = True)
AND "M"."Nr" <= 12 AND "D"."Nr" <= 31
Diese Ansicht greift auf eine Tabelle zu, die lediglich die Nummern von 1 bis 31
erfasst und schreibgeschützt ist. In einer weiteren Filtertabelle wird das Startjahr und
der Umfang in Jahren festgelegt, den die Ansicht umfassen soll. Das Datum wird
dadurch zusammengestellt, dass ein Datumsausdruck aus Jahr, Monat und Tag als
Text erstellt und anschließend in ein Datum umgewandelt wird. Die HSQLDB akzeptiert alle Tage bis zum 31. eines Monats, auch z.B. den 31.02.2015. Aus dem
31.02.2015 wird allerdings entsprechend der 3.03.2015 wiedergegeben. Deshalbmuss bei der Erstellung der Ansicht über DISTINCT ausgeschlossen werden, dass
Datumswerte doppelt vorkommen.
Hierauf greift die folgende Ansicht zu:
SELECT "a"."Datum",(SELECT COUNT(*) FROM "Ansicht_Datum" WHERE "Datum" <="a"."Datum")AS "lfdNr"
FROM "Ansicht_Datum" AS "a"Über eine Zeilennummerierung wird den Datumswerten eine Nummer hinzugefügt.
Da aus Ansichten sowieso keine Daten gelöscht werden können muss hier natürlich
nicht extra ein Schreibschutz bemüht werden.

Mit einer Abfrage kann jetzt zu einem bestimmten Datum ermittelt werden, wie z.B. das Datum in
14 Tagen lauten wird:
SELECT "a"."Leih_Datum",(SELECT "Datum" FROM "Datum" WHERE "ID" =(SELECT "ID" FROM "Datum" WHERE "Datum" = "a"."Leih_Datum")+14)
AS "Rueckgabedatum"
FROM "Ausleihe" AS "a"In der ersten Spalte wird das Ausleihdatum ermittelt. Auf diese Spalte greift eine korrelierende
Unterabfrage zu, die wieder in zwei Abfragen geschachtelt ist. Es wird über SELECT "ID" FROM
"Datum" der Wert des Feldes "ID" ermittelt, der dem Ausleihdatum entspricht. Zu diesem Wert
wird 14 addiert. Dieser Wert wird dem Feld "ID" bei der äußeren Unterabfrage zugewiesen. Zu dieser neuen "ID" wird nachgesehen, welches Datum im Datumsfeld des Datensatzes steht.
Leider wird bei der Anzeige in der Abfrage der Datumstyp nicht automatisch erkannt, so dass hierentsprechend formatiert werden muss. In einem Formular ist die entsprechende Anzeige aber dauerhaft speicherbar, so dass auch bei jeder Abfrage entsprechend ein Datumswert ausgegeben
wird.
Eine Direktvariante zur Ermittlung des Datumswertes ist sogar auf kürzerem Wege möglich. Hierbei wird der Startwert genutzt, ab dem die interne Tageszählung von Base beginnt:
SELECT "Leih_Datum",
DATEDIFF( 'dd', '1899-12-30', "Leih_Datum" ) + 14
AS "Rueckgabedatum"
FROM "Tabelle"
Codeschnipsel

24

Der ermittelte Zahlenwert kann im Formular als Datum über ein formatiertes Feld dargestellt werden. Es ist allerdings nur mit großem Aufwand möglich, ihn einfach für weiteren Abfragecode in ein
SQL-Datum zu übertragen.

Zeiten zu Zeitstempeln addieren
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 Zeitdargestellt werden.
Im Gegensatz zur Addition von Tagen zu einem Datum tritt bei den Zeiten allerdings ein Problem
auf, das anfangs vielleicht gar nicht auffällt:SELECT "DatumZeit"
DATEDIFF( 'ss', '1899-12-30', "DatumZeit" ) / 86400.0000000000 +36/24 AS "DatumZeit+36Stunden"
FROM "Tabelle"
Für die neue Zeitberechnung wird der Unterschied zur Startzeit '0' des Systems genommen. Das
ist wie beim 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 Tagenund 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 einer Ü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:
SELECT "DatumZeit"
DATEDIFF( 'dd', '1899-12-30', "DatumZeit" ) +Codeschnipsel

25

HOUR( "DatumZeit" ) / 24.0000000000 +MINUTE( "DatumZeit" ) / 1440.0000000000 +SECOND( "DatumZeit" ) / 86400.0000000000 +36/24
AS "DatumZeit+36Stunden"
FROM "Tabelle"
Statt die Stunden, Minuten und Sekunden aus dem Ursprungsdatum zu beziehen werden sie hierim Verhältnis zum aktuellen Datum dargestellt. Am 20.05.2015 steht die Uhr auf 13:00 Uhr, würdeaber 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 unddes 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. 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 undEnde der Sommerzeiten aufnimmt und dann mit einer Stundenkorrektur einspringt. Ein
etwas aufwändigeres Verfahren.
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 werdenoder ü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.

Laufenden Kontostand nach Kategorien ermitteln
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.
SELECT "ID", "Betrag", ( SELECT SUM( "Betrag" ) FROM "Kasse" WHERE"ID" <= "a"."ID" ) AS "Saldo" 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.
SELECT "ID", "Datum", "Betrag", ( SELECT SUM( "Betrag" ) FROM "Kasse"
WHERE "Datum" <= "a"."Datum" ) AS "Saldo" 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.
SELECT "ID", "Datum", "Betrag", "Konto_ID",( SELECT "Konto" FROM "Konto" WHERE "ID" = "a"."Konto_ID" ) AS
"Kontoname",( SELECT SUM( "Betrag" ) FROM "Kasse" WHERE "Datum" <= "a"."Datum" AND"Konto_ID" = "a"."Konto_ID" ) AS "Saldo",( SELECT SUM( "Betrag" ) FROM "Kasse" WHERE "Datum" <= "a"."Datum" )AS "Saldo_gesamt"
Codeschnipsel

26

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 erscheint. Da sichdie 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.

Zeilennummerierung
Automatisch hochzä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.
SELECT "ID", ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE "ID" <="a"."ID" ) AS "lfdNr." 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.
Wird einer Abfrage eine Bedingung hinzugefügt oder beruht eine Abfrage aus einer Verbindungvon mehreren Tabellen, so ist diese Bedingung bzw. die Verbindung von mehreren Tabellen auchin der korrelierenden Unterabfrage hinzuzufügen. Andernfalls stimmt die Zählung nicht:
SELECT "Name", ( SELECT COUNT( "ID" ) FROM "Name" WHERE "GebDat" >'1995-12-31' AND "ID" <= "a"."ID" ) AS "lfdNr." 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:
SELECT "ID", ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE "ID" <="a"."ID" AND "RechnungsID" = "a"."RechnungsID" ) AS "lfdNr." 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 derSortierung entsprechend abzubilden. Dabei muss die Sortierung allerdings einen eindeutigen Wert
ergeben. Sonst liegen 2 Nummern auf dem gleichen Wert.
SELECT "Name", ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE "Name" <="a"."Name" ) AS "lfdNr."
FROM "Tabelle" AS "a"
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.
SELECT "Name", ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE"Name"||"ID" <= "a"."Name"||"a"."ID" ) AS "lfdNr."
FROM "Tabelle" AS "a"Codeschnipsel

27

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 nochabhelfen will muss entsprechend mit Leerstellen oder Nullen auffüllen:
SELECT "Name", ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE "Name"||RIGHT('000000000'||"ID",10) <= "a"."Name"||RIGHT('000000000'||"a"."ID",10) ) AS "lfdNr."
FROM "Tabelle" AS "a"
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:
SELECT "ID", ( SELECT COUNT( "ID" ) + 1 FROM "Tabelle" WHERE "Zeit" <"a"."Zeit" ) AS "Platz" 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.
SELECT "ID", ( SELECT COUNT( "ID" ) + 1 FROM "Tabelle" WHERE "Zeit" <"a"."Zeit" ) AS "Platz",
CASE WHEN( SELECT COUNT( "ID" ) + 1 FROM "Tabelle" WHERE "Zeit" = "a"."Zeit" )= 1
THEN ( SELECT COUNT( "ID" ) + 1 FROM "Tabelle" WHERE "Zeit" <"a"."Zeit" )ELSE (SELECT ( SELECT COUNT( "ID" ) + 1 FROM "Tabelle" WHERE "Zeit" <"a"."Zeit" ) + COUNT( "ID" ) FROM "Tabelle" WHERE "Zeit" = "a"."Zeit""ID" < "a"."ID"END
AS "Zeilennummer" 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" <Codeschnipsel

28

"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, sokann darauf mit einer weiteren Abfrage problemlos zugegriffen werden. Als einfaches Beispiel hiernoch einmal die erste Abfrage zur Nummerierung, nur um ein Feld ergänzt:SELECT "ID", "Name", ( SELECT COUNT( "ID" ) FROM "Tabelle" WHERE "ID"<= "a"."ID" ) AS "lfdNr." 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:
SELECT "Name" AS "Name_1", ( SELECT "Name" FROM "Ansicht1" WHERE"lfdNr." = 2 ) AS "Name_2", ( SELECT "Name" FROM "Ansicht1" WHERE"lfdNr." = 3 ) AS "Name_3" 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:
SELECT "ID", "Name", "Nachname", ( SELECT COUNT( "ID" ) FROM "Tabelle"
WHERE "ID" <= "a"."ID" AND "Nachname" = "a"."Nachname") AS "lfdNr."
FROM "Tabelle" AS "a"Jetzt kann über die erstellte Ansicht eine entsprechende Familienzusammenstellung erfolgen:
SELECT "Nachname", "Name" AS "Name_1", ( SELECT "Name" FROM "Ansicht1"
WHERE "lfdNr." = 2 AND "Nachname" = "a"."Nachname") AS "Name_2",( SELECT "Name" FROM "Ansicht1" WHERE "lfdNr." = 3 AND "Nachname" ="a"."Nachname") AS "Name_3" FROM "Ansicht1" AS "a" 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.

Codeschnipsel

29

Zeilenumbruch durch eine Abfrage erreichen
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 kompletteinzulesen.
Der Zeilenumbruch innerhalb einer Abfrage erfolgt durch 'Char(13)'. Beispiel:
SELECT "Vorname"||' '||"Nachname"||Char(13)||"Straße"||Char(13)||"Ort"
FROM "Tabelle"
Dies erzeugt nachher:
Vorname Nachname
StraßeOrt
Mit so einer Abfrage, zusammen mit einer Nummerierung jeweils bis zur Nummer 3, lassen sichauch 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:
SELECT "Vorname"||' '||"Nachname"||Char(13)||Char(10)||"Straße"||Char(13)||Char(10)||"Ort" FROM "Tabelle"

Gruppieren und Zusammenfassen
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.
Dieses Beispiel entspricht in vielen Teilen dem der Zeilennummerierung. Die Gruppierung zu
einem gemeinsamen Feld stellt hier eine Ergänzung dar.
Nachname

VornameMüllerKarin

Schneider

Gerd

MüllerEgon

Schneider

VolkerMüllerMonikaMüllerRita

Wird nach der Abfrage zu:
Nachname

Vornamen

MüllerKarin, Egon, Monika, Rita

Schneider

Gerd, Volker

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:
Codeschnipsel

30

SELECT "Nachname", "Vorname", ( SELECT COUNT( "ID" ) FROM "Name" WHERE"ID" <= "a"."ID" AND "Nachname" = "a"."Nachname" ) AS "GruppenNr" 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.
SELECT DISTINCT "Nachname",( SELECT "Vorname" FROM "Ansicht_Gruppe" WHERE "Nachname" ="a"."Nachname" AND "GruppenNr" = 1 ) ||IFNULL( ( SELECT ', ' || "Vorname" FROM "Ansicht_Gruppe" WHERE"Nachname" = "a"."Nachname" AND "GruppenNr" = 2 ), '' ) ||IFNULL( ( SELECT ', ' || "Vorname" FROM "Ansicht_Gruppe" WHERE"Nachname" = "a"."Nachname" AND "GruppenNr" = 3 ), '' ) ||IFNULL( ( SELECT ', ' || "Vorname" FROM "Ansicht_Gruppe" WHERE"Nachname" = "a"."Nachname" AND "GruppenNr" = 4 ), '' ) ||IFNULL( ( SELECT ', ' || "Vorname" FROM "Ansicht_Gruppe" WHERE"Nachname" = "a"."Nachname" AND "GruppenNr" = 5 ), '' )AS "Vornamen"
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.

Codeschnipsel

31