Kopplung Programmiersprache - SQL

8. Kopplung
pp
g Programmiersprache
g
p
- SQL
„
Einleitung:
„ Eingebettetes SQL
– Cursor-Konzept
– positionierte Änderungsoperationen
Ä
(UPDATE, DELETE)
– Dynamisches SQL
„
Call-Level-Interface
Vertiefung in DBS2:
– Java-Unterstützung: JDBC, SQLJ
– Stored Procedures
– Web-Einbindung von Datenbanken (Servlets, JSP, Skriptsprachen, …)
WS0910, © Prof. Dr. E. Rahm
DBS1
8-1
Kopplung mit einer Wirtssprache
Einbettungg von SQL
Q
In Wirtssprache
(Embedded SQL)
statisches SQL
„
Call Schnittstelle
Call-Schnittstelle
Call Level Interface (CLI)
dynamisches SQL
dynamisches SQL
Spracherweiterungen
p
g
(bzw. neue Sprachenentwicklungen)
- persistente Programmiersprachen
- Datenbankprogrammiersprachen
Einbettung von SQL (Embedded SQL)
– Spracherweiterung um spezielle DB-Befehle (EXEC SQL ... )
– Vorübersetzer (Prä-Compiler) wandelt DB-Aufrufe in Prozeduraufrufe um
„
Call-Schnittstelle (CLI)
(
)
– DB-Funktionen werden durch Bibliothek von Prozeduren realisiert
– Anwendung enthält lediglich Prozeduraufrufe
– weniger
i
komfortable
k f t bl Programmierung
P
i
als
l mit
it Embedded
E b dd d SQL
„
Statisches SQL: Anweisungen müssen zur Übersetzungszeit feststehen
– Optimierung zur Übersetzungszeit ermöglicht hohe Effizienz (Performance)
„
Dynamisches SQL: Konstruktion von SQL-Anweisungen zur Laufzeit
WS0910, © Prof. Dr. E. Rahm
8-2
DBS1
Statisches SQL: Beispiel für C
exec sql
q include sqlca;
q
/* SQL Communication Area */
main ()
{
exec sql begin declare section;
char X[8];
int
GSum;
exec sql end declare section;
exec sql connect to dbname;
exec sql
l i
insert
t i
into
t PERS(PNR
PERS(PNR,PNAME,GEHALT)
PNAME GEHALT) values
l
(4711
(4711,’Ernie’,
’E i ’ 32000)
32000);
exec sql insert into PERS(PNR,PNAME,GEHALT) values (4712,’Bert’, 38000);
printf("ANR ? "); scanf(" %s", X);
exec sql select sum (GEHALT) into :GSum from PERS where ANR = :X;
printf("Gehaltssumme: %d\n", GSum)
exec sql commit work;
exec sql disconnect;
}
„
eingebettete SQL-Anweisungen werden durch "EXEC SQL" eingeleitet und spezielles
Symbol (hier ";") beendet, um Compiler Unterscheidung von anderen Anweisungen zu
g
ermöglichen
„ Verwendung von AP-Variablen in SQL-Anweisungen verlangt Deklaration innerhalb
eines "declare section"-Blocks sowie Angabe des Präfix ":" innerhalb von SQLAnweisungen
„ Werteabbildung
W t bbild
mit
it T
Typanpassung ddurch
h INTO
INTO-Klausel
Kl
l bei
b i SELECT
„ Kommunikationsbereich SQLCA (Rückgabe von Statusanzeigern u. ä.)
WS0910, © Prof. Dr. E. Rahm
DBS1
8-3
Verarbeitung von ESQL-Programmen
Anwendungsprogramm
mit SQL-Anweisungen
Listing,
Fehler, ...
ESQL-Precompiler
modifiziertes
Anwendungsprogramm
DBKatalog
Zugriffsmodule
(Access Modules)
Compiler &
Binder
DB
ausführbares
Programm
Eingabe, Ausgabe
Z iff Aufruf
Zugriff,
A f f
WS0910, © Prof. Dr. E. Rahm
8-4
DBS1
Mengenorientierte Anfragen
„
Queries mit nur einem Ergebnissatz
– einfache Übernahme der Ergebnise in Programmvariable (SELECT attr INTO :var)
„
Kernproblem
K
bl bei
b i SQL-Einbettung
SQL Ei b tt
in Programmiersprachen:
g von Tupelmengen
p
g auf
Abbildung
Variablen der Programmiersprache
Satzorientierte
Programmiersprache
3. Tupel
sequentiell
verarbeiten
– Nutzung von Cursor/Iteratoren
b
bzw.
Result-Sets
R lt S t zur satzweisen
t
i
Bereitstellung und Abarbeitung von
DBS-Ergebnismengen
4. Cursor/Iterator
/
schließen
1. Anfrage
mengenorientiertes
DBMS
WS0910, © Prof. Dr. E. Rahm
2. Anfrage ausführen;
Ergebnisse in Cursor /
R
ResultSet bereitstellen
8-5
DBS1
Cursor-Konzept in Embedded SQL
„
Cursor ist ein Iterator, der einer Anfrage (Relation) zugeordnet wird und mit
dessen Hilfe die Tupeln des Ergebnismenge einzeln (one tuple at a time) im
Programm bereitgestellt werden
– Trennung von Query-Spezifikation (Cursor-Deklaration) und
Bereitstellung/Verarbeitung von Tupeln im Query-Ergebnis
„
Operationen auf einen Cursor C1
–
–
–
–
„
DECLARE C1 CURSOR FOR table-exp
OPEN C1
FETCH C1 INTO VAR1, VAR2, . . ., VARn
CLOSE C1
Anbindung einer SQL-Anweisung an die Wirtssprachen-Umgebung
– Übergabe der Werte eines Tupels mit Hilfe der INTO-Klausel bei FETCH
=> INTO target-commalist
li (Variablenliste
(V i bl li d.
d Wirtsprogramms)
Wi
)
– Anpassung der Datentypen (Konversion)
„
kein Cursor erforderlich für Select
Select-Anweisungen
Anweisungen, die nur einen Ergebnissatz
liefern (SELECT INTO)
WS0910, © Prof. Dr. E. Rahm
8-6
DBS1
Cursor-Konzept (3)
„
Beispielprogramm in C (vereinfacht)
...
exec sql begin declare section;
char X[50];
char Y[8];
double G;
exec sql end declare section;
exec sql declare c1 cursor for
select NAME, GEHALT from PERS where ANR = :Y;
printf("ANR ? "); scanf(" %s", Y);
exec sql open C1;
while (sqlcode == ok) {
exec sql fetch C1 into :X, :G;
printf("%s\n",
printf(
%s\n , X)}
exec sql close C1;
...
„
DECLARE C1 . . . ordnet der Anfrage einen Cursor C1 zu
„ OPEN C1 bindet die Werte der Eingabevariablen
„ Systemvariable SQLCODE zur Übergabe von Fehlermeldungen (Teil von
SQLCA)
WS0910, © Prof. Dr. E. Rahm
DBS1
8-7
Scroll-Cursor
DECLARE cursor [SCROLL] CURSOR FOR table-exp
[ORDER BY order-item-commalist]
[FOR {
[
{READ ONLY | UPDATE [
[OF column-commalist]}]
]}]
„
Erweiterte Positionierungsmöglichkeiten durch SCROLL
– Cursor
Cursor-Definition
Definition (Bsp.):
EXEC SQL DECLARE C2 SCROLL CURSOR FOR
SELECT NAME, GEHALT FROM PERS
ORDER BY GEHALT ASCENDING
„
Erweitertes FETCH-Statement:
EXEC SQL FETCH [[<fetch orientation>] FROM <cursor>
Fetch orientation:
„
INTO
<target list>
NEXT, PRIOR, FIRST, LAST,
ABSOLUTE <expression>, RELATIVE <expression>
Beispiele:
WS0910, © Prof. Dr. E. Rahm
8-8
DBS1
DB-Aktualisierung über Cursor
„
Wenn die Tupeln, die ein Cursor verwaltet (active set), eindeutig Tupeln
einer Relation entsprechen, können sie über Bezugnahme durch den Cursor
geändert werden
positioned-update ::= UPDATE table SET update-assignment-commalist
WHERE CURRENT OF cursor
positioned-delete ::= DELETE
„
FROM
table WHERE CURRENT OF cursor
Beispiel:
while (sqlcode == ok) {
exec sql fetch C1 into :X, :G;
/* Berechne das neue Gehalt in Z */
/
/
exec sql update
PERS
set GEHALT = :Z
where current of C1;
}
„
keine Bezugnahme bei INSERT möglich!
WS0910, © Prof. Dr. E. Rahm
8-9
DBS1
Verwaltung von Verbindungen
„
Zugriff auf DB erfordert i.a. zunächst, eine Verbindung herzustellen, v.a. in
Client/Server-Umgebungen
–
–
–
–
Aufbau der Verbindung mit CONNECT,
CONNECT Abbau mit DISCONNECT
jeder Verbindung ist eine Session zugeordnet
Anwendung kann Verbindungen (Sessions) zu mehreren Datenbanken offenhalten
Umschalten der "aktiven" Verbindung durch SET CONNECTION
CONNECT TO
target [AS connect-name] [USER user-name]
SET CONNECTION
DISCONNECT
WS0910, © Prof. Dr. E. Rahm
{ connect-name | DEFAULT }
{ CURRENT | connect-name | ALL }
8-10
DBS1
Beispiel: Stücklistenauflösung
„
Darstellungsmöglichkeit im RM:
TEIL (TNR, BEZ, MAT, BESTAND)
STRUKTUR (OTNR,
(OTNR UTNR,
UTNR ANZAHL)
Goes Into-Graph
TEIL
A
1
5
8
B
2
G
„
4
C
4
D
2
E
TNR
A
B
C
D
E
F
G
STRUKTUR
BEZ
Getriebe
Gehäuse
Welle
Schraube
Kugellager
Scheibe
Schraube
MAT
Alu
Stahl
Stahl
Stahl
Blei
Chrom
BESTAND
10
0
100
200
50
0
100
OTNR UTNR
A
A
A
B
B
C
C
B
C
D
D
G
D
E
ANZAHL
1
5
8
4
2
4
2
Aufgabe: Ausgabe aller Endprodukte sowie deren Komponenten
WS0910, © Prof. Dr. E. Rahm
8-11
DBS1
Beispiel: Stücklistenauflösung (2)
„
max. Schachtelungstiefe sei bekannt (hier: 2)
exec sql begin declare section; char T0[10], T1[10], T2[10]; int ANZ;
q end declare section;
;
exec sql
exec sql declare C0 cursor for select distinct OTNR from STRUKTUR S1
where not exists (select * from STRUKTUR S2 where S2.UTNR = S1.OTNR);
exec sql declare C1 cursor for
select UTNR,
UTNR ANZAHL from STRUKTUR where OTNR = :T0;
exec sql declare C2 cursor for
select UTNR, ANZAHL from STRUKTUR where OTNR = :T1;
exec sql open C0;
while (1) {
exec sql fetch C0 into :T0;
if (sqlcode == notfound) break;
printf ("%s\n", T0);
exec sql open C1;
while
hil (2) { exec sql
l fetch
f t h C1 into
i t :T1,
T1 :ANZ;
ANZ
if (sqlcode == notfound) break;
printf (" %s: %d\n",T1, ANZ);
exec sql open C2;
while (
(3)
) {
exec sql
q fetch C2 INTO :T2,
, :ANZ;
if (sqlcode == notfound) break;
printf ("
%s: %d\n",T2, ANZ); }
exec sql close C2; }
exec sql close C1; } /* END WHILE */
exec sql close C0;
WS0910, © Prof. Dr. E. Rahm
8-12
DBS1
Dynamisches SQL
„
dynamisches SQL: Festlegung von SQL-Anweisungen zur Laufzeit
-> Query-Optimierung i.a. erst zur Laufzeit möglich
„ SQL-Anweisungen
SQL A
i
werden
d vom C
Compiler
il wie
i Zeichenketten
Z i h k tt behandelt
b h d lt
– Dekaration DECLARE STATEMENT
– Anweisungen
g enthalten SQL-Parameter
Q
((?)) statt Programmvariablen
g
„
2 Varianten: Prepare-and-Execute bzw. Execute Immediate
exec sql begin declare section;
char Anweisung[256],
Anweisung[256] X[6];
exec sql end declare section;
exec sql declare SQLanw statement;
Anweisung = "DELETE FROM PERS WHERE ANR = ? AND ORT = ?"; /*bzw. Einlesen
exec sql prepare SQLanw from :Anweisung;
exec sql execute SQLanw using
scanf(" %s", X);
exec sql
q execute SQLanw using
g
WS0910, © Prof. Dr. E. Rahm
8-13
DBS1
Dynamisches SQL (2)
„
Variante ohne Vorbereitung: EXECUTE IMMEDIATE
„ Beispiel
scanf(" %s", Anweisung);
exec sql execute immediate :Anweisung;
„
Maximale Flexibilität, jedoch potentiell geringe Performance
– kann für einmalige Query-Ausführung ausreichen
WS0910, © Prof. Dr. E. Rahm
8-14
DBS1
Call-Level-Interface
„
alternative Möglichkeit zum Aufruf von SQL-Befehlen innerhalb von
Anwendungsprogrammen: direkte Aufrufe von Prozeduren/Funktionen einer
standardisierten Bibliothek (API)
„ Hauptvorteil: keine Präkompilierung von Anwendungen
– Anwendungen
g mit SQL-Aufrufen
Q
brauchen nicht im Source-Code bereitgestellt
g
zu
werden
– wichtig zur Realisierung von kommerzieller Anwendungs-Software bzw. Tools
„
Ei t v. a. in
Einsatz
i Client/Server-Umgebungen
Cli t/S
U
b
Anwendung
call
return
CLI Call Handler
Client
(driver manager)
dynamic SQL
return
DBS-Server
WS0910, © Prof. Dr. E. Rahm
Server
8-15
DBS1
Call-Level-Interface (2)
„
Unterschiede in der SQL-Programmierung zu eingebettetem SQL
–
–
–
–
„
CLI impliziert i.a. dynamisches SQL (Optimierung zur Laufzeit)
komplexere Programmierung
explizite Anweisungen zur Datenabbildung zwischen DBS und Programmvariablen
einheitliche Behandlung von mengenwertigen und einfachen Selects (<-> CursorBehandlung bei ESQL)
SQL-Standardisierung
SQL
Standardisierung des CLI (Teil von SQL99)
– starke Anlehnung an ODBC
– über 40 Routinen:
- Verbindungskontrolle, Ressourcen-Allokation
- Ausführung von SQL-Befehlen
- Zugriff auf Diagnoseinformation, Transaktionsklammerung
„
JDBC: neuere Variante
WS0910, © Prof. Dr. E. Rahm
8-16
DBS1
Zusammenfassung
„
Cursor-Konzept zur satzweisen Verarbeitung von Datenmengen
– Operationen: DECLARE CURSOR, OPEN, FETCH, CLOSE
– Erweiterungen: Scroll
Scroll-Cursor,
Cursor Sichtbarkeit von Änderungen
„
Statisches (eingebettetes) SQL
– hohe Effizienz, relativ einfache Programmierung
– begrenzte Flexibilität (Aufbau aller SQL-Befehle muß zur
Übersetzungszeit festliegen, es können nicht zur Laufzeit verschiedene
Datenbanken angesprochen werden)
„
Call-Level-Interface (z.B. JDBC)
– keine Nutzung eines Präcompilers
– Einsatz v.a. in Client-Server-Systemen
– breite Unterstützung
WS0910, © Prof. Dr. E. Rahm
8-17
DBS1
Vorschau SS2010
„
Datenbanksysteme 2
– Anwendungsprogrammierung
gp g
g ((stored pprocedures,, JDBC),
),
Web-Anbindung von DB
– Objektorientierte DBS, O/R
O/R-Mapping
Mapping (Hibernate)
– Objektrelationale DBS (SQL99, SQL2003)
– XML-Datenbanken
XML Datenbanken (XML
(XML-Schema,
Schema XQuery)
„
Relationales Datenbankpraktikum (IBM DB2)
–
–
–
Entwurf einer Datenbank für gegebene Aufgabenstellung
Einrichtung der DB; initiales Laden mit Testdaten
Realisierung von Anwendungsfunktionen (in Java)
WS0910, © Prof. Dr. E. Rahm
8-18
DBS1