Bevor wir aber richtig loslegen, liste ich schnell noch die Komponenten auf, mit denen im Laufe des folgenden Artikels gearbeitet wird:
- Windows 2000
- Apache 1.3.8
- DB2/NT 8.1
- PHP 4.3.2
- MS-Visual C 6.0
Alles zusammen auf einem Windows-PC installiert, ist die Testsituation für den folgenden Artikel. Grundkenntnisse zu den jeweiligen Komponenten sind notwendig, um die folgenden Beispiele nachvollziehen zu können. Wie und ob PHP auf einer AS/400 arbeitet und zu installieren ist, können Sie übrigens in [3] nachlesen.
ODBC und der DB2/CLI
PHP nutzt die ODBC-Schnittstelle von DB2 und damit das DB2/CLI-Interface. Voraussetzung für alles folgende ist dementsprechend ein funktionierender DB2-Client auf dem PC oder Server, wo auch PHP installiert ist. Die Installation des DB2-Client soll aber nicht Thema dieses Artikels sein. Wir setzen voraus, dass alles notwendige vorhanden ist, also auch der Zugriff auf die DB2 Beispieldatenbank
SAMPLE. Im Zweifelsfalle wenden Sie sich bitte an den bereits häufiger zitierten DBA Ihres Vertrauens, der Ihnen sicher gerne weiter hilft.
First Contact oder Hello World
Um eine Datenbankverbindung aufzubauen, benötigen wir die ODBC-Extension von PHP. Diese muss in der
PHP.INI nicht extra installiert werden, sondern ist schon per Default aktiviert.
hello_world.php:
<?php
$hDB = odbc_connect('SAMPLE','xx','yy');
if (empty($hDB)) {
echo "Verbindungsaufbau misslungen..";
die();
} else {
echo "Verbindungsaufbau ok!";
odbc_close($hDB);
}
?>
Das Skript
hello_world.php versucht, eine Verbindung via
odbc_connect() zur DB2-Beispieldatenbank
SAMPLE aufzubauen. Ist der zurückgegebene Handle
$hDB leer, dann ist etwas schiefgegangen. Je nach Einstellung in der
php.ini sehen Sie dann den Fehler direkt im Browser oder nur in dem Logfile
error.log von Apache, welches im Unterverzeichnis
logs zu finden ist. Ist der User
XX gültig und das Password
YY korrekt, sollten sie folgende Meldung in Ihrem Browser sehen (Abb. 1).
Abb. 1: Erfolgreicher Verbindungsaufbau zur DB2-Datenbank
Bis hierher sollte es keine Probleme gegeben haben und wirklich neues habe ich Ihnen auch noch nicht verraten. Im nächsten Schritt lesen wir die Versionsnummern von DB2 aus. Ein Blick in den Systemkatalog bringt eine kleine Tabelle namens
SYSIBM.SYSVERSIONS zu tage, deren Inhalt die gesuchte Information liefert. Mit dem kleinen Programm in Listing 1 lassen wir uns das Ganze anzeigen.
Listing 1 GetDB2Version.php: (Ausschnitt)
[..]
/**
* SQL-Abfrage aufbauen und ausführen
*/
echo "<HTML>\n";
echo "<HEAD></HEAD>\n";
echo "<BODY>\n";
echo "<TABLE border='1' >\n";
$sQuery = "SELECT * FROM sysibm.sysversions
FOR READ ONLY";
// Query ausführen
$hResult = odbc_exec($hDB,$sQuery);
if ($hResult) {
// anzahl Spalten ermitteln
$nCol = odbc_num_fields($hResult);
// Tabellen Header aufbauen
echo "<TR>\n";
for ($i=1;$i<=$nCol;$i++) {
$sFieldName = odbc_field_name($hResult,$i);
echo "<TH>".$sFieldName."</TD>";
}
echo "</TR>\n";
// Alle Datenzeilen einlesen
while (odbc_fetch_row($hResult)) {
// Tabellen Datenzeile aufbauen
echo "<TR>\n";
for ($i=1;$i<=$nCol;$i++) {
$sFieldValue = odbc_result($hResult,$i);
echo "<TD>".$sFieldValue."</TD>";
}
echo "</TR>\n";
}
[..]
Nachdem eine Datenbankverbindung analog dem Beispiel
hello_world.php aufgebaut ist, weisen wir der Variablen
$sQuery unsere SQL-Abfrage zu. DB2 soll alle Spalten der internen Versionstabelle liefern und außerdem möchten wir das Ergebnis lesen. Der Zusatz
FOR READ ONLY bewirkt, dass DB2 die Daten lesend zur Verfügung stellt. Ohne
FOR READ ONLY verweigert DB2 die Aussage mit dem Hinweis
SQL0607N "UPDATE" ist für Systemobjekte nicht definiert. Die Tabelle
SYSIBM.SYSVERSIONS ist - wie das vorgestellte
SYS ja schon sagt - eine Systemtabelle bzw. ein Systemobjekt, auf das nur lesend zugegriffen werden kann. Ohne den Vermerk
FOR READ ONLY versucht PHP offensichtlich, eine Satzsperre (je nach eingestelltem Isolationlevel im DB2/CLI) einzurichten. Mit dem Zusatz
FOR READ ONLY funktioniert die Abfrage, und wir erhalten das gewünschte Ergebnis (Abb. 2). Das komplette Listing
GetDB2Version.php liegt natürlich wie immer vollständig auf der Heft-CD bereit.
Abb. 2: Inhalt der DB2-Systemtabelle SYSIBM.SYSVERSIONS
DB2CLI.INI anpassen
Alternativ zur obigen Variante
FOR READ ONLY, lässt sich das geschilderte Problem auch mit einer globalen Einstellung in der
DB2CLI.INI beheben. Bei der Installation muss dazu die Unterstützung (Optimierung) für Microsoft Visual Basic aktiviert werden. Dabei werden einige Parameter in der
DB2CLI.INI gesetzt (Abb. 3). Der wesentliche Parameter ist
PATCH2=6, der den Moveable Cursor deaktiviert.
DB2CLI.INI (..Ausschnitt..):
[SAMPLE]
UID=tw
PATCH2=6
PATCH1=1024
LOBMAXCOLUMNSIZE=1048575
LONGDATACOMPAT=1
Alternativ zur Kommandozeile gibt es auch eine GUI-Oberfläche, um die
DB2CLI.INI zu bearbeiten.
Abb. 3: Einstellung der ODBC-Datenquelle SAMPLE (DB2CLI.INI)
Abb. 3 zeigt die GUI-Oberfläche, um die ODBC-Verbindung einzustellen. Der wesentliche Eintrag in der
DB2CLI.INI ist
PATCH2 mit dem Wert
6. Ist dieser Parameter aktiv, kommt es nicht zum oben genannten Problem
SQL0607N "UPDATE" ist für Systemobjekte nicht definiert.
Cursortyp SQL_CUR_USE_ODBC
Die dritte und vielleicht eleganteste Variante ist der Einsatz der Option
SQL_CUR_USE_ODBC beim Aufbauen der Datenbankverbindung.
/**
* Definition des ODBC Cursortyp
*/
$hDB = odbc_connect('SAMPLE','xx','yy',
SQL_CUR_USE_ODBC);
Anstatt den DB2-Client anzupassen, liefert PHP ebenfalls eine Möglichkeit, das oben genannte Problem mit Bordmitteln zu beheben. Der Vorteil hier ist auf jeden Fall: wir als PHP-Programmierer kommen ohne fremde Hilfe ans Ziel. PHP verfügt über insgesamt vier verschiedene Cursortypen für
odbc_connect(). Bei vielen speziellen Problemen kann mit diesen Parametern für Abhilfe gesorgt werden. Im PHP-Manual und -Handbuch werden folgende Cursortypen näher beschrieben:
- SQL_CUR_USE_IF_NEEDED
- SQL_CUR_USE_ODBC
- SQL_CUR_USE_DRIVER
- SQL_CUR_DEFAULT
Im Zweifelsfalle lohnt sich ein Blick auf die PHP-Homepage [1] und die dortigen Newsgroups.
Stored Procedure - warum ?
Stored Procedures (SP) sind kleine Programme, die in C oder Java geschrieben sind und komplett innerhalb der Datenbank abgelegt (gebunden) sind. SP sind klein und schnell in der Ausführung, da das Prepare - also das Prüfen des SQL-Befehles - nur einmal (während der
CREATE PROCEDURE) erfolgt. SPs ermöglichen es, ganze Teile der Geschäftslogik in der Datenbank abzulegen. Knifflige Routinen müssen nur einmal geschrieben werden und andere Programme können einfach mit einem
CALL gehaltserhoehung_in_prozent (25) darauf zugreifen. Die Themen COM/DCOM und der aktuelle Hype mit den Web Services lässt grüßen. Auch dort wird versucht, komplexe Abläufe hinter allgemein zugänglichen Schnittstellen (APIs) zu verstecken.
Stored Procedure Language SQL
Seit der DB2 Version 7.1 hat IBM auch ein Herz für diejenigen Datenbankentwickler, die zwar perfekt in SQL, aber deren Kenntnisse in C oder JAVA eher ausbaufähig sind. IBM hat als weitere SP-Sprache SQL eingeführt und nennt das ganze nun SQL PL (SQL Procedural Language). Dieses Entgegenkommen wäre sicherlich nicht ohne den kollegialen Druck von Oracle (PL/SQL) und Microsoft (T-SQL) möglich gewesen. Wie dem auch sei, seit der Version 7 kennt DB2 nun SQL als SP-Sprache, und damit sind wir auch beim zentralen Thema angelangt [4].
LOGINLOG.DDL:
CREATE TABLE tw.loginlog (
username CHAR(32) NOT NULL,
message VARCHAR(256),
createtime timestamp NOT NULL
)
Für unser erstes Beispiel legen wir uns schnell die kleine Tabelle
LOGINLOG an. Darin sollen alle Anmeldungen und ein optionaler Nachrichtentext gespeichert werden. Nachdem die Tabelle existiert, könnte es eigentlich mit der SP klappen, wenn da nicht EDV im Spiel wäre ...
CREATE PROCEDURE - Hürden
Vor dem (Programm-)Vergnügen steht meist die Installation, so auch hier, aber zuerst schauen wir uns unsere erste SP mit dem Namen
LOGINLOG_SP genauer an.
LOGINLOG_SP.DDL:
CREATE PROCEDURE loginlog_sp (IN p_sMsg CHAR(32))
SPECIFIC loginlog_sp
LANGUAGE SQL
BEGIN
INSERT INTO tw.loginlog
(username, message, createtime)
VALUES
(USER, p_sMsg, CURRENT TIMESTAMP);
END!
Auffällig ist der
IN-Parameter
p_sMsg. Das Kürzel
IN bedeutet hier: Es kann ein Wert der SP zur weiteren Bearbeitung übergeben werden. Der Parameter soll vom Typ
CHAR und maximal 32 Byte lang sein. Wo es
IN gibt, ist auch
OUT nicht weit. Mit
OUT werden Parameter beschrieben, deren Werte von der SP zurück an das aufrufende Programm gegeben werden. Aber dazu später mehr. Weiterhin gibt es da die Eigenschaft
SPECIFIC. Damit wird der echte eindeutige Name der SP festgelegt, in unserem Fall ist er identisch zu dem in der
CREATE PROCEDURE-Zeile, also
LOGINLOG_SP. Nun kommt
LANGUAGE SQL. Dieses Kommando ist optional. Fehlt es, ergänzt DB2 automatisch zu
LANGUAGE SQL. Zu guter Letzt der Body mit
BEGIN und
END. Weitere Details zu SQL PL und deren Syntax [4] würden hier den Rahmen sprengen. Wichtig ist noch der Hinweis auf ein individuelles Befehlsendezeichen - beispielsweise ! - welches im
DB2 Command Center eingestellt werden muss. Gut, doch nun schauen wir uns die Fehlermeldungen an, die DB2 beim
CREATE PROCEDURE anmerkt.
SQL7032N Die SQL-Prozedur "LOGINLOG_SP" wurde nicht erstellt.
Die Diagnosedatei ist "P0124965.log".
[...]
Wenn wir jetzt die genannte Diagnosedatei
P0124965.log im Verzeichnis
G:\SQLLIB\function\routine\sqlproc\SAMPLE\TW\tmp suchen, wird uns schnell klar, dass während dem
CREATE PROCEDURE ein C-Compiler aufgerufen wird, um die notwendigen Dateien für das SP zu erzeugen. Unsere schöne SQL-Procedure wird intern automatisch in C-Code umgewandelt und an die Datenbank gebunden. Damit DB2 aber den Compiler richtig nutzen kann, muss die DB2-Umgebungsvariable
DB2_SQLROUTINE_COMPILER_PATH korrekt gesetzt sein.
DB2_SQLROUTINE_COMPILER_PATH
DB2-Umgebungsvariablen werden am besten mit dem Programm
DB2SET.EXE auf Kommandoebene (MS-DOS-Eingabeaufforderung) gesetzt (Abb. 4). Eine DB2-Umgebungsvariable hat nichts mit den sonstigen Windowsvariablen der Art
Environment zu tun, sondern wird extra in der Registry verwaltet.
Abb. 4: DB2-Umgebungsvariable DB2_SQLROUTINE_COMPILER_PATH setzen
Der Umgebungsvariablen
DB2_SQLROUTINE_COMPILER_PATH wird der Pfad zur Datei
vcvars32.bat zugewiesen. In diesem Batchjob werden die notwendigen Compilervariablen für den Microsoft C/C++ Compiler gesetzt. Nun gibt es noch zwei kleine Hürden beim Setzen dieser Variablen. Erstens darf kein Leerzeichen links oder rechts vom Gleichheitszeichen sein und zweitens muss der Pfadname in doppelten Anführungszeichen stehen, da er Leerzeichen enthält.
Jetzt aber...
Wenn wir nun versuchen unsere SQL-Procedure
LOGINLOG_SP zu erzeugen, meldet sich DB2 - nach einigem Gerumpel auf der Platte - netterweise mit
DB20000I Der Befehl SQL wurde erfolgreich ausgeführt. Das bedeutet, wir können unsere SP jetzt ausprobieren. Damit andere Benutzer das neue Paket auch nutzen können, müssen noch die
EXECUTE-Rechte auf dem Paket vergeben werden. Bleibt noch das Problem, auf welchen Namen das neue Paket denn eigentlich hört. Denn einfach nur
GRANT EXECUTE ON PACKAGE tw.loginlog_sp TO PUBLIC
klappt nicht, da DB2 den Paketnamen
LOGINLOG_SP nicht kennt. Ein Blick in das Verzeichnis, in dem DB2 die erzeugten Pakete ablegt, bringt endlich mehr Klarheit.
Abb. 5: Verzeichnis für die fertige SP LOGINLOG_SP alias P3022352
Abbildung 5 zeigt, wo und unter welchem internen Namen DB2 die fertigen Pakete (Stored Procedures) speichert. Hinter der Nummer
P3022352 verbirgt sich unsere Procedure
LOGINLOG_SP. Die Rechtevergabe muss auf diesem Paketnamen sowie die SP
LOGINLOG_SP erfolgen, die das Paket nutzt.
GRANT EXECUTE ON PACKAGE TW.P3022352 TO PUBLIC
GRANT EXECUTE ON PROCEDURE TW.LOGINLOG_SP(CHAR()) TO PUBLIC
Geschafft! Nun können wir unsere SP erst einmal im DB2 Command Center ausprobieren.
DB2 COMMAND CENTER:
CALL tw.loginlog_sp('Es klappt...')
-> Rückgabestatus = 0
Anschließend schauen wir nach, ob sich in der Tabelle der gewünschte Eintrag befindet.
DB2 COMMAND CENTER:
SELECT * FROM tw.loginlog
USERNAME MESSAGE CREATETIME
-------- ------------- ---------------------
TW Es klappt... 2004-03-20-13.03.16.987000
1 Satz/Sätze ausgewählt.
Damit ist das erste Level geschafft. Die SP existiert und kann angesprochen werden. Im nächsten Schritt versuchen wir das mit PHP nachzubauen.
CallDB2SP.php: (Ausschnitt)
/***
* DB2 Stored Procedure aufrufen
*/
$sQuery = "CALL tw.loginlog_sp('PHP kann es auch...')";
// Befehl ausführen
odbc_exec($hDB,$sQuery);
odbc_commit($hDB);
[...]
Der wesentliche Teil im Skript
CallDB2SP.php ist hier zu sehen. Analog einer normalen Abfrage wird ein
CALL-Befehl für den Aufruf der SP eingesetzt. Als Parameter wird der Text
PHP kann es auch übergeben. Anschließend wird der aktuelle Inhalt der Tabelle
tw.loginlog als HTML-Tabelle ausgegeben (Abb. 6).
Abb. 6: Inhalt der Tabelle LOGINLOG nach dem Ausführen von CallDB2SP.php
Das PHP-Skript nutzt als Username
DBU (Database User). Den User
DBU habe ich für meine PHP-Web-Anwendungen eingerichtet und ihm aus Sicherheitsgründen nur die notwendigen Zugriffsrechte gegeben. Und zwar
EXECUTE auf
tw.loginlog_sp sowie
SELECT plus
INSERT auf die Tabelle
tw.loginlog. Je nach Konzept benötigt man hier natürlich eine sichere Loginroutine, mit der sich der Nutzer zweifelsfrei identifizieren muss. Nun gut, bisher haben wir gesehen, dass PHP erfolgreich Werte an eine DB2-Stored Procedure übergeben kann. Aber wie sieht es eigentlich mit Rückgabewerten aus?
One way street ?
In den Newsgroups kann man immer wieder mal die Frage lesen, wie man denn an die Rückgabewerte einer Stored Procedure käme? Lösungen findet man eher selten, deshalb probieren wir es einfach selber aus:
LOGINLOG_COUNT_SP.DDL:
CREATE PROCEDURE loginlog_count_sp
(OUT p_nCount INT)
SPECIFIC loginlog_count_sp
LANGUAGE SQL
BEGIN
SELECT COUNT (*) INTO p_nCount
FROM tw.loginlog;
END!
Unsere neue Procedure
loginlog_count_sp soll die Anzahl der Datensätze in der Tabelle
loginlog zählen und das Ergebnis in Form eines Integer-Wertes zurück geben. An sich ist dies kein großes Problem und in der SQL-Console bzw. auf Kommandoebene klappt es auch widerspruchslos.
CALL tw.loginlog_count_sp(?)
Wert der Ausgabeparameter
-------------------------
Parametername: P_NCOUNTParameterwert: 2
Rückgabestatus = 0
Nun gut, dann bauen wir das mal schnell mit PHP nach. Dieser Meinung war ich zuerst auch und mein erster Versuch sah dann wie in Listing 2 aus.
Listing 2 CallDB2CountSP.php: (Ausschnitt)
[...]
/***
* DB2 Stored Procedure aufrufen
*/
$sQuery = "CALL tw.loginlog_count_sp('?')";
// Prepare/Execute wegen HOST Variablen (?)
$stmt = odbc_prepare($hDB,$sQuery);
if ($stmt) {
$nCount = 0;
$aParam = array();
$aParam['count'] = 0;
if (odbc_execute($stmt,$aParam)) { // Line 33
// Alle Datenzeilen einlesen
while (odbc_fetch_row($stmt)) {
[...]
Warning: SQL error: [IBM][CLI Driver][DB2/NT] SQL0469N Der Parametermodus (IN, OUT oder INOUT) ist für einen Parameter in der Prozedur "LOGINLOG_COUNT_SP" mit dem spezifischen Namen "LOGINLOG_COUNT_SP" (Parameternummer "0", Name "P_NCOUNT") ungültig. SQLSTATE=42886 , SQL state 42886 in SQLExecute in ..\calldb2countsp.php on line 33
Doch leider funktioniert es nicht, und ich habe keinen besseren Ansatz gefunden. Die hier gezeigte Technik mit
odbc_prepare() und
odbc_execute() arbeitet zwar mit normalen DML-Befehlen wie
SELECT,
INSERT,
UPDATE oder
DELETE, aber offensichtlich nicht im Zusammenspiel mit Stored Procedures. Auch der passende Hinweis auf den Developer-Seiten von IBM [5] liefert wenig Tröstliches.
| Offizielle IBM-Information zum Problem mit PHP und Output-Parametern [5] | | Problem Cannot call stored procedures with output parameters via PHP and ODBC because DB2 does not currently have a PHP driver. The PHP libraries are not supported. Cause Because DB2 does not have a PHP driver, the PHP libraries are not supported. Solution One possible workaround is to change the way the stored procedure returns data to allow PHP to retrieve the information. |
|
Flinte ins Korn werfen?
Was tun? Warten bis IBM ihren DB2/ODBC-Treiber verbessert oder sich einen Workaround überlegen. Zweites entspricht doch eher unserem PHP-Magazin-Image. Denn SQL PL bietet eine
RESULT SETS-Eigenschaft, die wir uns zu Nutzen machen können [6].
GETRESULTSET_SP.DDL:
CREATE PROCEDURE GetResultSet()
SPECIFIC GetResultSet
RESULT SETS 1
LANGUAGE SQL
BEGIN
-- definiere cursor mit Return
DECLARE c1 CURSOR WITH RETURN FOR
SELECT count (*) as Anzahl FROM tw.loginlog;
-- stellt ein Result-Set zur Verfügung
OPEN c1;
END!
Neu in dieser SP ist die Eigenschaft
RESULT SETS 1. Damit wird definiert: Diese Procedure soll einen (!) Resultset-Cursor zurück geben. Die spezielle Benennung
SETS 1 lässt vermuten, dass hier mehrere Ergebnismengen gleichzeitig zurück gegeben werden können. Und richtig, ein Blick in [4] zeigt, wie dies geht. Zur Zeit genügt uns aber ein Resultset.
CURSOR WITH RETURN bestimmt den Cursor, der ausgeführt werden soll. In unserem Fall handelt es sich um statisches SQL, da keine variablen Teile wie Parameter oder
HOST-Variablen im Einsatz sind. Mit
OPEN C1 wird schlussendlich der eigentliche Cursor
C1 für die Clientapplikation (unser PHP-Programm !) geöffnet. Nachdem wir diese Procedure erzeugt haben, dürfen wir nicht vergessen, wieder die notwendigen
EXECUTE-Rechte für den Web-User
DBU auf dieses Paket zu erteilen. Und immer wenn es funktioniert, sieht das Ergebnis genial einfach aus (Abb. 7).
Abb. 7: GetResultSet.PHP präsentiert die Rückgabewerte der SQL PL
Was haben wir erreicht? Abbildung 7 zeigt Daten an, die von einer DB2-SP erzeugt worden sind, zwar nicht direkt über die
OUT-Parameter, aber dafür mittels dem übergebenen Resultset. Eventuell ist diese Lösung sogar flexibler als der Einsatz von
OUT-Parametern.
GetResultSet.php: (Ausschnitt)
[...]
/***
* DB2 Stored Procedure aufrufen
*/
$sQuery = "CALL tw.GetResultSet()";
// CALL ausführen
$hResult = odbc_exec($hDB,$sQuery);
if ($hResult) {
// anzahl Spalten ermitteln
$nCol = odbc_num_fields($hResult);
[...]
Aus Sicht des PHP-Skriptes
GetResultSet.php verhält sich die SP-Abfrage wie eine normale SQL-Abfrage. Der gesamte Ablauf sieht letztlich genau so aus, wie das eingangs gezeigte Skript
GetDB2Version.php. Die Möglichkeit, einen Cursor aus der SQL-Procedure zurück an das aufrufende Clientprogramm zu übergeben, hat auch für PHP die DB2-Tür ganz weit aufgestoßen.
Quintessenz
Die hier gezeigten SQL PL-Beispiele sind zwar recht einfach gehalten, zeigen aber doch einen gangbaren Weg auf, damit PHP auch in dieser Liga mitspielen kann. Die Parameterübergabe ist möglich. Zudem haben wir die Tür genügend weit aufgestoßen, um die dahinter liegenden Möglichkeiten mit dem DB2-Feature SQL PL zu erkennen und schätzen zu lernen. Ab der kommenden DB2 Version 9 wird die etwas hakelige Geschichte mit dem C-Compiler abgelöst werden, dann soll direkt ein entsprechender Bytecode erzeugt werden. Lassen wir uns einfach überraschen.
Thomas Wiedmann ist Anwendungs- und Datenbankentwickler für individuelle Softwarelösungen, Autor des Buches DB2 sowie IBM Certified DB2 Administrator. Wenn er nicht gerade Musik hört, liest oder fotografiert, beschäftigt er sich mit Datenbanken und Web-Applikationen. Sie können seine Webseite unter http://www.twiedmann.de einsehen. Literatur und Links
Hat Ihnen dieser Artikel gefallen? Dann abonnieren Sie das PHP Magazin direkt über unser
