IS: Datenbanken, © Till Hänisch 2000
Programmierung von Datenbank Anwendungen
ESQL, ODBC, JDBC und co
IS: Datenbanken, © Till Hänisch 2000
Methoden
bisher interaktive Verwendung von SQL Terminal Skripte
Ausführen von SQL aus Programmiersprache heraus proprietäre APIs standardisierte Schnittstellen
statisch (embedded SQL) dynamisch (ODBC, JDBC)
IS: Datenbanken, © Till Hänisch 2000
Embedded SQL Einbettung von SQL-Statements in Wirts-Sprache
C,COBOL, PL/1, FORTRAN, PASCAL,... Vor-Übesetzung des Programms in Wirts-Sprache (precompile)
i.w. gleiche Syntax wie bei interaktivem SQL, zusätzlich Konstrukte für
Einbettung der SQL-Befehle Fehlerbehandlung Übergabe von Variableninhalten Übergabe von Query-Ergebnissen
Einfache, sprachunabhängige Syntax für Precompiler
EXEC SQL Präfixc für SQL-Kommandos ":" als Kennzeichner für Variablen
IS: Datenbanken, © Till Hänisch 2000
Vorgehenexample.pc
example.c
example.o
example[.exe]
Precompiler für C
C Compiler
Linker
C Source mit eingebettetem SQL
C Source, SQl durch DBMS-spezifischeFunktionsaufrufe ersetzt
Object Code
ausführbares Programm
DBMS-Library
IS: Datenbanken, © Till Hänisch 2000
Tupelvariablen
SQL liefert Tupelmenge, Darstellung in C, PASCAL,..."Impedence mismatch"Typkonzept des RDBMS und der Wirtssprache passen nicht zusammen
Lösung: CursorIterator, Tupel-Zeiger für satzweise VerarbeitungEXEC SQL DECLARE name CURSOR FOR select statm.
Operationen:OPEN führt Abfrage ausCLOSEFETCH name INTO :var1, :var2,...
überträgt Werte der Attribute des aktuellen Datensatzes inVariablen und setzt Zeiger eins weiter
IS: Datenbanken, © Till Hänisch 2000
prinzipieller AufbauEXEC SQL BEGIN DECLARE SECTION;Deklaration der ÜbergabevariablenEXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT :userid IDENTIFIED BY :password
EXEC SQL DECLARE c CURSOR FOR SELECT * FROM EMP;
EXEC SQL OPEN c;
for(;;) {EXEC SQL FETCH ...
}
EXEC SQL CLOSE c;EXEC SQL DISCONNECT;
IS: Datenbanken, © Till Hänisch 2000
Anmerkungen statisches SQL
wird im Programm fest definiert und kann vom Precompiler ausgewertet werden
SQL muß vorher bekannt sein ! Wie ist isql implementiert ? dynamisches SQL (in ESQL nicht möglich)
tupelweise Verarbeitung u.U. nicht effizient
ein Funktionsaufruf pro Tupel - > Array Fetch,... ESQL ist standardisiert
wie SQL selbst passende Umgebung muß zum Programm gelinkt werden Geht nicht, wenn Auswahl des DB-Systems erst zur Laufzeit erfolgen soll ! -> ODBC
IS: Datenbanken, © Till Hänisch 2000
Native API, Beispiel OCI Oracle Call Interface (CLI) kompliziert, mächtig, Oracle spezifisch bestimmter Funktionen nur mit OCI
mehrere Transaktionen BLOBs
static char cmd[] = "INSERT INTO MESSAGE(SEVERITY,CODE) VALUES (:Severity,:Code)";
if (!olog(&lda, hda, (unsigned char *)pszUserid, -1, (unsigned char *)pszPassword, -1, (unsigned char *)pszNetAlias, -1, (ub4)OCI_LM_DEF))
if (!oopen(&cda, &lda, (text *) 0, -1, -1, (text *) 0, -1))
if (!oparse(&cda,(unsigned char *) cmd,-1,0,2))ProcessMessage(&msg); /* normaler C-Code */if ((!obndrv(&cda,(unsigned char *)":Severity",-1,(unsigned char *) Severity,
strlen(Severity), VARCHAR2_TYPE,-1,0,0,-1,-1))|| (obndrv(&cda,(unsigned char *)":Code",-1,(unsigned char *)Code, strlen(Code),VARCHAR2_TYPE,-1,0,0,-1,-1)))
if (!oexec(&cda))
IS: Datenbanken, © Till Hänisch 2000
Embedded SQL SQL wird in Standard C (COBOL,...) eingebettet Quellcode datenbankunabhängig Precompiler, der OCI erzeugt ausführbares Programm ist datenbankabhängig
EXEC SQL BEGIN DECLARE SECTION; VARCHAR pszUserid[20];
VARCHAR pszPassword[20]; VARCHAR Severity[5];VARCHAR Code[10];
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
ProcessMessage(&msg); /* normaler C-Code */
EXEC SQL INSERT INTO MESSAGE(Severity, Code)VALUES (:Severity, :Code);
IS: Datenbanken, © Till Hänisch 2000
Warum ODBC ?Am Anfang waren die Daten, sie waren unformatiert, und Dunkelheit herrschte auf der Erde. Und Codd sagte: „Es werde ein relationales Datenmodell“. Und so geschah es.
Und Codd sagte: „Die Daten sollen von den Datenstrukturen der Applikationsprogramme getrennt werden, so daß eine Datenunabhängigkeit entstehe“. Und es war gut.
Und die DBMS-Hersteller sagten: „Wir wollen fruchtbar sein und uns mehren“. Und so geschah es.
Und die Benutzer sagten: „Wir wollen Applikationen einsetzen, um auf die Daten von allen DBMS-Herstellern zuzugreifen“
Und die Applikationsentwickler senkten die Häupter und sagten: „Wir müssen durch das finstere Tal mit den Precompilern oder CLI‘s, Kommunikationsstacks und Protokollen aller Hersteller wandern“. Und es war nicht gut...Und so entstand ODBC
(Kyle Geiger, Inside ODBC)
IS: Datenbanken, © Till Hänisch 2000
ODBC-Architektur
Anwendung
ODBCTreibermanager
ODBCTreiber
Datenbank
ODBCTreiber
Datenbank
ODBCTreiber
Datenbank
IS: Datenbanken, © Till Hänisch 2000
ODBC Open Database Connectivity Industriestandard (Microsoft, IBM,...) datenbankunabhängig
static char cmd[] = "INSERT INTO MESSAGE(SEVERITY,CODE) VALUES (?,?)";
rc=SQLAllocEnv(&henv);rc=SQLAllocConnect(henv,&hdbc);rc=SQLConnect(hdbc,“Kurs",SQL_NTS,ODBC_USERNAME,SQL_NTS,ODBC_PASSWORD,SQL_NTS);
rc=SQLAllocStmt(hdbc,&hstmt);
rc = SQLPrepare(hstmt,cmd,SQL_NTS);
rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,strlen(Severity),0,Severity,0,NULL);
rc = SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,strlen(Code),0,Code,0,NULL);
rc = SQLExecute(hstmt);
IS: Datenbanken, © Till Hänisch 2000
JDBC
Java Database Connectivity
import java.sql.*;
class Employee{ public static void main (String args []) throws SQLException { DriverManager.registerDriver(new com.sybase.jdbc.SybDriver());
Connection conn = DriverManager.getConnection ("jdbc:sybase:Tds:vaio:9898",
"ba", "isdb00");
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery ("select empno,ename from emp");
// Iterate through the result and print the employee names while (rset.next ()) System.out.println (rset.getInt(1) + " " + rset.getString (2)); }}
IS: Datenbanken, © Till Hänisch 2000
JDBC - Statement
Kann SQL-Anweisungen ausführen Spezialfall: PreparedStatement:
Bei mehrfacher Ausführung bleibt SQL-Text gleich, muß nicht bei jeder Ausführung analysiert werden
Bietet u.U. bessere Performance
Statement stmt = conn.createStatement ();
Stmt.executeQuery(“SELECT * FROM EMP“);
PreparedStatement ps = conn.prepareStatement(„SELECT * FROM EMP WHERE EMPNO = ?“);
IS: Datenbanken, © Till Hänisch 2000
JDBC - ResultSet
executeQuery liefert ResultSet-Objekt zurück Kapselt Cursor, kann Ergebnis zeilenweise durchgehen Steht nach executeQuery vor dem ersten Datensatz next() geht einen Datensatz weiter, liefert true zurück, solange aktueller Datensatz gültig
Zugriff auf Spalten mit getXXX (getInt, getString,...)
ResultSet rset = stmt.executeQuery ("select empno,ename from emp");
while (rset.next ()) System.out.println (rset.getInt(1) + " " + rset.getString (2)); }
IS: Datenbanken, © Till Hänisch 2000
JDBC - PreparedStatement
Platzhalter werden mit setXXX mit Werten belegt
Erster Parameter ist Index des Platzhalters
PreparedStatement ps = conn.prepareStatement(„SELECT * FROM EMP WHERE EMPNO = ?“);
for(...) {ps.setInt(1,4711);ResultSet rset = ps.execute();...
}
IS: Datenbanken, © Till Hänisch 2000
JDBC - Transaktionskontrolle
AutoCommit führt nach jedem execute ein commit durch (oft Standard, sinnvoll ?)
Manuelle Commit-Steuerung oft sinnvoller Nicht alle Isolationlevel werden von allen
Datenbanken unterstützt (siehe Datenbanken II)
conn.setAutoCommit(false);
Conn.commit();Conn.rollback();Conn.setTransactionIsolation(
Connection.TRANSACTION_SERIALIZABLE);
IS: Datenbanken, © Till Hänisch 2000
Performance
SQL> describe lagerbewegung Name Null? Type ----------------------------------------- -------- ----------------- NUMMER NOT NULL NUMBER ART NOT NULL NUMBER(2) VORGANGSNUMMER VARCHAR2(10) BESTELLNUMMER NUMBER TEILENUMMER NOT NULL VARCHAR2(15) MENGE NOT NULL NUMBER(10,3) LAGERORT NOT NULL VARCHAR2(12) DATUM NOT NULL DATE
SQL> select count(*) from lagerbewegung; COUNT(*)---------- 159804
IS: Datenbanken, © Till Hänisch 2000
Szenario Lesen aller Records aus einer Tabelleund schreiben in andere, zunächst intern durch DB
Dann verschiedene Programmiertechniken Idee: Bearbeitung der Records nötig, z.B. ergänzen von Werten ...
SQL> create table fastest as select * from lagerbewegung;
Table created.
Elapsed: 00:00:03.91
IS: Datenbanken, © Till Hänisch 2000
Einschub PL/SQL 3G Sprache von Oracle
Andere etwa: Transact SQL (Microsoft/ Sybase) SQL PL (IBM)
An ADA angelehnt Direkte Einbettung von SQL-Statements in Programm
Normalerweise statisches SQL Wozu ?
Definierte Schnittstelle zum Zugriff auf Daten Wird (nach compile) in der DB gespeichert und dort
ausgeführt (Performance) Konsistenz, z.B. direkter Zugriff auf Datentypen in DB User defined functions, z.B.
„select finance.AuftragTotal('01-1691/01') from dual;“
Usw. Erstellung von stored procedures usw.
IS: Datenbanken, © Till Hänisch 2000
Stored procedure
SQL> execute perf_procPL/SQL procedure successfully completed.Elapsed: 00:01:56.90
create or replace procedure perf_proc as cursor c is select * from lagerbewegung; c_rec c%rowtype; i integer;begin open c; i := 0; fetch c into c_rec; while c%found loop insert into perf(nummer,art,vorgangsnummer,...) values (c_rec.nummer,c_rec.art...); i := i + 1; if ((i mod 1000) = 0) then commit; end if; fetch c into c_rec; end loop; close c;end;/
IS: Datenbanken, © Till Hänisch 2000
Java, dynamisches SQL
ResultSet rset = stmt.executeQuery ("select nummer, ...where rownum < 10000"); // Zunächst nur 10.000 records ....
while (rset.next ()) { sql = "insert into perf(nummer,art...)" + “values (" + rset.getInt(1) + "," + ...)"; ins.execute(sql); NumberOfRecords++; if ((NumberOfRecords % CommitInterval) == 0) conn.commit(); }
oracle$ java Perftestelapsed time: 38.146 seconds für 10.000 records,Entspricht insgesamt (extrapoliert) ca. 11 Minuten
IS: Datenbanken, © Till Hänisch 2000
Prepared statement PreparedStatement ps = conn.prepareStatement("insert into perf(...,
values (?,?,?,?,?,?,?,to_date(?,'yyyy-mm-dd hh24:mi:ss'))");
ResultSet rset = stmt.executeQuery ("select nummer ... ");
while (rset.next ()) { ps.setInt(1,rset.getInt(1));
... ps.execute(); NumberOfRecords++; if ((NumberOfRecords % CommitInterval) == 0) conn.commit(); }
oracle$ java PerftestPrepared elapsed time: 17.573 seconds für 10.000 records,Entspricht insgesamt (extrapoliert) ca. 5 Minuten !
IS: Datenbanken, © Till Hänisch 2000
Vergleich
Technik Dauer
Table Copy ohne Transaktion
4 Sekunden
Stored procedure
2 Minuten
Java mit dynamischem Statement
11 Minuten
Java mit prepared statement
5 Minuten
IS: Datenbanken, © Till Hänisch 2000
Besser ... (DB-spezifisch)
SQL> execute perf_bulkPL/SQL procedure successfully completed.Elapsed: 00:00:13.36
create or replace procedure perf_bulk astype recs is table of lagerbewegung%rowtype;data recs;cursor c is select * from lagerbewegung;i integer;begin open c; loop fetch c bulk collect into data limit 10000; forall i in 1..data.count insert into perf values data(i); commit; exit when c%notfound; end loop; close c;end;
IS: Datenbanken, © Till Hänisch 2000
NB: Commit-FrequenzCommit alle n records
Dauer (sec)
1 1132 (*)
10 384
100 285
1000 280
10000 275
Eine Transaktion 259
(*): möglicherweise phys. Speicher zu klein
IS: Datenbanken, © Till Hänisch 2000
Aktive Datenbanken
„normale“ Datenbanken speichern Daten
Aktive Datenbanken führen Aktionen aus (ECA-Modell) Event
Z.B. Änderung von Daten, Zeitpunkt,... Condition
Bedingung, unter der Action ausgeführt wird
Action
IS: Datenbanken, © Till Hänisch 2000
Wozu ?
Denormalisierte Relationen Protokollierung Replikation Materialized views Einfache Integritätsbedingungen durch constraints, komplexere ? Business rules Z.B. „Fakturierte Aufträge dürfen nicht geändert werden“
IS: Datenbanken, © Till Hänisch 2000
Beispiel (Oracle Trigger)
CREATE OR REPLACE TRIGGER TUpdAuftrag BEFORE UPDATE ON Auftrag FOR EACH ROW
BEGIN
IF ((:old.Status = Globvar.Stat_Auftrag_abgerechnet) AND (USER <> '&1')) THEN Error.raise_error(Error.en_Abgerechnet); END IF;END;
CREATE OR REPLACE TRIGGER TDelAuftrag BEFORE DELETE ON Auftrag FOR EACH ROW
BEGIN
IF (:old.Status = Globvar.Stat_Auftrag_abgerechnet) THEN Error.raise_error(Error.en_Abgerechnet); END IF;END;