n n n n n n VLDatenbankenI–10–1 n n n u u VLDatenbankenI–10–2 VLDatenbankenI–10–3

(1)

10. Anwendungsprogrammierung

■ Client-Server-Architektur

■ Anbindung von Programmiersprachen

■ Call-Level-Schnittstellen: SQL/CLI, JDBC

■ Einbettung: Embedded SQL, SQLJ

■ Gespeicherte Prozeduren

■ Prozedurale Erweiterungen: SQL/PSM, PL/SQL

VL Datenbanken I – 10–1

Client-Server-Architektur

■ typische Architektur von DB-Anwendungen, da zentrale Verwaltung durch DBMS notwendig

■ Prinzip: Client nimmt Dienste eines Servers in Anspruch

■ erfordert

◆ Kenntnis über angebotene Dienste

◆ Protokoll zur Regelung der Interaktion

Client-Server-Modell

(Dienstnehmer)

Client Server

(Diensterbringer) 1.Anforderung

2.Bearbeitung

3.Antwort

(2)

Funktionsgruppen in DB-Anwendungen

■ Präsentation und Benutzerinteraktion

■ Anwendungslogik

■ Datenmanagementfunktionalität

2-Schichten-Architektur

Benutzerschnittstelle Anwendungslogik DB-Schnittstelle

Client

DB-Server DBMS-Protokoll

3-Schichten-Architektur

Client

DB-Server DBMS-Protokoll DB-Schnittstelle Anwendungslogik Benutzerschnittstelle

CORBA, RMI, ...

Applikations- server

(3)

Programmiersprachenanbindung

Kopplungsarten:

■ prozedurale oder CALL-Schnittstellen (call level interface)

■ Einbettung einer DB-Sprache in Programmiersprachen

◆ statische Einbettung: Vorübersetzer-Prinzip

;SQL-Anweisungen zur Übersetzungszeit festgelegt

◆ dynamische Einbettung:

;Konstruktion von SQL-Anweisungen zur Laufzeit

■ Spracherweiterungen und neue Sprachentwicklungen

Cursor-Konzept

SQL PASCAL

Cursor Relation

Datenbank Anwendungsprogramm

Cursor in SQL

Cursor-Deklaration:

declare AktBuch cursor for select ISBN, Titel, Verlagsname from Bücher

where Verlagsname = ’Thomson’;

Cursor-Deklaration mit Änderungsmöglichkeit:

declare AktBuch cursor for select ISBN, Titel, Verlagsname from Bücher

for update of ISBN, Titel;

(4)

Cursor in SQL2

declare CursorName

[insensitive] [scroll] cursor for ...

■ next: Gehe weiter zum nächsten Tupel

■ prior: Gehe zum vorherigen Tupel

■ first bzw. last: Gehe zum ersten bzw. letzten Tupel

■ absolutenfrom: Gehe zumn-ten Tupel des Cursors (negative Werte werden relativ zum letzten Tupel rückwärts gewertet — absolute -1 ist also äquivalent zu last)

■ relativenfrom: Gehe zumn-ten Tupel relativ zur aktuellen Cursor-Position

SQL/CLI: Der Standard

■ Call-Level-Interface: Bibliothek von Prozeduren/Funktionen zur

◆ Kommunikation mit dem DBMS

◆ Definition und Ausführung von Anfragen

◆ Verarbeitung von Ergebnissen

■ SQL/CLI: ISO-Standard für API

■ Programmiersprachen: C/C++, Ada, Fortran, Pascal, . . .

CLI: Handles

■ Handle: Verweis auf globale Datenstruktur zur Kommunikation mit DBMS

■ C-Datentyp:SQLHANDLE

■ erforden explizite Allokation (SQLAllocHandle)

■ Arten:

◆ Environment Handles: Verwaltung des globalen Zustandes der Applikation

◆ Connection Handles: Verwaltung von Verbindungsdaten

◆ Statement Handles: Informationen zu einer SQL-Anweisung

