Programmierung

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
!
!
!
i.w. gleiche Syntax wie bei interaktivem SQL,
zusätzlich Konstrukte für
!
!
!
!
!
C,COBOL, PL/1, FORTRAN, PASCAL,...
Vor-Übesetzung des Programms in Wirts-Sprache
(precompile)
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
Vorgehen
example.pc
C Source mit eingebettetem SQL
Precompiler für C
example.c
C Source, SQl durch DBMS-spezifische
Funktionsaufrufe ersetzt
C Compiler
example.o
Linker
example[.exe]
Object Code
DBMSLibrary
ausführbares Programm
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: Cursor
Iterator, Tupel-Zeiger für satzweise Verarbeitung
EXEC SQL DECLARE name CURSOR FOR select statm.
Operationen:
OPEN
führt Abfrage aus
CLOSE
FETCH name INTO :var1, :var2,...
überträgt Werte der Attribute des aktuellen Datensatzes in
Variablen und setzt Zeiger eins weiter
IS: Datenbanken, © Till Hänisch 2000
prinzipieller Aufbau
EXEC SQL BEGIN DECLARE SECTION;
Deklaration der Übergabevariablen
EXEC 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
!
!
!
!
!
tupelweise Verarbeitung u.U. nicht effizient
!
!
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)
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
ODBC
Treibermanager
ODBC
Treiber
ODBC
Treiber
ODBC
Treiber
Datenbank
Datenbank
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
Statement stmt = conn.createStatement ();
Stmt.executeQuery(“SELECT * FROM EMP“);
PreparedStatement ps = conn.prepareStatement(
„SELECT * FROM EMP WHERE EMPNO = ?“);
!
!
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
IS: Datenbanken, © Till Hänisch 2000
JDBC - ResultSet
ResultSet rset = stmt.executeQuery ("select empno,ename from emp");
while (rset.next ())
System.out.println (rset.getInt(1) + " " + rset.getString (2));
}
!
!
!
!
!
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,...)
IS: Datenbanken, © Till Hänisch 2000
JDBC - PreparedStatement
PreparedStatement ps = conn.prepareStatement(
„SELECT * FROM EMP WHERE EMPNO = ?“);
for(...) {
ps.setInt(1,4711);
ResultSet rset = ps.execute();
...
}
!
Platzhalter werden mit setXXX mit Werten belegt
!
Erster Parameter ist Index des Platzhalters
IS: Datenbanken, © Till Hänisch 2000
JDBC - Transaktionskontrolle
conn.setAutoCommit(false);
Conn.commit();
Conn.rollback();
Conn.setTransactionIsolation(
Connection.TRANSACTION_SERIALIZABLE);
!
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)
IS: Datenbanken, © Till Hänisch 2000
Performance
SQL> describe lagerbewegung
Name
----------------------------------------NUMMER
ART
VORGANGSNUMMER
BESTELLNUMMER
TEILENUMMER
MENGE
LAGERORT
DATUM
SQL> select count(*) from lagerbewegung;
COUNT(*)
---------159804
IS: Datenbanken, © Till Hänisch 2000
Null?
-------NOT NULL
NOT NULL
NOT
NOT
NOT
NOT
NULL
NULL
NULL
NULL
Type
----------------NUMBER
NUMBER(2)
VARCHAR2(10)
NUMBER
VARCHAR2(15)
NUMBER(10,3)
VARCHAR2(12)
DATE
Szenario
!
!
!
Lesen aller Records aus einer Tabelle
und 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
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;
SQL> execute perf_proc
end;
PL/SQL procedure successfully completed.
/
Elapsed: 00:01:56.90
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 Perftest
elapsed 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
Stored procedure
4 Sekunden
Java mit
dynamischem
Statement
Java mit prepared
statement
11 Minuten
2 Minuten
5 Minuten
IS: Datenbanken, © Till Hänisch 2000
Besser ... (DB-spezifisch)
create or replace procedure perf_bulk as
type 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;
SQL> execute perf_bulk
PL/SQL procedure successfully completed.
Elapsed: 00:00:13.36
IS: Datenbanken, © Till Hänisch 2000
NB: Commit-Frequenz
Commit 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
!
!
Condition
!
!
Z.B. Änderung von Daten, Zeitpunkt,...
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);
IS: Datenbanken, © Till Hänisch 2000
END IF;
END;