Datenbank-Zugriffsschnittstellen. © Prof. T. Kudraß, HTWK Leipzig Überblick Datenbankzugriff mit PHP Datenbankzugriff mit JDBC Datenbankzugriff mit PSP

Datenbank-Zugriffsschnittstellen

© Prof. T. Kudraß, HTWK Leipzig

Überblick

Datenbankzugriff mit PHP Datenbankzugriff mit JDBC Datenbankzugriff mit PSP Zugriff auf Metadaten

– PHP– PL/SQL– JDBCC

Dynamisches SQL– Parametrisierung von Anfragen– Dynamischer Aufbau von WHERE-Bedingungen– Dynamische SELECT- und FROM-Klausel


Kommunikation mit der Datenbank

Vorbereiten einer SQL-Anweisung

Freigabe der Ressourcen

Verbindungsaufbau

Lesen der Ergebnismenge

Verbindungsabbau

Ausführung einer Anweisung


Datenbankzugriff mit PHP

Zahlreiche APIs für alle gängigen DBMS IN PHP, Portierung erschwert durch unterschiedliche APIs

Daher Entwicklung einer einheitlichen DB-Schnittstelle im Rahmen von PHP-PEAR (PHP Extension and Application Repository)

PEAR = Bibliothek von Open Source Paketen– Authentifizierung, Verschlüsselung, XML, Web-Services u.a.– müssen nachträglich installiert werden

Paket Pear:DB– Allgemeine Schnittstelle für die Kommunikation mit verschiedenen

DBMS – Interner Aufruf der von PHP bereitgestellten Methoden für jedes

DBMS Arbeit mit Oracle: 2 verschiedene Pakete

– Standard-Oracle-Funktionen, Präfix ora– Erweiterte Oracle-Funktionen, die das Oracle8-Call-Interface (OCI8)

nutzen, Präfix oci (Grundlage für PEAR:DB)


Verbindungsaufbau

Pear:DB: – (einmalige) Einbindung der Datei DB.php auf dem Pear:DB-Paket

require_once ′DB.php′;– Data Source Name (DSN)

DBMS + Benutzerdaten + Hostphptype://username:password@hostspec

– Beispiel:oci8://username:password@iproject