◆ Description Handles: Daten zu Ergebnisspalten bzw.

Paramtern

(5)

CLI: Aufbau einer DB-Verbindung

1. Handles allokieren

SQLHANDLE henv; /* Environment Handle */

SQLHANDLE hdbc; /* Connection Handle */

SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

if (SQLAllocHandle (SQL_HANDLE_DBC, henv,

&hdbc) != SQL_SUCCESS) /* Fehlerbehandlung */

2. Verbindung herstellen

SQLCHAR *server = "shop_db", *uid = "scott",

*pwd = "tiger";

SQLConnect (hdbc, server, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS);

CLI: Anfrageausführung

SQLHANDLE hstmt;

SQLCHAR *select =

"select KNr, Name, Ort from Kunde";

SQLAllocHandle (SQL_HANDLE_STMT, hdbc,

&hstmt);

SQLExecDirect (hstmt, select, SQL_NTS);

CLI: Ergebnisverarbeitung

1. Bindung an Variablen herstellen SQLINTEGER knr;

SQLCHAR kname[31], kort[51];

SQLBindCol (hstmt, 1, SQL_C_LONG, (SQLPOINTER) &knr, 0, NULL);

SQLBindCol (hstmt, 2, SQL_C_CHAR, kname, 31, NULL);

SQLBindCol (hstmt, 3, SQL_C_CHAR, kort, 51, NULL);

2. Ergebnisse auslesen

while (SQLFetch (hstmt) == SQL_SUCCESS) printf ("Kunde = %ld, %s, %s\n",

knr, kname, kort);

(6)

CLI: Transaktionssteuerung

■ Transaktion erfolgreich beenden

SQLEndTrans (SQL_HANDLE_DBC, hdbc, SQL_COMMIT);

■ Transaktion abbrechen

SQLEndTrans (SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);

ODBC

■ SQL/CLI-konforme Implementierung für Microsoft Windows

■ Zugriff auf verschiedene Datenbanksysteme über systemspezifische Treiber möglich

■ dynamisches Laden von Treibern (implementiert als DLL)

■ Registrierung von Datenquellen mit Name, System, Treiber, Verbindungsinformation

JDBC: Überblick

■ Datenbankzugriffsschnittstelle für Java

■ abstrakte, datenbankneutrale Schnittstelle

■ vergleichbar mit ODBC

■ Low-Level-API: direkte Nutzung von SQL

■ Java-Packagejava.sql

◆ DriverManager: Einstiegspunkt, Laden von Treibern

◆ Connection: Datenbankverbindung

◆ Statement: Ausführung von Anweisungen über eine Verbindung

◆ ResultSet: verwaltet Ergebnisse einer Anfrage, Zugriff auf einzelne Spalten

(7)

JDBC: Ablauf

1. Aufbau einer Verbindung zur Datenbank

■ Angabe der Verbindungsinformationen

■ Auswahl und Laden des Treibers 2. Senden einer SQL-Anweisung

■ Definition der Anweisung

■ Belegung von Parametern 3. Verarbeiten der Anfrageergebnisse

■ Navigation über Ergebnisrelation

■ Zugriff auf Spalten

JDBC: Verbindungsaufbau

1. Treiber laden

Class.forName ("com.company.DBDriver");

2. Verbindung herstellen Connection con;

String url = "jdbc:subprotocol:datasource";

con = DriverManager.getConnection (url, "scott", "tiger");

JDBC-URL spezifiziert

■ Datenquelle/Datenbank

■ Verbindungsmechanismus (Protokoll, Server-Host und Port)

JDBC: Anfrageausführung

1. Anweisungsobjekt (Statement) erzeugen

Statement stmt = con.createStatement ();

2. Anweisung ausführen String query =

"SELECT titel, preis FROM buch";

ResultSet rset = stmt.executeQuery (query);

Klassejava.sql.Statement

■ Ausführung von Anfragen (SELECT) mitexecuteQuery

