Abfragen / Queries

• » Home
• »Server-Daten
• »Menü-Hilfe
• » Abfragen

Abfragen sind das zentrale Werkzeug, die in den vielen Tabellen gesammelten Daten nach eigenen Wünschen zusammenzufassen und sie auszugeben. Hierbei können sämtliche Sql-Standard-Select-Befehle (Select, From, Where, Group By, Having, Union, Order By) und Unterabfragen verwendet werden. Ferner ist die Deklaration von Parametern gestattet. Diese können entweder mit vorbelegten oder mit interaktiv eingegebenen Werten belegt werden. Die Ausgabe kann sowohl im Rahmen der internen Masken als auch auf Ausgabeseiten erfolgen.

Bei der Bearbeitung einer Abfrage wird das folgende Menü genutzt. Bei der Ersterstellung wird in der linken Liste nur ein Menüpunkt für die neue Abfrage mit deren Namen angezeigt. Erst wenn eine Abfrage einmal gespeichert wurde, folgen die beiden anderen Menüpunkte für Parametervorbelegungen und die Ausgabe des Abfrageergebnisses.

Suchen

Neue Abfrage

Speichern

Abbruch

---

Hilfe

Übersicht  (1/2) ≪ < 1 > ≫  -    myFirstQuery    - Parameter    - Output  -    mySecondQuery    - Parameter    - Output

Id ↑↓ Abfragename * ↑↓ Kommentar ↑↓ Status * activeinactivecreate againerror↑↓ Ausgabeseite ---abfrage-und-editieren.htmlcalendar.htmlindex.htmlk2.htmlkontakt.htmlkontaktformular.htmlk-test.htmlkundenrechnungen.htmll2.htmllinkliste.html↑↓ Xml-Ausgabe * no XMLonly dataonly schemadata and schema↑↓ Sql-Code * Declare @i int, @dT dateTime Select A.A_Name As ArtikelName, V.V_Name As VertreterName, U.Datum, UmsatzId, U.A_Stueck, U.A_Stueck * A.A_Preis As EinzelUmsatz From Artikel As A Inner Join Umsatz As U On A.ArtikelId = U.ArtikelId Inner Join Vertreter As V On U.VertreterId = V.VertreterId Where ((U.UmsatzId > @i) Or (@i Is Null)) And ((U.Datum > @dT) Or (@dT Is Null)) Order By EinzelUmsatz Desc, U.Datum, ArtikelName↑↓ Owner adminserver-daten Protokoll 2005-09-18 21:53:30, admin    Liste Löschen PDF generieren

Notwendig sind der Name der Abfrage, der Status und der Sql-Code. 'Kommentar' ist ein zusätzliches Feld, das ansonsten nicht weiter berücksichtigt wird.

Das Feld 'Ausgabeseite' ermöglicht die Festlegung einer Ausgabeseite zu dieser Abfrage. In diesem Fall muß die Abfrage zwei Spalten 'print_id' und 'mail' zurückgeben. Weitere Spalten können hinzugefügt werden, um die Abfrage zu testen. Beim Klick auf 'PDF generieren' wird zunächst die Abfrage ausgeführt. Anschließend wird die Ausgabeseite mit jeder print_id als Parameter aufgerufen und aus dieser Darstellung ein PDF-Dokument erzeugt. Dieses wird schließlich an die Liste der dieser print_id zugeordneten Mailadressen gesandt. Damit läßt sich eine Art Serienbrief-Funktionalität realisieren. Näheres hierzu ist unter PDF-Dokumente zu finden.

<Xml-Ausgabe>: Abfragen können als Xml-Quelle aufgerufen werden. Alles weitere ist unter Xml-Ausgaben zugänglich.

