Embedded SQL

Vorbemerkungen
Inhalt
1. Datenbankprogrammierung auf der Client-Seite: Embedded SQL, JDBC und SQLJ
2. Datenbankprogrammierung auf der Server-Seite: Stored Procedures
3. Erweiterbarkeit von Datenbanksystemen: UDFs, UDTs und LOBs
4. Zugriffsstrukturen für komplexe Objekte
5. Anfrageübersetzung und -optimierung
6. Aktive Datenbanksysteme, Trigger
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
11
¨ der Datenbank-Programmierung
Ansatze
1. Datenbank-Programmierung
1. Datenbank-Programmierung
• SQL ist eingeschränkt bezüglich der algorithmischen Mächtigkeit, z.B. Berechnung
einer transitiven Hülle ist nicht möglich.
• Die Einschränkung ist von Bedeutung für die Terminierung, die Endlichkeit von
Ergebnissen und die Optimierbarkeit.
• Für Datenbank-Anwendungen braucht man oft die vollständige Mächtigkeit einer
Programmiersprache. Ansätze hierfür:
– Einbettung von SQL in eine Wirtssprache
– Erweiterung von SQL um Kontrollstrukturen
– Erweiterung existierender Programmiersprachen zu persistenten Programmiersprachen
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
23
1. Datenbank-Programmierung
Einbettung von SQL
Einbettungstechniken für SQL
• prozedurale Schnittstelle / call-Schnittstelle
Dem Programmierer wird eine Bibliothek von Prozeduren zur Verfügung gestellt,
die den Zugriff und die Manipulation der Datenbank gewährleisten.
Eine eigenständige Datenbanksprache wird nicht direkt eingesetzt.
ODBC, JDBC, SQL/CLI (call level interface)
• Einbettung in eine Wirtssprache
SQL wird als eigenständige Sprache in eine existierende Sprache (Wirtssprache,
z.B. C) eingebettet und somit direkt eingesetzt.
Die Syntax und der Compiler der Wirtssprache wird nicht modifiziert.
Embedded SQL, SQLJ
• Spracherweiterungen / Sprachentwicklungen
Existierende Programmiersprachen werden um Datenbankfunktionalit ät erweitert
bzw. es werden neue dedizierte Sprachen entwickelt.
Im folgenden untersuchen wir den Ansatz der Einbettung von SQL in eine Wirtssprache.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
24
1. Datenbank-Programmierung
Einbettung von SQL
Statische Einbettung
• SQL-Anweisungen werden in den Programmtext eingestreut und syntaktisch gekennzeichnet.
• Ein Precompiler ersetzt die SQL-Anweisungen in Anweisungen für eine prozedurale Schnittstelle der Wirtssprache.
• Das vom Precompiler generierte Programm kann von einem gewöhnlichen Compiler für die Wirtssprache übersetzt werden.
☞ Die SQL-Anweisungen müssen zur Übersetzungszeit feststehen.
☞ Nur Werte in Ausdrücken der Where-Klausel sind variabel.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
25
1. Datenbank-Programmierung
Einbettung von SQL
Dynamische Einbettung
• SQL-Anweisungen können zur Laufzeit konstruiert werden.
• Aus Sicht der Wirtssprache werden die SQL-Anweisungen in Form von Zeichenketten zur Verfügung gestellt.
• Eine syntaktische und semantische Analyse der SQL-Anweisung findet ebenfalls
erst zur Laufzeit statt.
• Vom Datenbanksystem werden spezifische Prozeduren für die Behandlung variabler Ergebnisrelationen bereitgestellt.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
26
1. Datenbank-Programmierung
Einbettung von SQL
Cursor-Konzept
• Gewöhnliche Programmiersprachen bieten zwar Strukturen aber keine Mengen als
Datentyp an.
• Eine Ergebnisrelation in SQL ist aber eine Menge von Tupeln.
☞ Das Ergebnis einer SQL-Anfrage kann nicht direkt in einen Wert eines Datentyps
der Wirtssprache abgebildet werden (impedance mismatch).
• Das Konzept des Cursors bietet eine abstrakte Sichtweise auf eine Ergebnisrelation.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
27
1. Datenbank-Programmierung
Einbettung von SQL
Cursor
Anwendungsprogramm
☞ Ein Cursor stellt eine Art Zeiger auf die Zeilen einer Ergebnisrelation dar.
Datenbank
SQL−Anfrage
C++
Cursor
Ergebnisrelation
☞ Ein gutes Bild ist es, sich den
Cursor als einzeiliges Fenster
auf die Ergebnisrelation vorzustellen.
☞ Dieses Fenster wird schrittweise über die Ergebnisrelation geschoben.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
28
1. Datenbank-Programmierung
Einbettung von SQL
Cursor-Anweisungen
DECLARE CURSOR: Mit einer Cursordeklaration wird u.a. der Name des Cursors
und die zugehörige Cursortabelle (Ergebnisrelation) definiert.
DECLARE Cursorname [SCROLL] CURSOR [WITH HOLD]
FOR Abfrageausdruck [ORDER BY-Klausel ] [Modifikationsklausel ]
Modifikationsklausel:
FOR READ ONLY |
UPDATE [OF Spaltenname {, Spaltenname }]
declare caddr cursor for
select customer_id, name
from customer
where address = ’Stanford’
• Der Abfrageausdruck (Cursorformel) ist der wichtigste Bestandteil einer CursorAnweisung. Er legt die Cursortabelle fest.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
29
1. Datenbank-Programmierung
Einbettung von SQL
• Durch SCROLL stehen zusätzliche Positionierungsmöglichkeiten des Cursors bei
FETCH INTO zur Verfügung. Nur in Zusammenhang mit READ ONLY m öglich.
Ohne SCROLL: nur sequentieller Durchlauf
• WITH HOLD: Cursor bleibt über ein COMMIT hinaus im geöffneten Zustand.
OPEN CURSOR: Berechnung der Ergebnisrelation
OPEN Cursoname
FETCH INTO: Positioniert den Cursor auf ein bestimmtes Tupel der Cursortabelle und
überträgt die Attributwerte dieses Tupels in Variablen der Wirtssprache.
FETCH [[FETCH-Orientierung ] FROM] Cursoname
INTO Zielspez {, Zielspez }
FETCH-Orientierung:
NEXT | PRIOR | FIRST | LAST |
ABSOLUTE wert | RELATIVE wert
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
30
1. Datenbank-Programmierung
Einbettung von SQL
fetch caddr into :cid, :name
UPDATE CURRENT: Ändern des aktuellen Tupels
UPDATE Relationenname
SET-Klausel
WHERE CURRENT OF Cursorname
DELETE CURRENT: Löschen des aktuellen Tupels
DELETE FROM Relationenname
WHERE CURRENT OF Cursorname
CLOSE: Freigabe der Ergebnisrelation
CLOSE Cursorname
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
31
1. Datenbank-Programmierung
Embedded SQL
Embedded SQL (C und C++)
• Syntaktische Auszeichnung von SQL-Anweisungen
Alle eingebetteten SQL-Anweisungen müssen mit dem Schlüsselwort exec sql
beginnen, um vom Precompiler erkannt zu werden.
• Weiterhin müssen die SQL-Anweisungen mit einem Semikolon abgeschlossen
werden.
Beispiel: Cursor-Deklaration
exec sql declare caddr cursor for
select customer_id, name
from customer
where address = ’Stanford’;
• Deklaration von Host-Variablen
Variablen, die sowohl in SQL-Anweisungen als auch in der Wirtssprache verwendet
werden, heißen Host-Variablen (host variables).
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
32
1. Datenbank-Programmierung
Embedded SQL
Sie müssen in einem separaten Deklarationsblock (declare section) deklariert werden.
exec sql begin declare section;
long customer_id;
exec sql end declare section;
• Verwendung von Host-Variablen
Host-Variablen können in SQL-Anweisungen wie Konstanten benutzt werden. Sie
werden durch einen vorangestellten Doppelpunkt gekennzeichnet:
exec sql delete
from customer
where customer_id = :customer_id;
Die den SQL-Datentypen entsprechenden Programmiersprachen-Datentypen
hängen von der jeweiligen Wirtssprache ab.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
33
1. Datenbank-Programmierung
Embedded SQL
• Behandlung von Ergebnisrelationen
Ist garantiert, daß eine Anfrage höchstens ein Tupel liefert, so kann die intoKlausel benutzt werden, um das Ergebnis der Anfrage in Host-Variablen abzulegen.
exec sql select name, address
into :name, :address
from customer
where customer_id = :customer_id;
• Zur Behandlung von Nullwerten können Indikator-Variablen verwendet werden.
Indikator-Variablen sind Host-Variablen vom Datentyp int oder short.
In der SQL-Anweisung werden sie direkt hinter der Host-Variablen für die Aufnahme des Wertes angegeben.
Ist die Indikator-Variable mit einem negativen Wert belegt, wird dadurch ein Nullwert angezeigt.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
34
1. Datenbank-Programmierung
Embedded SQL
Beispiel: Nicht gespeicherte Adressen erkennen:
exec sql select name, address
into :name, :address:addiv
where customer_id = :customer_id;
• Cursor
Das Öffnen eines Cursors erfolgt mit einer open-Anweisung:
exec sql open caddr;
Für die Übertragung der Tupelwerte in die Hostvariablen dient die fetchAnweisung:
exec sql fetch caddr into :cid, :name;
Bei Bedarf sind Indikatorvariablen zu verwenden.
Mit der close-Anweisung wird ein Cursor geschlossen:
exec sql close caddr;
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
35
1. Datenbank-Programmierung
Embedded SQL
• Fehlerbehandlung
Um Fehlersituationen zu erkennen, wird die sogenannte SQL Communication Area
(SQLCA) in ein Anwendungsprogramm eingebunden:
exec sql include sqlca;
In der SQLCA ist u.a. die Variable sqlcode definiert, die den Status der letzten
Ausführung eines SQL-Befehls angibt:
sqlcode
0
<0
100
> 0, 6= 100
Bedeutung
OK
Fehler
Kein Tupel gefunden
Warnung
Die whenever-Anweisung ermöglicht eine flexible Reaktion auf Fehler:
exec sql whenever Bedingung Aktion ;
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
36
1. Datenbank-Programmierung
Embedded SQL
Als Bedingungen sind definiert: not found, sqlwarning, sqlerror
Die Aktion ist entweder continue oder goto label .
• Öffnen und Schließen einer Datenbank-Verbindung
Dieser Aspekt ist nicht standardisiert. Üblicherweise werden hierzu die Befehle:
exec sql connect Datenbank ;
und
exec sql disconnect Datenbank ;
verwendet.
• Transaktionssteuerung
Zum Commit einer Transaktion benutzt man:
exec sql commit work;
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
37
1. Datenbank-Programmierung
Embedded SQL
Das Zurücksetzen einer Transaktion geschieht mit:
exec sql rollback work;
Weiterhin können Transaktionsattribute (Zugriffsart, Isolationsstufe) gesetzt werden:
exec sql set transaction isolation level serializable;
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
38
1. Datenbank-Programmierung
Embedded SQL
meinprog.sqc
Quelldatei
Precompiler
Schritte zur Erstellung
eines
Anwendungsprogramms bei der Verwendung von Embedded
SQL:
1. Precompiler
2. Compiler
sqlprep meinprog.sqc datenbank
meinprog.c
C−Datei
C−Compiler
cc −c −I/usr/IBMdb2/V7.1/include meinprog.c
meinprog.o
Objekt−Datei
Bibliotheken
3. Linker
Linker
cc −o meinprog −L/usr/IBMdb2/V7.1/lib meinprog.o −ldb2
Programm
meinprog
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
39
1. Datenbank-Programmierung
Embedded SQL
• Diese Schritte sind rein prinzipieller Natur.
• Die Erstellung von Anwendungsprogrammen bei der Verwendung von Embedded
SQL ist in keinster Weise standardisiert!
• Je nach Datenbanksystem gibt es Unterschiede in
– der Einbindung zusätzlicher include-Dateien,
– den Namen und den Option des Precompilers,
– den verwendeten Fehlercodes,
– der Verbindung der übersetzten SQL-Statements mit einer Datenbank,
– den einzubindenden Bibliotheken,
– etc.
• Darüberhinaus gibt es deutliche Unterschiede zwischen den SQL-Dialekten.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
40
1. Datenbank-Programmierung
Embedded SQL
Wieviele Tupel enthält die Tabelle words in einer Datenbank? (DB2)
#include <stdio.h>
#include <string.h>
#include <sqlenv.h>
EXEC SQL INCLUDE SQLCA;
int main( int argc, char * argv[] )
{
EXEC SQL BEGIN DECLARE SECTION;
char db[9];
long nwords;
EXEC SQL END DECLARE SECTION;
memset( db, 0, sizeof( db ) ); strcpy( db, argv[1] );
EXEC SQL CONNECT TO :db;
if ( sqlca.sqlcode != 0 )
{
fprintf( stderr, "db2: %s (%d)\n", sqlca.sqlerrmc, sqlca.sqlcode );
exit( 1 );
}
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
41
1. Datenbank-Programmierung
Embedded SQL
EXEC SQL SELECT count(*) INTO :nwords FROM words;
if ( sqlca.sqlcode != 0 )
fprintf( stderr, "db2: %s (%d)\n", sqlca.sqlerrmc, sqlca.sqlcode );
else
printf( "%ld\n", nwords );
EXEC SQL DISCONNECT :db;
}
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
42
1. Datenbank-Programmierung
Embedded SQL
Der Programmtext liege in der Datei first.sqc.
Erzeugen und Ausführen des Programms:
$ sqlprep first.sqc testdb
...
LINE
MESSAGES FOR first.sqc
------ -------------------------------------------------------------------SQL0060W The "C" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.
$ cc -c first.c -I/usr/IBMdb2/V7.1/include
$ cc -o first -L/usr/IBMdb2/V7.1/lib first.o -ldb2
$ first testdb
45407
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
43
1. Datenbank-Programmierung
Embedded SQL
Dynamic SQL
• Bisher müssen alle SQL-Anweisungen vor der Compilierung feststehen. Nur die
Belegung der Host-Variablen ist modifizierbar.
• Somit ist es beispielsweise nicht möglich, die Bedingungen im where-Teil der Anfrage erst zur Laufzeit festzulegen.
• Um derartiges zur Laufzeit zu ermöglichen wurde Dynamic SQL entwickelt.
• In Dynamic SQL werden SQL-Anfragen als Zeichenketten in Host-Variablen abgelegt.
• Spezielle SQL-Variablen enthalten die übersetze und optimierte Anfrage.
• Die Struktur der Tupel einer Resultatsmenge kann mit einer SQL Descriptor Area
(SQLDA) ermittelt werden.
• Dynamic SQL ist in keinster Weise standardisiert.
• Aufgaben by Dynamic SQL:
– Vorbereiten einer SQL-Anweisung
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
44
1. Datenbank-Programmierung
Embedded SQL
– Erhalten einer Beschreibung der Struktur der Ergebnistupel
– Ausführen einer vorbereiteten SQL-Anweisung
– zeilenweises Lesen der Ergebnismenge
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
45
1. Datenbank-Programmierung
Embedded SQL
• prepare Erzeugung eines Zugriffsplans für eine SQL-Anweisung und Übersetzung
dieser Anweisung in eine ausführbare Form:
exec sql prepare s1 from :anweisung;
exec sql prepare q1 from :anfrage;
• describe Ermitteln einer Beschreibung der Datentypen der Ergebnismenge:
exec sql describe q1 into :sqlda;
• execute Ausführen einer vorbereiteten SQL-Anweisung;
nur möglich, wenn die Anweisung keine Resultatsmenge liefert
exec sql execute s1;
• Dynamische Cursor-Deklaration Deklaration eines Cursors für eine Anfrage
exec sql declare c1 cursor for q1;
• Dynamisches Open Öffnen des dynamischen Cursors
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
46
1. Datenbank-Programmierung
Embedded SQL
exec sql open c1;
• Dynamisches Fetch Ablegen eines Ergebnistupels in der SQL Descriptor Area
exec sql fetch c1 using descriptor :sqlda;
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
47