■ Ausführung von Änderungsanweisungen (DELETE, INSERT, UPDATE) mitexecuteUpdate

(8)

JDBC: Ergebnisverarbeitung

1. Navigation über Ergebnismenge (Cursor-Prinzip) while (rset.next ()) {

// Verarbeitung der einzelnen Tupel ...

}

2. Zugriff auf Spaltenwerte übergetType-Methoden

■ über Spaltenindex

String titel = rset.getString (1);

■ über Spaltenname

String titel = rset.getString ("titel");

JDBC: Fehlerbehandlung

■ Fehlerbehandlung mittels Exception-Mechanismus

■ SQLException für alle SQL- und DBMS-Fehler try {

// Aufruf von JDBC-Methoden ...

} catch (SQLException exc) {

System.out.println (SSQLException: "+

exc.getMessage ());

}

JDBC: Änderungsoperationen

■ DDL- und DML-Operationen mittelsexecuteUpdate

■ liefert Anzahl der betroffenen Zeilen (für DML-Operationen)

Statement stmt = con.createStatement ();

int rows = stmt.executeUpdate (

" UPDATE buch SET bestand = bestand-1"+

" WHERE isbn = ’12345’ ");

(9)

JDBC: Transaktionssteuerung

■ Methoden von Connection

◆ commit ()

◆ rollback () Auto-Commit-Modus

◆ implizites Commit nach jeder Anweisung

◆ Transaktion besteht nur aus einer Anweisung

◆ Umschalten mittelssetAutoCommit (boolean)

Statische Einbettung: Embedded SQL

exec sql declare AktBuch cursor for select ISBN, Titel, Verlagsname from Bücher

for update of ISBN, Titel;

Öffnen und Schließen einer Datenbank exec sql connect UniBeispiel;

Deklarationen

■ Deklaration benutzter Datenbankrelationen exec sql declare Buch table

( ISBN char(10) not null, Titel char(120) not null,

Verlagsname char(30) not null);

■ Deklaration gemeinsamer Variablen

exec sql begin declare section;

BuchISBN char(10);

NeuerPreis real;

exec sql end declare section;

(10)

Datentransfer

exec sql update Buch_Versionen set Preis = :NeuerPreis where ISBN = :BuchISDN ; exec sql insert into Buch_Versionen

values (:NeuISBN, :NeuAuflage, 1995, :Seiten, :Preis);

exec sql select ISBN, Auflage, Jahr, Preis into :ISBN, :Auflage, :Jahr, :Preis from Buch_Versionen

where ISBN = :SuchISBN and Auflage = 1;

Datentransfer (II)

Indikator-Variablen zum Test auf null-Werte:

exec sql select :ISBN, Auflage, Jahr, Preis into :ISBN, :Auflage, :Jahr,

:Preis:PreisInd from Buch_Versionen

where ISBN = :SuchISBN and Auflage = :SuchAuflage;

Einsatz der Cursor-Technik

exec sql open AktBuch;

exec sql fetch AktBuch

into :ISBN, :Titel, :Verlagsname;

exec sql close AktBuch;

exec sql delete from Bücher

where current of AktBuch;

(11)

Fehler- und Ausnahmebehandlung

SQL Communication Area

exec sql include sqlca;

‘whenever’-Anweisung

exec sql whenever <Bedingung> <Aktion>;

■ not found: Kein Tupel wurde gefunden, definiert etwa als sqlcode= 100.

■ sqlwarning: Warnung, entspricht etwa sqlcode>0∧sqlcode6= 100.

■ sqlerror: Fehler, also sqlcode<0.

Transaktionssteuerung

exec sql commit work;

exec sql rollback work;

Dynamische Einbettung: Dynamic SQL

exec sql begin declare section;

AnfrageString char(256) varying;

exec sql end declare section;

exec sql declare AnfrageObjekt statement;

AnfrageString :=

’DELETE FROM Vorlesungen WHERE SWS < 2’;