Der Sql-Code kann mit der Deklaration von Parametern beginnen. Im obigen Beispiel sind die beiden Parameter @i und @dT mit den Datentypen int und dateTime deklariert. Diese können in der Abfrage überall anstelle von Spaltennamen oder Konstanten verwendet werden. Wurde eine Abfrage mit Parametern deklariert und erfolgreich gespeichert, so werden die Parameter extrahiert und gesondert abgelegt. Dies erfolgt nach jedem Speichern, jeder Speicherversuch löscht die bisherigen Parameter. Interaktiv können in der folgenden Maske Vorbelegungen erfolgen:

Suchen

Neue Abfrage

Abbruch

---

Hilfe

Übersicht  (1/2) ≪ < 1 > ≫  -    myFirstQuery    - Parameter    - Output  -    mySecondQuery    - Parameter    - Output

Id Name Value 5 @i 7 @dT

Dieses Menü kann auf zwei Arten genutzt werden. Entweder werden Parameterwerte direkt festgelegt und anschließend zum Output gewechselt. Dann gelten die Parameter nur für diese Ausführung. Oder die Parameter werden nach der Eingabe gespeichert. Dann werden die Werte als Vorbelegungen genutzt und beim nächsten Aufruf dieser Maske erneut angezeigt. Falls eine Abfrage keine Parameter nutzt, enthält dieser Menüpunkt keine Eingabemöglichkeit.

Die Ausgabe einer Abfrage kann wie folgt aussehen:

Suchen

Neue Abfrage

Abbruch

---

Hilfe

Übersicht  (1/2) ≪ < 1 > ≫  -    myFirstQuery    - Parameter    - Output  (1/9) ≪ < 1 > ≫  -    mySecondQuery    - Parameter    - Output

ArtikelName VertreterName Datum UmsatzId A_Stueck EinzelUmsatz Mantel Meier, Franz 25.06.1999 00:00:00 5 35 12600,0000 Hose Meyer, Emil 24.06.1999 00:00:00 6 35 3867,5000 Mantel Meier, Franz 24.06.1999 00:00:00 2 10 3600,0000 Oberhemd Meyer, Emil 24.06.1999 00:00:00 3 70 3094,0000 Oberhemd Meyer, Emil 24.06.1999 00:00:00 1 40 1592,0000 Oberhemd Meyer, Emil 25.06.1999 00:00:00 9 20 884,0000 Oberhemd Schulze, Fritz 25.06.1999 00:00:00 4 20 884,0000 Hose Schulze, Fritz 24.06.1999 00:00:00 7 5 552,5000 Oberhemd Schulze, Fritz 24.06.1999 00:00:00 8 10 398,0000

Die linke Leiste enthält eine zusätzliche Gliederung, aus welcher, analog zur Standardleiste, die Zahl der von der Abfrage zurückgelieferten Zeilen sowie die aktuelle Position hervorgeht. Ebenso stehen dieselben Links zum Blättern durch das Ergebnis zur Verfügung. Die Ausgabe des Suchergebnisses entspricht unmittelbar der Sql-Abfrage, die Sortierung wurde auf die letzte Spalte absteigend festgelegt.

Sicherheit

Die Erstellung von Abfragen richtet sich nach dem üblichen Rechteschema, es ist das Recht zum Erstellen von Abfragen oder das globale Recht zur Erstellung von Objekten notwendig. Bei der Ausführung von Abfragen wird das Execute-Recht für diese Abfrage geprüft. Es werden nicht die Einzelberechtigungen für jede Tabelle geprüft, die in der Abfrage verwendet werden. Das Recht, Abfragen zu erstellen, impliziert immer das Recht, auf diese Weise sämtliche Daten lesen zu können.