– Verbindungsaufbau$db = &DB::connect(′oci8://username:password@iproject′);

oci-Funktion– Syntax:

int ocilogon (string user, string password [,string oracle_sid]);

– Beispiel: $conn = ocilogon(“username“,“passwort“,“iproject“);


Vorbereiten einer SQL-Anweisung

Pear:DB – Vorbereiten einer Anfrage, nur wenn Parameter enthalten, die bei

Ausführung belegt werden müssen (Parameter als ? notiert)

sth=$db->prepare(“SELECT Stud_Matrikel,Stud_Name,“. “Stud_Vorname,Stud_Email,Stud_Studgang“. “FROM Studenten LEFT JOIN Teilnehmer“.

“ON Stud_Matrikel=Teiln_SMatrikel_FK“.“WHERE Teiln_Kurs_ID_FK=?“.“ORDER BY Stud_Name“);

sth=$db->prepare(“SELECT Stud_Matrikel,Stud_Name,“. “Stud_Vorname,Stud_Email,Stud_Studgang“. “FROM Studenten LEFT JOIN Teilnehmer“.

“ON Stud_Matrikel=Teiln_SMatrikel_FK“.“WHERE Teiln_Kurs_ID_FK=?“.“ORDER BY Stud_Name“);

oci-Funktion– Parsen einer DB-Anfrage vor deren Ausführung int ociparse (int connhandle, string query)$stmt=OCIParse($conn, “SELECT Kurse.*, Kl_Name || ‘, ‘|| Kl_Vorname “.

“AS Kl_Fullname, Kl_Email “.“FROM Kurse LEFT JOIN Kursleiter “.“ON Kurs_Leiter_ID_FK = Kl_ID ORDER BY Kurs_Name“);

$stmt=OCIParse($conn, “SELECT Kurse.*, Kl_Name || ‘, ‘|| Kl_Vorname “.

“AS Kl_Fullname, Kl_Email “.“FROM Kurse LEFT JOIN Kursleiter “.“ON Kurs_Leiter_ID_FK = Kl_ID ORDER BY Kurs_Name“);


Ausführung einer SQL-Anweisung

Pear:DB: – Übergabe einer Zeichenkette an query-Funktion

$res=& $db->query(“SELECT Kurse.*, Kl_Name, Kl_Email “. “FROM Kurse LEFT JOIN Kursleiter “.“ON Kurs_Leiter_ID_FK = Kl_ID “.“ORDER BY Kurs_Name“);

$res=& $db->query(“SELECT Kurse.*, Kl_Name, Kl_Email “. “FROM Kurse LEFT JOIN Kursleiter “.“ON Kurs_Leiter_ID_FK = Kl_ID “.“ORDER BY Kurs_Name“);

– Ausführung einer mit prepare vorbereiteten Anfrage

# Übergabe eines einzelnen Parameters$res = & $db->execute($sth, $kurs_id);

# Übergabe mehrerer Parameter als Array$werte = array($kurs_id, $Kl_Name);

$res = & $db->execute($sth, $werte);

# Übergabe eines einzelnen Parameters$res = & $db->execute($sth, $kurs_id);

# Übergabe mehrerer Parameter als Array$werte = array($kurs_id, $Kl_Name);

$res = & $db->execute($sth, $werte);


Ausführung einer SQL-Anweisung (Forts.)

oci-Funktion: – Übergabe des Statement Handle an die Funktion

ociexecute int ociexecute (int statement [,int model]);

Beispiel:ociexecute($stmt)

– Optionaler Parameter mode OCI_COMMIT_ON_SUCCESS: Automatisches Commit am Ende

der Transaktion OCI_DEFAULT: Wenn Wert gesetzt, muß jede Transaktion explizit

mit commit oder rollback beendet werden

Standard: OCI_COMMIT_ON_SUCCESS


Lesen der Ergebnismenge

Pear:DB: – Zeilenweises Lesen der Ergebnismenge– Fetch-Mode setzen, um Zugriff über Spaltennamen zu

ermöglichen $db->setFetchMode(DB_FETCHMODE_ASSOC)

– Ausgabe in einer Schleife, z.B.

while($res->fetchInto($row)) { echo “<tr>\n“ echo “<td class=\“inner\“>“.$row[“STUD_MATRIKEL“].“</td>\n“; echo “<td class=\“inner\“>“.$row[“STUD_VORNAME“].“</td>\n“; [. . .]}

while($res->fetchInto($row)) { echo “<tr>\n“ echo “<td class=\“inner\“>“.$row[“STUD_MATRIKEL“].“</td>\n“; echo “<td class=\“inner\“>“.$row[“STUD_VORNAME“].“</td>\n“; [. . .]}


Lesen der Ergebnismenge (Forts.)

oci-Funktion – Ermittelt Referenz auf Array mit den Daten einer Ergebniszeile int ocifetchinto(int statement, array &result

[, int mode]);

while(OCIFetchInto($stmt, &$feld, OCI_ASSOC)){ echo "<tr>\n"; echo "<td class=\"inner\">".

$feld["KURS_NAME"]."</td>\n"; echo "<td class=\"inner\">".

$feld["KURS_WOCHENTAG"]."</td>\n";

... echo "</tr>\n";}

while(OCIFetchInto($stmt, &$feld, OCI_ASSOC)){ echo "<tr>\n"; echo "<td class=\"inner\">".

$feld["KURS_NAME"]."</td>\n"; echo "<td class=\"inner\">".

$feld["KURS_WOCHENTAG"]."</td>\n";

... echo "</tr>\n";}


Freigabe der Ressourcen und Verbindungsabbau

Pear:DB– Query-Objekte, die mit prepare vorbereitet wurden,

mit freePrepared freigeben$db->freePrepared($sth)

– Verbindungsabbau mittels disconnect$db->disconnect()

oci-Funktion– Freigabe der Statement-Ressourcen mittels

ocifreestatementint ocifreestatement(int statement)

– Verbindungsabbau durch Methode ocilogoffint ocilogoff(int connhandle)


Oracle PHP API

int Ora_Bind (int cursor, string PHP variable_name, string SQL parameter name, int length, int type)

int Ora_Close (int cursor)

string Ora_ColumnName (int cursor, int col)

string Ora_ColumnType (int cursor, int col)

string Ora_Commit (int conn)

int Ora_CommitOff (int conn)

int Ora_CommitOn (int conn)

int Ora_Do (int conn, string query)

Bindet eine PHP-Variabel an einenOracle-Parameter

Schließt Oracle-Cursor

Ermittelt Spaltennamen

Ermittelt Spaltentyp

Commit eine Transaktion

Ausschalten des autom. Commit

Einschalten des autom. Commit

schnelle Kombination aus Ora_Parse, Ora_Exec und Ora_Fetch


Oracle PHP API (Forts.)

string Ora_Error (int cursor_or_connection)

int Ora_Error_Code (int cursor_or_connection)

int Ora_Exec (int cursor)

int Ora_Fetch (int cursor)

mixed Ora_GetColumn (int cursor, mixed col)

int Ora_LogOff (int connection)

int Ora_LogOn (string user, string password)

int Ora_NumCols (int cursor)

int Ora_NumRows (int cursor)

Liefert eine Oracle-Fehlermeldung

Liefert einen Oracle-Fehlercode

Führt einen geparsten Befehl aus

Liest einen Datensatz aus

Liest Daten aus einem gefetchten Satz

Schließt eine Oracle-Verbindung

Öffnet eine Oracle-Verbindung

Ermittelt Anzahl Spalten vom Ergebnis

Ermittelt Anzahl Zeilen vom Ergebnis


Oracle PHP API (Forts.)

string Ora_Open (int connection)

int Ora_Parse (int cursor, string sql, int defer)

int Ora_Rollback (int connection)

Öffnet einen Oracle-Cursor

Analysiert eine SQL-Anweisung und verknüpft diese mit dem angeg. Cursor

Macht eine Oracle-Transaktion rückgängig

Detaillierte Informationen über alle Oracle-Funktionen:

http://www.php.net/manual/ref.oracle.php


PHP-Beispielprogramm

<?php

putenv( "ORACLE_SID=sid1"); putenv( "ORACLE_HOME=/u01/app/oracle/product/8.0.5");

$handle = ora_plogon( "SCOTT@sid1", "TIGER") or die; $cursor = ora_open($handle); ora_commitoff($handle);

$query = "SELECT * FROM EMP"; ora_parse($cursor, $query) or die; ora_exec($cursor);

echo "<HTML><PRE>\n"; echo "$query\n\n";


PHP-Beispielprogramm (Forts.)

$numcols = 0; while(ora_fetch($cursor)){ $numcols = ora_numcols($cursor); for($column=0; $column < $numcols; $column++){ $data = trim(ora_getcolumn($cursor, $column)); if($data =="") $data = "NULL"; echo"$data\t"; } echo "\n"; } $numrows = ora_numrows($cursor); echo "\nROWS RETURNED: $numrows\n"; echo "</PRE></HTML>\n";

ora_close($cursor);

?>


Entwicklung von PL/SQL Server Pages (PSP)

1. Schreiben der PL/SQL Server Page Standard-Skripttag Spezielle Skripttags:

- Pages- Prozeduren- Parameter- Deklarationen- Expression Blocks- Include- Kommentare

2. Kompilieren der PSP-Datei als Stored Procedure3. Aufruf der PSP im Browser


Standard-Skripttag

Funktion: Begrenzung von PL/SQL-Anweisungen

Inhalt: beliebige PL/SQL-Statements, z.B. Prozeduraufrufe

<% PL/SQL code %><% PL/SQL code %>Syntax


Spezielle Tags:Page Direktive

Funktion: Charakterisierung der PSP Attribute:

– language: verwendete Skriptsprache (PL/SQL Standard)– contentType: Inhaltstyp der Seite; text/html Standard– errorPage: PSP-Seite, die auf auftretenden Fehlern aufgerufen

wird (Standard: keine Datei)

<% page [language=“PL/SQL“][contentType=“content type string“][errorPage=“file.psp“] %>

<% page [language=“PL/SQL“][contentType=“content type string“][errorPage=“file.psp“] %>

Syntax


Spezielle Tags:Procedure & Parameter Direktive

Funktion: Spezifikation von Prozedur und Parameter (alles IN) Attribute:

– procedure: Name des Prozedur– parameter: Name des Parameters– type: Datentyp des Parameters; Standardwert varchar2 (ohne Länge)– default: Standardwert für Parameter

<% plsql procedure=“procedure name“ %><% plsql procedure=“procedure name“ %>Syntax

<% plsql parameter=“parameter name“ %>[type=“PL/SQL datatype“][default=“value“] %>

<% plsql parameter=“parameter name“ %>[type=“PL/SQL datatype“][default=“value“] %>


Spezielle Tags:Declaration Direktive

Funktion: Deklaration von Variablen und Cursor auf der ganzen Seite

Beispiel:

<%! PL/SQL declaration;[PL/SQL declaration;] ... %>

<%! PL/SQL declaration;[PL/SQL declaration;] ... %>

Syntax

<%! cursor prod_cur is select * from products where price between minprice and´maxprice

vc_name varchar2(200):=‘Peter‘; %>

<%! cursor prod_cur is select * from products where price between minprice and´maxprice

vc_name varchar2(200):=‘Peter‘; %>


Spezielle Tags: Expression Block / Print Direktive

Funktion: Ausgabe eines beliebigen PL/SQL-Ausdrucks (String, Zahl, Ergebnis eines Funktionsaufrufs)

Beispiel:

<%= PL/SQL expression %> <%= PL/SQL expression %> Syntax

<%= ‘The employee name is ‘|| emp_rec.ename %>oderThe employee name is <%= emp_rec.ename %>

<%= ‘The employee name is ‘|| emp_rec.ename %>oderThe employee name is <%= emp_rec.ename %>


Spezielle Tags: Include Direktive

Funktion: Einbinden des Quelltexts anderer Dateien in die Seite

Beispiel:

<%@ include file =“path name“ %> <%@ include file =“path name“ %> Syntax

<%= include file=“header.inc“ %><%= include file=“header.inc“ %>

Anmerkungen:- Datei darf HTML- und PSP-Skriptelemente enthalten- Einbindung nur einmal zur Übersetzungszeit (mögliche

Alternative: Einbindung durch Prozeduraufruf)


Spezielle Tags: Kommentare

Funktion: erscheint nicht in der generierten Prozedur bzw. in den daraus erstellten HTML-Seiten

<%-- Kommentar --%> <%-- Kommentar --%> Syntax

<%!-- Kommentar --%> <%!-- Kommentar --%> Syntax

Funktion: Kommentare, die in der HTML-Ausgabe erscheinen (normale HTML-Syntax)

-- einzeiliger Kommentar /* mehrzeiliger Kommentar */

-- einzeiliger Kommentar /* mehrzeiliger Kommentar */

Syntax

Funktion: Kommentare innerhalb eines PL/SQL-Blocks


Kompilieren der PL/SQL Server Page

loadpsp [-replace] - user username/password[@connect_string] [include_file_name ...] [<page1> <page2> ... ]

loadpsp [-replace] - user username/password[@connect_string] [include_file_name ...] [<page1> <page2> ... ]

Syntax

Beispiel

loadpsp -replace - user name/passw@iproject timestamp.inc display_cust.psp

loadpsp -replace - user name/passw@iproject timestamp.inc display_cust.psp

Attribute:- replace: überschreibt Prozedur gleichen Namens- username/password@connect_string: Login-Information- include-file_name: Dateien, die mittels include eingebunden werden

- page1 ...: Name der PSP-Dateien, die kompiliert werden sollen


ParameterverarbeitungGET-Methode

Beispiel:

http://abraham.imn.htwk-leipzig.de:7777/pls/web/cust_order_items?ord_id=100&cust_id=100

http://abraham.imn.htwk-leipzig.de:7777/pls/web/cust_order_items?ord_id=100&cust_id=100

URL bei Submit:

...<form method=“GET“ action=“cust_order_items“><input type=“hidden“ name=“cust_id“ value=“<%=cust_id %“><input type=“text“ name=“ord_id“ size=“10“ value=“<%=order_id %“><input_type=“submit“ value = “Order Details“></form>...

...<form method=“GET“ action=“cust_order_items“><input type=“hidden“ name=“cust_id“ value=“<%=cust_id %“><input type=“text“ name=“ord_id“ size=“10“ value=“<%=order_id %“><input_type=“submit“ value = “Order Details“></form>...


ParameterverarbeitungPOST-Methode

Beispiel:

http://abraham.imn.htwk-leipzig.de:7777/pls/web/cust_order_items

http://abraham.imn.htwk-leipzig.de:7777/pls/web/cust_order_items

URL bei Submit:

...<form method=“POST“ action=“cust_order_items“><input type=“hidden“ name=“cust_id“ value=“<%=cust_id %“><input type=“text“ name=“ord_id“ size=“10“ value=“<%=order_id %“><input_type=“submit“ value = “Order Details“></form>...

...<form method=“POST“ action=“cust_order_items“><input type=“hidden“ name=“cust_id“ value=“<%=cust_id %“><input type=“text“ name=“ord_id“ size=“10“ value=“<%=order_id %“><input_type=“submit“ value = “Order Details“></form>...


Parameterübergabe

<%@ plsql procedure "show_detail" %><%@ plsql parameter=“p_ID“ type=“number“ default=“0“%><%@ plsql parameter=“p_Name“ type=“varchar2“ default=“‘‘“%>

<SELECT NAME = “p_ID“ size=“1“><% for item in

(select ID, Name from Products order by Name) loop %><option value=“<%=item.ID %>“ <%=item.Name %></option>

<% end loop; %></SELECT>

<SELECT NAME = “p_ID“ size=“1“><% for item in

(select ID, Name from Products order by Name) loop %><option value=“<%=item.ID %>“ <%=item.Name %></option>

<% end loop; %></SELECT>

SELECT-Box wird durch Abfrage auf Tabelle Products mit Werten gefüllt, Zuordnung zum Parameter p_ID2. Eingabeparameter: p_Text als Input-Feld in HTML-Formular

<form method=“post“ action=“show_detail“> <p>Eingabe: <input type=text size=50 maxlength=50 name=“p_Name“> <SELECT NAME=“p_ID“ size=“1“> ... </SELECT> <input type=“submit“ value=“Abschicken“> </form>

<form method=“post“ action=“show_detail“> <p>Eingabe: <input type=text size=50 maxlength=50 name=“p_Name“> <SELECT NAME=“p_ID“ size=“1“> ... </SELECT> <input type=“submit“ value=“Abschicken“> </form>


Fehlerbehandlung - Verwendung von Errorpages

erwartete vs. unerwartete Fehler erwarteter Fehler: NO_DATA_FOUND unerwarteter Fehler: 2 Produkte mit der gleichen ID Verwendung von ErrorPage in Page-Direktive zur Behandlung

unerwarteter Fehler Nachteil: keine Parameterübergabe möglich (z.B.

Fehlerzeitpunkt, Eingabeparameter)

EXCEPTION WHEN OTHERS THENhtp.init;error; END; -- error: Name der Fehlerseite


Benutzerdefinierte Ausnahmebehandlung (Exceptions)

<%@ page errorPage="Error_Page.psp" %> -- Angabe der Fehlerseite...l_exception EXCEPTION; -- Deklaration der Ausnahme l_exception...IF (TO_DATE(arrival_date,'YYYY-MM-DD') <= SYSDATE)THEN RAISE l_exception; -- Datum liegt vor dem aktuellem DatumEND IF;EXCEPTION WHEN l_exception THEN -- Test, ob es der vom Benutzer ausgelöste Fehler ist RAISE; -- Weiterleitung des Fehlers an übergeordnete Fehler- END; -- behandlung in Error_Page

...<% IF (SQLERRM = 'User-Defined Exception') THEN %> <% l_error_message := 'Date values should be greater than Current Date'; %> <%= l_error_message %> -- Ausgabe der Fehlermeldung<% ELSE %> <%= SQLERRM %> -- wenn anderer Fehler, Ausgabe der Fehlermeldung<% END IF; %>...


Zusammenfassendes Beispiel:Ausgabe einer Kursliste

<table cellspacing="2" cellpadding="3" border="0"> <tr> <th class="inner">Titel</th> <th class="inner">Leiter</th> <th class="inner">Tag</th> <th class="inner">Zeit von</th> <th class="inner">Zeit bis</th> <th class="inner">Ort</th> <th class="inner">Plätze</th> </tr> <% FOR ds IN (SELECT Kurse.*, Kl_Name ||’, ’ || Kl_Vorname AS Kl_Fullname, Kl_EMail FROM Kurse LEFT JOIN Kursleiter ON Kurs_Leiter_ID_FK=Kl_ID ORDER BY Kurs_Name) LOOP%>

<table cellspacing="2" cellpadding="3" border="0"> <tr> <th class="inner">Titel</th> <th class="inner">Leiter</th> <th class="inner">Tag</th> <th class="inner">Zeit von</th> <th class="inner">Zeit bis</th> <th class="inner">Ort</th> <th class="inner">Plätze</th> </tr> <% FOR ds IN (SELECT Kurse.*, Kl_Name ||’, ’ || Kl_Vorname AS Kl_Fullname, Kl_EMail FROM Kurse LEFT JOIN Kursleiter ON Kurs_Leiter_ID_FK=Kl_ID ORDER BY Kurs_Name) LOOP%>


Zusammenfassendes Beispiel:Ausgabe einer Kursliste (Forts.)

<tr> <%=’<td class="inner">’||ds.Kurs_Name||’</td>’%> <%=’<td class="inner"><a href="mailto:’||ds.Kl_EMail||’">’ ||ds.Kl_Fullname||’</a></td>’%> <%=’<td class="inner">’||ds.Kurs_Wochentag||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Zeitvon||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Zeitbis||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Ort||’</td>’%> <% IF ds.Kurs_AktTeiln < ds.Kurs_MaxTeiln THEN %> <%=’<td class="inner2">’ || ds.Kurs_AktTeiln||’/’ ||ds.Kurs_MaxTeiln||’</td>’%> <% ELSE %> <%=’<td class="inner3">’ ||ds.Kurs_AktTeiln||’/’ ||ds.Kurs_MaxTeiln||’</td>’%> <% END IF; %> </tr> <% END LOOP;%></table>

<tr> <%=’<td class="inner">’||ds.Kurs_Name||’</td>’%> <%=’<td class="inner"><a href="mailto:’||ds.Kl_EMail||’">’ ||ds.Kl_Fullname||’</a></td>’%> <%=’<td class="inner">’||ds.Kurs_Wochentag||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Zeitvon||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Zeitbis||’</td>’%> <%=’<td class="inner">’||ds.Kurs_Ort||’</td>’%> <% IF ds.Kurs_AktTeiln < ds.Kurs_MaxTeiln THEN %> <%=’<td class="inner2">’ || ds.Kurs_AktTeiln||’/’ ||ds.Kurs_MaxTeiln||’</td>’%> <% ELSE %> <%=’<td class="inner3">’ ||ds.Kurs_AktTeiln||’/’ ||ds.Kurs_MaxTeiln||’</td>’%> <% END IF; %> </tr> <% END LOOP;%></table>


Ausgabe der PSP-Beispielseite


Überblick: Datenbankanfragen mit JDBC

Query

Close

Connect

Processresults

Datenbankverbindung herstellen

Datenbankanfrage

Verbindung zur DB schließen

Ergebnisse verarbeiten


Phase 1: Connect

Query

Close

Connect

Processresults

Driver registrieren

Verbindung zur Datenbank


JDBC Driver

Ist ein Interpreter, der JDBC Methodenaufrufe in herstellerspezifische Datenbank-Kommandos übersetzt.

DriverJDBC CallsJDBC Calls

Datenbank Datenbank KommandosKommandos

Datenbank

Implementiert Interfaces in java.sql Kann auch herstellerspezifische

Erweiterungen zum JDBC-Standard bereitstellen.


O9 oder O10

Oracle JDBC Driver: Thin Client Driver

Komplett in Java geschrieben, Typ 4 Driver Verwendet das Oracle Net Service Protokoll Muß von Applets genutzt werden.

Applet

JDBC

ClientClient ServerServer

Thin driver


Oracle JDBC Driver: OCI Client Driver

Geschrieben in C and Java, Typ 2 Driver Oracle Client Software muß installiert sein.

ClientClient ServerServer

O9 oder O10

Application

JDBC

OCI driver

ocixxx.dll


Oracle JDBC Driver: Server-Seitiger Driver

Läuft innerhalb der Datenbank Muß von Java Stored Procedures genutzt

werden.

Oracle8Oracle8ii

Stored Procedure

JDBC

Server-side Driver

C Library

SQLEngine


Andere JDBC Driver

JDBC-ODBC Bridge– Übersetzt JDBC in Open Database Connectivity

Calls (ODBC)

– Erlaubt Kommunikation mit existierenden ODBC Drivern wenn kein JDBC Driver verfügbar

Oracle Lite Driver

Für Kommunikation mit einer Oracle Lite Datenbank


JDBC URLs

JDBC verwendet URL, um Datenbankverbindung zu identifizieren.

jdbc:<subprotocol>:<subname>

ProtocolProtocolDatabaseDatabaseIdentifierIdentifier

jdbc:oracle:<driver>:@<database>

SubprotocolSubprotocol


JDBC URLs mit Oracle Drivern

Thin driver

OCI driver

Server-side Driver: Verwende die Default Connection

jdbc:oracle:thin:@<host>:<port>:<SID>

jdbc:oracle:oci8:@<TNSNAMES entry>


Aufbau einer Connection

1. Registriere Driver.

DriverManager.registerDriver (neworacle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection

(URL, userid, password);

Connection conn = DriverManager.getConnection

("jdbc:oracle:thin:@localhost:1521:iproject",

"userid", "password");

2. Connect zur Datenbank.


Default Connection

Default Connection für Verbindung innerhalb des Servers.

Nutze OracleDriver.defaultConnection().

Connection conn = (new

oracle.jdbc.driver.OracleDriver()).defaultConnection();

Die resultierende Connection geht zur bestehenden Datenbanksitzung.

Default Connection nicht schließen.


Phase 2: Query

Close

Connect

Query Erzeuge ein Statement

Processresults

Abfrage auf Datenbank


Statement Object

Ein Statement Objekt sendet SQL-Befehl zur Datenbank.

Man benötigt aktive Connection, um JDBC Statement zu erzeugen.

Statement hat drei Methoden, um ein SQL Statement zu erzeugen:– executeQuery()für QUERY Statements

– executeUpdate()für INSERT, UPDATE, DELETE, oder DDL statements

– execute() für beliebiges Statement


Datenbank-Abfragen

1. Erzeuge ein leeres Statement Objekt.

2. Führe das Statement aus.

Statement stmt = conn.createStatement();

ResultSet rset = stmt.executeQuery(statement);

int count = stmt.executeUpdate(statement);

boolean isquery = stmt.execute(statement);


Datenbank-Abfragen: Beispiele

Ausführen eines SELECT Statement.

Statement stmt = conn.createStatement();ResultSet rset = stmt.executeQuery ("SELECT "+ "Kurse.*, Kl_Name ||’, ’|| Kl_Vorname AS " + "Kl_FName, Kl_EMail "+ "FROM Kurse LEFT JOIN Kursleiter "+ "ON Kurs_Leiter_ID_FK=Kl_ID "+ "ORDER BY Kurs_Name");

• Ausführen eines DELETE Statement.Statement stmt = conn.createStatement();int rowcount = stmt.executeUpdate (" DELETE FROM Studenten " + " WHERE Stud_Matrikel=1234567");


Phase 3: Verarbeiten der Ergebnisse

Close

QueryDurchlaufen der Ergebnisse

ProcessResults

Zuweisen der Ergebnisse an Java- Variablen

Connect


Das ResultSet Objekt

JDBC liefert die Ergebnisse einer Query in einem ResultSet Objekt.

Ein ResultSet verwaltet einen Cursor, der auf den aktuellen Datensatz zeigt.

Verwende next() zum Durchlaufen des Result Set Satz für Satz.

getString(), getInt(), usw. für Wertzuweisung an Java-Variablen.


Verarbeitung der Ergebnisse

2. Nutze getXXX() zum Lesen der Spaltenwerte.

while (rset.next()) { … }

String val =

rset.getString(colname);

while (rset.next()) {

int kurs_id = rset.getInt("KURS_ID");

String kurs_name = rset.getString(“KURS_NAME");

… // Variablen verarbeiten

}

String val =

rset.getString(colIndex);

1. Durchlaufen des Result Set.


Behandlung von SQL Nullwerten

Primitive Java Typen haben keine Nullwerte. Deshalb keine primitiven Typen verwenden,

wenn evtl. Nullwerte gelesen werden. Nutze ResultSet.wasNull(), um zu

bestimmen, ob eine Spalte einen Nullwert aufweist.

while (rset.next()) { String kurs_name = rset.getString("KURS_NAME"); if (rset.wasNull()) { … // Handle null value }…}


Phase 4: Close

Connect

Query

ProcessResults

Close

Schließe Result Set

Schließe Statement

Schließe Connection


Schließen

1. Schließe ResultSet Objekt

rset.close();

stmt.close();

conn.close();

2. Schließe Statement Objekt

3. Schließe Connection (nicht notwendig bei serverseitigem Driver)


Einordnung der Oracle JDBC Driver

Applet

Client-Application

EJB, Servlet(auf Middle Tier)

EJB, Servlet(in der Datenbank)

Stored Procedure

OCI

Programm-Typ

Thin

Driver

Thin

Server side

OCI

Thin

Server side


Zugriff auf Metadaten in Pear:DB

Funktion tableinfo zum Lesen der Metadaten von Tabelle oder ResultSet

Ergebnis in Array

# $db ist Datenbankhandle

$metadaten = $db->tableInfo(‘tabellenname‘);

# $db ist Datenbankhandle

$res =& $db->query(‘SELECT * FROM tablename‘);

$metadaten = $db->tableInfo($res);


Zugriff auf Metadaten in Pear:DB(Forts.)

Metadaten

– table: Name der Tabelle

– name: Name der Spalte

– type: Datentyp der Spalte

– len: Länge einer Spalte

– flags: zusätzlich Werte, die der Spalte zugewiesen sind

for ($i=0; $i<sizeof($metadaten); $i++) { echo "Tabelle: ".$metadaten[$i][’table’]."<br>"; echo "Spaltenname: ".$metadaten[$i][’name’]."<br>"; echo "Datentyp: ".$metadaten[$i][’type’]."<br>"; echo "Laenge: ".$metadaten[$i][’len’]."<br>"; echo "Flags: ".$metadaten[$i][’flags’]."<br>"; echo "<br>";}


Zugriff auf Metadaten in PL/SQL

Zugriff auf Metadaten über Views des Oracle Data Dictionary = Sichten auf zugrundeliegende Systemtabellen, z.B. all_tab_columns, all_objects, all_tables

Definierte Schnittstelle unabhängig von der DBMS-Version

FOR ds IN ( SELECT table_name, column_name, data_type,data_length, nullable

FROM all_tab_columns WHERE OWNER=’THOMAS’ AND TABLE_NAME=’STUDENTEN’ ORDER BY column_id )

LOOP htp.prn(’ Tabellenname: ’||ds.Table_Name||’<br>’); htp.prn(’ Spaltenname: ’||ds.Column_Name||’<br>’); htp.prn(’ Datentyp: ’||ds.Data_Type||’<br>’); htp.prn(’ Laenge: ’||ds.data_length||’<br>’); htp.prn(’ isNullable: ’||ds.nullable||’<br>’);END LOOP;%>


Zugriff auf Metadaten eines Result Set in PL/SQL

Nutze das Package DBMS_SQL Parsen der SQL-Anweisung

cur := dbms_sql.open_cursor;-- Parsen der Anfrage, DMBS_SQL.native wird für alle Oracle--- Datenbanken > Version7 verwendetdbms_sql.parse(cur,’SELECT * FROM studenten’,DBMS_SQL.native);-- Ausführen der Anfragei := dbms_sql.execute(cur);

Zugriff auf Metadaten des Resultset

dbms_sql.describe_columns(cur, spaltenanzahl, ds);

type desc_rec is record ( col_type BINARY_INTEGER := 0, col_max_len BINARY_INTEGER := 0, col_name VARCHAR2(32) := ’’, col_null_ok BOOLEAN := TRUE);


Zugriff auf Metadaten eines Result Set in PL/SQL (Forts.)

Ausgabe der Metadaten

-- hole ersten Datensatzrec := ds.first;IF (rec is not null) THEN LOOP htp.prn(’ Spaltenname: ’||ds(rec).col_name ||’<br>’); htp.prn(’ Datentyp: ’ ||ds(rec).col_type ||’<br>’); htp.prn(’ Laenge: ’ ||ds(rec).col_max_len||’<br>’); IF (ds(rec).col_null_ok) THEN htp.prn(’ isNullable: true <br>’); ELSE htp.prn(’ isNullable: false <br>’); END IF; htp.prn(’<br>’);-- naechster Datensatz rec := ds.next(rec); EXIT WHEN (rec is null); END LOOP;END IF;


Zugriff auf Metadaten in JDBC(Datenbank)

Klasse DatabaseMetaData enthält Informationen über das spezifische DBMS, z.B.

– Datenbank-Version– maximale Zeichenkettenlänge für Bezeichner– unterstützte Funktionen (z.B. EXISTS-Subquery)– unterstützte Datentypen

BeispielDatabaseMetaData md = conn.getMetaData();ResultSet rset = md.getTables(null,"THOMAS",null, null);while (rset.next()) { out.println("Schema: "+rset.getString("TABLE_SCHEM")+", "); out.println("Tabellenname: "+ rset.getString("TABLE_NAME")+"<br>");}

Schema: THOMAS, Tabellenname: KURSESchema: THOMAS, Tabellenname: KURSLEITER . . .


Zugriff auf Metadaten in JDBC(ResultSet)

Klasse ResultSetMetaData enthält Informationen über das Ergebnis einer Anfrage, z.B.

– Anzahl Spalten– Spaltenname und Datentyp– Eigenschaften wie Zulässigkeit von Nullwerten

BeispielStatement stmt = conn.createStatement ();ResultSet rsetQuery = stmt.executeQuery ("SELECT * FROM Kurse ORDER BY Kurs_Name");

ResultSetMetaData rsmetadata = rsetQuery.getMetaData();int spalten = rsmetadata.getColumnCount();

for (int i=1;i<=spalten;i++) { out.println("Spaltenname: "+rsmetadata.getColumnName(i)+"<br>"); out.println("Datentyp: "+rsmetadata.getColumnType(i)+"<br>"); out.println("Laenge: "+rsmetadata.getPrecision(i)+"<br>"); out.println("isNullable: "+rsmetadata.isNullable(i)+"<br>");}


Unterstützung dynamischer Anfragen

Dynamische Belegung von Werten in der WHERE-Klausel (durch Parametrisierung)

Dynamischer Aufbau der WHERE-Bedingung (d.h. variable Spalten)

Dynamischer Aufbau der zu selektierenden Spalten (SELECT-Liste) bzw. Tabellen (FROM-Klausel)


Platzhalter in der WHERE-Klausel

PHP: Vorbereiten der Anfrage mit Parameternsth = $db->prepare("SELECT Stud_Matrikel, Stud_Name,".

"Stud_Vorname, Stud_Email, Stud_Studiengang "."FROM Studenten LEFT JOIN Teilnehmer "."ON Stud_Matrikel = Teiln_SMatrikel_FK "."WHERE Teiln_Kurs_ID_FK= ?"." ORDER BY Stud_Name");

# Übergabe eines einzelnen Parameters$res =& $db->execute($sth, $kurs_id);

PL/SQL: Nutzung von Variablen bzw. Parametern bei Prozeduren


Prepared Statement Object in JDBC

Ein Prepared Statement Objekt enthält vorübersetzte SQL-Befehle.

Ist geeignet, wenn Statement mehr als einmal ausgeführt werden muß.

Ein Prepared Statement kann Variablen enthalten, die jedesmal bei Ausführung definiert werden.


Erzeugen eines Prepared Statement

Erzeuge Prepared Statement, identifiziere Variablen mit ?

PreparedStatement pstmt =

conn.prepareStatement ("UPDATE Kurse

SET Kurs_Maxteiln = ? where Kurs_ID = ?");

PreparedStatement pstmt =

conn.prepareStatement

("SELECT Kurs_Name, Kurs_Beschreibung" +

" FROM Kurse "+ " WHERE Kurs_ID=?");


Ausführen eines Prepared Statement

1. Variablen-Werte übergeben

pstmt.setXXX(int paramIndex, XXX paramValue);

PreparedStatement pstmt = conn.prepareStatement ("UPDATE Kurse SET Kurs_Maxteiln = ? where Kurs_ID = ?");

pstmt.setInt (1, 20);

pstmt.setInt (2, 25);

int count = pstmt.executeUpdate();

2. Statement ausführen.

pstmt.executeQuery();

pstmt.executeUpdate();


Dynamische Zusammenstellung einer Anfrage

Ansatz 1: Nutzung von Zeichenkettenfunktionen in der jeweiligen Sprache

Ansatz 2:Verwendung des Oracle-Pakets DBMS_SQL

Anwendungsbeispiel:Dynamische Anzeige von Studentendaten mit vorgegebener Matrikel-Nr. und vom Benutzer wählbarer Spalten


Anwendungsbeispiel


Realisierung mit Zeichenketten-funktionen in PHP

[...]# Checkboxnamen werden in Array spalten[] übergeben <td>Name</td> <td><input type=checkbox name="spalten[]" value="stud_name"></td>[...] <td>Vorname</td> <td><input type=checkbox name="spalten[]„

value="stud_vorname"></td>[...]<?php# Inhalt der Checkboxen in Array gespeichert$spalten = $_POST[’spalten’];

# Matrikelnummer in Variable matrikel gespeichert$matrikel = $_POST[’matrikel’];

# Zusammensetzen des Spaltenstrings als kommaseparierte Zeichenkette$spalten_string = implode(’, ’,$spalten);


Realisierung mit Zeichenketten-funktionen in PHP (Forts.)

# Abfrage zusammenstellen und vorbereiten$sth = $db->prepare("SELECT ".$spalten_string.

" FROM Studenten "."WHERE stud_matrikel =? ");

# Abfrage ausführen mit Belegen des Parameters$res =& $db->execute($sth, $matrikel);# Tabellenkopf ausgeben$anz = count($spalten);for ($i=0; $i<$anz; $i++) { echo "<th class=\"inner\">".$spalten[$i]."</th>";}# Durchlaufen des ResultSetswhile($res->fetchInto($row)){ echo "<tr>\n"; $anz = count($row); # Ergebniszeile durchlaufen for ($i=0; $i<$anz; $i++) { echo "<td class=\"inner\">".$row[$i]."</td>"; } echo "</tr>\n";}


Realisierung mit DBMS_SQL Package in PL/SQL

[...]<input type=hidden name="spalten" value="stud_matrikel"><table border="1" cellpadding="0" cellspacing="0"><tr> <td>Matrikelnummer:</td> <td><INPUT name="matrikel" size="10" maxlength="7" value=""></td></tr><tr> <td>Name</td> <td><input type=checkbox name="spalten"

value="stud_name"></td></tr>[...]

Liste der Checkboxen in HTML


Realisierung mit DBMS_SQL Package in PL/SQL (2)

<%@ plsql procedure="psp_dynamic" %><%@ plsql parameter="matrikel" type="NUMBER" %><%@ plsql parameter="spalten" type="owa_util.ident_arr" %>

Erstellung und Ausführung der dynamischen SQL-Anweisung in PSP Programm

Speichere die Namen aller selektierten Spalten in einer ZeichenketteFOR i IN 1..spalten.count LOOP IF (i=spalten.count) THEN spalten_all := spalten_all || spalten(i); ELSE spalten_all := spalten_all || spalten(i) || ’,’; END IF;END LOOP;



c := dbms_sql.open_cursor;

Öffnen des Cursors für weitere Ausführung der dynam. Anfrage

DBMS_SQL.PARSE(c, ’SELECT ’||spalten_all||’ FROM studentenWHERE stud_matrikel = :matrikel’, dbms_sql.native);

DBMS_SQL.BIND_VARIABLE(c, ’:matrikel’, matrikel);

Parsen der Anfrage und Parameterbindung

Definiere eine Spalte für jede selektierte Spalte im Cursor

FOR i IN 1..spalten.countLOOP DBMS_SQL.DEFINE_COLUMN(c, i, value_string, 30);END LOOP;



DBMS_SQL.execute(c);[...]FOR i IN 1..spalten.count LOOP htp.print(’<th class="inner">’||spalten(i)||’</th>’); END LOOP;

Ausführung der Anfrage – Ausgabe des Tabellenkopfes

Hinweis:Neben DBMS_SQL auch native dynamic SQL in Oracle verfügbar



Lese jede Ergebniszeile mittels (DBMS_SQL.FETCH_ROWS) Pro Zeile: Lese alle Spaltenwerte (Out-Param value_string) LOOP IF DBMS_SQL.FETCH_ROWS(c)>0 THEN htp.print(’<tr>’); FOR i IN 1..spalten.count LOOP DBMS_SQL.COLUMN_VALUE(c, i, value_string); htp.print(’<td class="inner">’||value_string||’</td>’); END LOOP; htp.print(’</tr>’); ELSE exit; END IF;END LOOP;-- Nach Ergebnisausgabe: Cursor schliessenDBMS_SQL.CLOSE_CURSOR(c);


Transaktionsunterstützung

Autocommit-Modus– bedeutet: Jedes SQL-Statement als individuelle

Transaktion behandelt– neue Connections sind im Autocommit-Modus– Ausschalten des Autocommit-Modus

JDBC: conn.setAutoCommit(false)Pear:DB: $db->autoCommit(false)

Transaktionskontrolle ohne Autocommit-Modus– JDBC: conn.commit() conn.rollback()– Pear:DB: $db->commit() $db->rollback()

Documents

Datenbank-Zugriffsschnittstellen. © Prof. T. Kudraß, HTWK Leipzig Überblick Datenbankzugriff mit PHP Datenbankzugriff mit JDBC Datenbankzugriff mit PSP