...

exec sql prepare AnfrageObjekt from :AnfrageString;

exec sql execute AnfrageObjekt;

„Anfragen als Zeichenketten“

(12)

Dynamische Einbettung: Dynamic SQL

...

AnfrageString :=

’DELETE FROM Buch_Versionen ’ +

’WHERE ISBN = ? AND Auflage = ?’ ; exec sql prepare AnfrageObjekt

from :AnfrageString;

exec sql execute AnfrageObjekt

using :LöschISBN, :LöschAuflage;

Wertübergabe an Anfragen

SQLJ: Embedded SQL für Java

■ Einbettung von SQL-Anweisungen in Java-Quelltext

■ Vorübersetzung des erweiterten Quelltextes in echten Java-Code durch Translator sqlj

■ Überprüfung der SQL-Anweisungen

◆ korrekte Syntax

◆ Übereinstimmung der Anweisungen mit DB-Schema

◆ Typkompatibilität der für Datenaustausch genutzten Variablen

■ Nutzung von JDBC-Treibern

SQLJ-Anweisungen

■ Kennzeichnung durch#sqlDeklarationen

■ Klassendefinitionen für Iteratoren

■ SQL-Anweisungen: Anfragen, DML- und DDL-Anweisungen

#sql { SQL-Operation };

■ Beispiel:

#sql { INSERT INTO buch VALUES ("Datenbanken", 59.00, "123456",

"MITP") };

(13)

Host-Variablen

■ Variablen einer Host-Sprache (hier Java), die in SQL-Anweisungen auftreten können

■ Verwendung: Austausch von Daten zwischen Host-Sprache und SQL

■ Kennzeichnung durch":variable"

■ Beispiel:

String titel, isbn = "123456";

#sql { SELECT titel INTO :titel FROM buch WHERE isbn = :isbn };

Iteratoren

Implementierung des Cursor-Konzeptes 1. Deklaration des Iterators

#sql public iterator BookIter (String titel, double preis);

2. Definition des Iteratorobjektes BookIter iter;

3. Ausführung der Anweisung

#sql iter = { SELECT titel, preis FROM buch };

4. Navigation

while (iter.next ()) {

System.out.println (iter.titel () +

" "+ iter.preis ());

}

Gespeicherte Prozeduren

■ Probleme von CLI und Embedded SQL:

◆ ständiger Wechsel der Ausführungskontrolle zwischen Anwendung und DBS

◆ keine anweisungsübergreifende Optimierung notwendig

■ Ausweg: gespeicherte Prozeduren

◆ im Datenbank-Server verwaltete und auch dort ausgeführte Software-Module in Form von Prozeduren bzw. Funktionen

◆ Aufruf aus Anwendungen und Anfragen heraus

(14)

Vorteile Gespeicherter Prozeduren

■ Strukturierungsmittel für größere Anwendungen

■ Prozeduren nur vom DBMS abhängig und nicht von externen Programmiersprachen oder

Betriebssystemumgebungen

■ Optimierung der Prozeduren

■ Ausführung der Prozeduren unter Kontrolle des DBMS

■ zentrale Kontrolle der Prozeduren: redundanzfreie Darstellung relevanter Aspekte der

Anwendungsfunktionalität

■ Rechtevergabe für Prozeduren

■ in der Integritätssicherung: Aktionsteil von Triggern

SQL/PSM: Der Standard

■ SQL-Standard für prozedurale Erweiterungen

■ PSM: Persistent Stored Modules

◆ gespeicherte Module aus Prozeduren und Funktionen

◆ Einzelroutinen

◆ Einbindung externer Routinen (implementiert in C, Java, . . . )

◆ syntaktische Konstrukte für Schleifen, Bedingungen etc.

SQL/PSM: Funktionen

■ Funktionsdefinition

create function dm_in_euro (float v) returns float

begin

return (v / 1.95583);

end;

■ Aufruf innerhalb einer Anfrage