Bei der Erstellung einer Abfrage wird der Code diversen Prüfungen unterzogen. Zunächst wird die syntaktische Korrektheit mittels SqlServer-Techniken überprüft und mögliche Schlüsselwörter gefiltert, deren Verwendung nicht gestattet ist. Anschließend wird der Code mit einem eigenen Parser geprüft, um Positionen von Tabellen- und Spaltennamen zu ermitteln. Deren Korrektheit wird anschließend getestet. Schließlich werden die so gewonnenen Codefragmente um eventuelle Multilang-Verzweigungen ergänzt und eine gespeicherte Prozedur erstellt, welche die zusätzliche Logik für das Blättern durch die Suchergebnisse enthält.

Gestattet sind die üblichen Sql-Select-Anweisungen (Select, From, Join, Where, Group By, Having, Union, Order By). Nicht gestattet sind bsp. Insert/Update/Delete bzw. Select ... Into, der Zugriff auf Systemobjekte, die Nutzung von Set/Execute, die Ausführung von SqlServer-Prozeduren und die Verwendung von mehrteiligen Objektnamen.

Spezielle Parameter

Innerhalb von Abfragen können die drei folgenden Spezialparameter genutzt werden:

@USER_NAME nvarchar(50)
@USER_MAIL nvarchar(50)
@USER_ID int

Sind einer oder mehrere dieser Parameter zu Beginn der Abfrage deklariert ('Declare ...'), so werden sie vor der Ausführung der Abfrage mit dem zugeordneten Wert für den aktuellen Benutzer belegt. Ferner können sie in der Abfrage bsp. als WHERE-Kriterium genutzt werden. Damit gelingt es, Lesern nur jene Informationen zurückzugeben, welche explizit für sie freigegeben wurden, ohne daß deshalb für jeden Nutzer eine eigene Gruppe mitsamt passendem Berechtigungssatz eingerichtet werden muß. Das folgende Beispiel skizziert diese Verwendung.

Beispiel: @USER_MAIL für Prüfungsergebnisse

Eine Einrichtung nimmt vor Ort mehrere schriftliche Prüfungen ab. Diese werden im Laufe der nächsten Wochen korrigiert. Das Ergebnis soll von den Teilnehmern anschließend gesichert per Internet abrufbar sein: Jeder Prüfungsteilnehmer soll nur seine Ergebnisse lesen können, Scherzanmeldungen (mit welchen immer zu rechnen ist) dürfen keine Ergebnisse sehen.

Es werden zwei Tabellen benötigt: Eine Tabelle 'Personen' verwaltet die Grunddaten zu jedem Prüfling (laufende Nummer, Personendaten), eine Tabelle 'Testergebnisse' enthält zu jedem Prüfling (Spalte PersonenId mit Verknüpfung auf die Grundtabelle 'Personen') mehrere Detaildatensätze mit den einzelnen Prüfungsergebnissen. Zusätzlich muß jeder Kandidat bei der Prüfung eine Mailadresse angeben, welche beim Eintragen der Daten in die Datenbank in der Tabelle 'Personen' eingefügt wird. Damit kann die folgende Abfrage erstellt werden:

Declare @USER_MAIL nvarchar(50)

Select A.Nachname, A.Vorname, (weitere Daten),
B.Test_1, B.Test_2, (weitere Ergebnisse)
From Personen As A Inner Join Testergebnisse As B
On A.PersonenId = B.PersonenId
Where A.Personen_Mail = @USER_MAIL

Ferner werden drei Ausgabeseiten erstellt: Die erste bietet die Anmeldung per Mailadresse, die zweite das Standard-Login. Diese beiden Seiten müssen für den anonymen Aufruf zugelassen sein, ansonsten könnte sich niemand anmelden. Die dritte Ausgabeseite ruft die obige Abfrage auf und kann direkt von der Login-Seite her verlinkt sein. Meldet sich nun ein Teilnehmer der Prüfung mit einer Mailadresse an, so sendet ihm das Grundsystem zunächst ein zufälliges Passwort zu. Wechselt der Prüfling zum Login, so kann er sich mit seiner Mailadresse und dem soeben erhaltenen Passwort anmelden. Dies gilt allgemein - für tatsächliche Prüflinge und für Spaßanmeldungen. Bei der Ausführung der Abfrage wird der Parameter @USER_MAIL mit der Mail des aktuell angemeldeten Nutzers belegt, ohne daß der Nutzer hierauf noch einen Einfluß hat. Handelt es sich bei der Mail um eine Adresse, welche von den Mitarbeitern in die 'Personen'-Tabelle eingetragen wurde, so werden diesem Nutzer die Ergebnisse seiner Prüfung ausgegeben. Meldet sich eine dritte Person mit einer eigenen Mailadresse an, so ist deren Mailadresse keine Mail in der Tabelle 'Personen' zugeordnet. Diese Person kann sich zwar anmelden, sie erhält jedoch nur einen leeren Datensatz.

