Zugriff auf Metadaten

(1)

(2)

Ü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

(3)

Kommunikation mit der Datenbank

Vorbereiten einer SQL-Anweisung

Freigabe der Ressourcen Verbindungsaufbau

Lesen der Ergebnismenge

Verbindungsabbau

Ausführung einer Anweisung

(4)

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)

(5)

Verbindungsaufbau



Pear:DB:

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

– Data Source Name (DSN)

DBMS + Benutzerdaten + Host

phptype://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“);

(6)

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 “.

“ON Kurs_Leiter_ID_FK = Kl_ID ORDER BY Kurs_Name“);

(7)

Ausführung einer SQL-Anweisung



Pear:DB:

–

Übergabe einer Zeichenkette an query-Funktion

$res=& $db->query(

“SELECT Kurse.*, Kl_Name, Kl_Email “.

“ON Kurs_Leiter_ID_FK = Kl_ID “.

“ORDER BY Kurs_Name“);

$res=& $db->query(

“SELECT Kurse.*, Kl_Name, Kl_Email “.

“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, $kurs_id);

# Übergabe mehrerer Parameter als Array

$werte = array($kurs_id, $Kl_Name);

(8)

(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

(9)

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“;

[. . .]

}

(10)

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";

$feld["KURS_WOCHENTAG"].

"</td>\n";

...

echo "</tr>\n";

}

while(OCIFetchInto($stmt, &$feld, OCI_ASSOC)){

echo "<tr>\n";

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

$feld["KURS_WOCHENTAG"].

"</td>\n";

...

echo "</tr>\n";

}

(11)

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 ocifreestatement

int ocifreestatement(int statement)

–

Verbindungsabbau durch Methode ocilogoff

int ocilogoff(int connhandle)

(12)

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 einen Oracle-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

(13)

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

(14)

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

(15)

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";

(16)

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);

?>

(17)

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 Procedure

3.

Aufruf der PSP im Browser

(18)

Standard-Skripttag

 Funktion: Begrenzung von PL/SQL- Anweisungen

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

Prozeduraufrufe

<% PL/SQL code %>

Syntax

(19)

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

(20)

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“ %>

Syntax

<% plsql parameter=“parameter name“ %>

[type=“PL/SQL datatype“]

[default=“value“] %>

<% plsql parameter=“parameter name“ %>

[type=“PL/SQL datatype“]

[default=“value“] %>

(21)

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‘; %>

(22)

Expression Block / Print Direktive



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



Beispiel:

<%= PL/SQL expression %>

Syntax

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

oder

The employee name is <%= emp_rec.ename %>

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

oder

The employee name is <%= emp_rec.ename %>

(23)

Include Direktive



Funktion: Einbinden des Quelltexts anderer Dateien in die Seite



Beispiel:

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

Syntax

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



Anmerkungen:

-

Datei darf HTML- und PSP-Skriptelemente enthalten

-

Einbindung nur einmal zur Übersetzungszeit (mögliche

Alternative: Einbindung durch Prozeduraufruf)

(24)

Kommentare



Funktion: erscheint nicht in der generierten Prozedur bzw.

in den daraus erstellten HTML-Seiten

<%-- Kommentar --%>

Syntax

<%!-- 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

(25)

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



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

(26)

GET-Methode

 Beispiel:

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“>

</form>

...

(27)

POST-Methode

 Beispiel:

cust_order_items

URL bei Submit:

...

<form method=“POST“ action=“cust_order_items“>

</form>

...

<form method=“POST“ action=“cust_order_items“>

</form>

...

(28)

<%@ 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_ID

2. 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>

(29)

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 THEN htp.init;

error; END; -- error: Name der Fehlerseite

(30)

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 Datum END 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; %>

...

(31)

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%>

(32)

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||’/’

<% 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||’/’

<% ELSE %>

<%=’<td class="inner3">’ ||ds.Kurs_AktTeiln||’/’

<% END IF; %>

</tr>

<% END LOOP;%>

</table>

(33)

Ausgabe der PSP-Beispielseite

(34)

JDBC

Query

Close Connect

Process results Datenbankverbindung

herstellen

Datenbankanfrage

Verbindung zur DB schließen

Ergebnisse verarbeiten

(35)

Phase 1: Connect

Query

Process results

Driver registrieren

Verbindung zur Datenbank

(36)

JDBC Driver

 Ist ein Interpreter, der JDBC