select Bezeichnung, dm_in_euro (Preis) from Produkt

where dm_in_euro (Preis) < 100

■ Nutzung außerhalb von Anfragen

set euro_preis = dm_in_euro (79.00);

(15)

SQL/PSM: Prozeduren

■ Prozedurdefinition

create procedure rechnungsbetrag ( in int nr, out float summe) begin

select sum(p.Preis ∗ bp.Anzahl) into summe

from Bestellposten bp, Produkt p where bp.BestNr = nr and

bp.ProdId = p.ProdId;

end;

■ Nutzung über call-Anweisung declare preis float;

call rechnungsbetrag (42, preis);

SQL/PSM: Zugriffscharakteristik

■ Eigenschaften von Prozeduren, die Anfrageausführung und -optimierung beeinflussen

◆ deterministic: Routine liefert für gleiche Parameter gleiche Ergebnisse

◆ no sql: Routine enthält keine SQL-Anweisungen

◆ contains sql:Routine enthält SQL-Anweisungen (Standard für SQL-Routinen)

◆ reads sql data: Routine führt SQL-Anfragen (select-Anweisungen) aus

◆ modifies sql data: Routine, die DML-Anweisungen (insert, update, delete) enthält

SQL/PSM: Ablaufkontrolle

■ Variablendeklaration

declare name varchar(50);

■ Zuweisung

set var = 42;

■ Bedingte Verzweigungen

if <Bedingung> then <Anweisungen>

[ else <Anweisungen> ] end if;

■ Schleifen

loop <Anweisungen> end loop while <Bedingung> do

<Anweisungen> end while repeat <Anweisungen>

until <Bedingung> end repeat

(16)

SQL/PSM: Ausnahmebehandlung

■ Auslösen einer Ausnahme (Condition) signal <ConditionName>;

■ Deklarieren von Ausnahmen declare yyy condition;

■ Ausnahmebehandlung begin

declare exit handler for

begin

<Ausnahmebehandlung>;

end

<Anweisungen>;

end

PL/SQL von Oracle

■ prozedurale SQL-Erweiterung für Oracle

■ Merkmale

◆ erweitertes Typsystem

◆ Package-Konzept Deklarationen

declare

Heute date;

type PersonRecordType is record ( PersonName varchar (50),

GebDatum date);

Mitarbeiter PersonRecordType;

PL/SQL: Cursor

cursor AktBuch is

select ISBN, Titel, Verlagsname from Bücher;

Zugriff auf Typinformation

AktPersName Mitarbeiter.PersonName%type;

BuchTupel AktBuch%rowtype;

(17)

Operationale Konstrukte

if <Bedingung> then

[ else

<PL/SQL-Anweisungen> ] end if;

for <IndexVariable> in <EndlicherBereich>

loop

<PL/SQL-Anweisungen>;

end loop;

while <Bedingung>

loop

<PL/SQL-Anweisungen>;

end loop;

Iteration über Tabellen

for BuchRec in AktBuch loop ... end loop;

entspricht declare

BuchRec AktBuch%rowtype;

begin loop

fetch AktBuch into BuchRec;

exit when AktBuch%notfound;

...

end loop;

Fehlerbehandlung

when Ausnahme then ProgrammStück;

(18)

PL/SQL: Gespeicherte Prozeduren

create function FunktionsName

( Param1 ParamTyp1, ..., ParamN ParamTypN ) return ErgebnisTyp

is

/* PL/SQL - Block mit return-Anweisung */

create procedure ProzedurName ( Param1 in ParamTyp1, ( Param2 out ParamTyp2, ( Param3 in out ParamTyp3,

... ) is

/* PL/SQL - Block mit Zuweisungen an out-Parameter */

Weitere Prozedurale SQL-Erweiterungen

■ IBM DB2: SQL/PSM

■ Informix: SPL

■ Sybase, Microsoft SQL Server: Transact-SQL

■ externe Routinen: implementiert in C, Java, . . .