Analog kann die Sonderspalte _Owner genutzt werden, um mit der Überprüfung

_Owner = @USER_ID

die Datensätze eines Nutzers zurückzugeben, welche dieser zuvor selbst erstellt hat.

Hinweise

Nutzt eine Abfrage eine Tabelle und wird die Tabelle anschließend gelöscht oder Spalten geändert/gelöscht, so wird die Abfrage nicht automatisch geändert oder deaktiviert. Denn mittels vorgestelltem '%' kann mit der Standard-Kreuzsuche auf der ersten Maske rasch ermittelt werden, welche Abfragen eine Tabelle oder Spalte nutzen. <%Artikel> findet alle Abfragen, in welchen das Wort 'Artikel' an einer beliebigen Position im Feld <Sql-Code> verwendet wird.

Die Möglichkeit, durch das Ergebnis zu blättern, ist auf eine möglichst ausdifferenzierte Sortierung angewiesen. Wird bsp. nur nach einer Spalte sortiert und sind die Werte von Zeile 9 - 12 in der Sortierspalte identisch, so führt die Suche nach 11-20 dazu, daß zehn Datensätze ab Position 9 ausgegeben werden. Falls die Abfrage nicht gruppiert und hierdurch eindeutige Zeilen erzeugt, sollte eine Primärschlüsselspalte zur Sortierung angefügt werden, um diesen Effekt zu vermeiden. Es können bis zu drei Sortierungen angegeben werden. Bei Views existiert dieses Problem theoretisch ebenfalls, es kann dort jedoch vermieden werden, da die Primärschlüsselspalte bekannt ist. Bei Abfragen mit Gruppierungen und damit erreichten Eindeutigkeiten wäre es unsinnig, automatisiert eine Sortierspalte hinzuzufügen, da sich die Logik der Abfrage hierdurch ändern würde.

Wird eine Abfrage auf den Status 'inaktiv' gesetzt, so wird der eingegebene Code nicht geprüft und eine eventuell bereits vorhandene gespeicherte Prozedur gelöscht. Die Sicherheitseinstellungen bleiben unverändert. Dies kann nützlich sein, falls der eingegebene Code aufgrund von Fehlern zurückgewiesen wird, die Arbeit jedoch bsp. bis zum nächsten Arbeitstag unterbrochen werden soll und ein Speichern wünschenswert wäre.

Validierung des Sql-Codes

Der Sql-Code wird zunächst mit einer Transact-Sql-Routine auf syntaktische Korrektheit geprüft. Nach einer Prüfung auf nicht erlaubte Schlüsselwörter wird er mittels einer eigenen Routine auf einen binären Baum abgebildet, um die beteiligten Tabellen zu ermitteln. Aus prinzipiellen Erwägungen heraus ist die Verwendung von

Select * From Tabelle

nicht gestattet. Ferner gibt es derzeit eine bekannte Grenze bei der Validierung: Kürzel wie 'd', 'dd', 'MM' usw. die auch als Kürzel für Datums- und Zeitformatierungen verwendet werden, können nicht als Alias für Tabellen genutzt werden. Man verwende statt 'D' nun 'D1'.