Methodenaufrufe in herstellerspezifische Datenbank-Kommandos übersetzt.

Driver JDBC Calls

JDBC Calls

Datenbank Datenbank Kommandos Kommandos

Datenbank

 Implementiert Interfaces in java.sql

 Kann auch herstellerspezifische

Erweiterungen zum JDBC-Standard

bereitstellen.

(37)

O9 oder O10

Thin Client Driver

 Komplett in Java geschrieben, Typ 4 Driver

 Verwendet das Oracle Net Service Protokoll

 Muß von Applets genutzt werden.

Applet JDBC

Client

Client ServerServer

Thin driver

(38)

OCI Client Driver

 Geschrieben in C and Java, Typ 2 Driver

 Oracle Client Software muß installiert sein.

Client

Client ServerServer

O9 oder O10 Application

JDBC OCI driver

ocixxx.dll

(39)

Server-Seitiger Driver

 Läuft innerhalb der Datenbank

 Muß von Java Stored Procedures genutzt werden.

Oracle8 Oracle8ii

Stored Procedure JDBC

Server-side Driver C Library

SQL Engine

(40)

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

(41)

JDBC URLs

JDBC verwendet URL, um Datenbankverbindung zu identifizieren.

jdbc:<subprotocol>:<subname>

Protocol

Protocol DatabaseDatabase

Identifier Identifier

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

Subprotocol Subprotocol

(42)

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>

(43)

Aufbau einer Connection

1. Registriere Driver.

DriverManager.registerDriver (new

oracle.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.

(44)

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.

(45)

Phase 2: Query

Query Erzeuge ein Statement

Process results

Abfrage auf Datenbank

(46)

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

(47)

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);

(48)

Datenbank-Abfragen: Beispiele

 Ausführen eines SELECT Statement.

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.

int rowcount = stmt.executeUpdate

(" DELETE FROM Studenten " +

" WHERE Stud_Matrikel=1234567");

(49)

Ergebnisse

Close Query

Durchlaufen der Ergebnisse

Process Results

Zuweisen der Ergebnisse an Java- Variablen

Connect

(50)

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.

(51)

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.

(52)

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.

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

if (rset.wasNull()) {

… // Handle null value }

…}

(53)

Phase 4: Close

Connect

Query

Process Results

Close

Schließe Result Set

Schließe Statement

Schließe Connection

(54)

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)

(55)

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

(56)

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);

(57)

(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>";

}

(58)

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;

%>

(59)

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 verwendet

dbms_sql.parse(cur,’SELECT * FROM studenten’,DBMS_SQL.native);

-- Ausführen der Anfrage i := 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) := ’’,

(60)

Set in PL/SQL (Forts.)

Ausgabe der Metadaten

-- hole ersten Datensatz rec := 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;

(61)

(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



Beispiel

DatabaseMetaData md = conn.getMetaData();

ResultSet rset = md.getTables(null,"THOMAS",null, null);

out.println("Schema: "+rset.getString("TABLE_SCHEM")+", ");

out.println("Tabellenname: "+

rset.getString("TABLE_NAME")+"<br>");

}

Schema: THOMAS, Tabellenname: KURSE

Schema: THOMAS, Tabellenname: KURSLEITER

(62)

(ResultSet)



Klasse

ResultSetMeta

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

– Anzahl Spalten

– Spaltenname und Datentyp

– Eigenschaften wie Zulässigkeit von Nullwerten



Beispiel

Statement 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>");

}

(63)

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)

(64)

Platzhalter in der WHERE-Klausel

 PHP: Vorbereiten der Anfrage mit Parametern

sth = $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");

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



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

(65)

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.

(66)

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=?");

(67)

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();

(68)

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

(69)

Anwendungsbeispiel

(70)

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);

(71)

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 ResultSets while($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";

}

(72)

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

(73)

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 Zeichenkette

FOR 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;

(74)

Package in PL/SQL (3)

c := dbms_sql.open_cursor;

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

DBMS_SQL.PARSE(c, ’SELECT ’||spalten_all||’ FROM studenten WHERE 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

DBMS_SQL.DEFINE_COLUMN(c, i, value_string, 30);

END LOOP;

(75)

Package in PL/SQL (4)

DBMS_SQL.execute(c);

[...]

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

(76)

Package in PL/SQL (5)

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>’);

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 schliessen DBMS_SQL.CLOSE_CURSOR(c);

(77)

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()

–