PHP und Microsoft SQL Server

So heute geht es um das noch ungewöhnliche Paar PHP und Microsoft SQL Server. Zu diesem Thema könnte man ganze Bücher füllen, deswegen kann das Thema hier höchstens angerissen werden, die jeweils verfügbaren Links sollten euch aber bei Bedarf weiterhelfen.

Als jahrelanger begeisterter MySQL User war ich zunächst skeptisch als in einem neuen Projekt MSSQL als gegebene Datenbank genutzt werden musste.

Mit diesem Artikel möchte ich zunächst Umsteigern etwas die Ängste nehmen, zum anderen aber vor typischen Fallstricken warnen.

Treiberwahl

Prinzipiell gibt es verschiedene Möglichkeiten um sich mit einem MSSQL Server zu verbinden, allerdings stelle ich ein paar Mindestvoraussetzungen die erfüllt sein müssen, um ein Projekt auch zukunftssicher zu entwickeln:

• Aktiver Support für den Treiber
• UTF-8/Unicode Support
• Stabilität

Sind diese Mindestkriterien erfüllt entscheiden danach folgende Gesichtspunkte:

• Performance unter hoher Last
• Ressourcenbedarf beim Webserver

ODBC

Durch einige Erfahrung mit ODBC und Visual Basic war mein erster Ansatz hierüber eine Verbindung mit dem Server herzustellen. Technisch funktioniert dies mit den nativen PHP ODBC Treibern auch problemlos, wie aber von mir zuvor schon befürchtet ist die Performance nicht zufriedenstellend. Viel schlimmer ist aber noch das Problem, dass wir bei Stresstests ständige Abstürze des Apache Servers verzeichneten, was dieser Lösung bereits zu Beginn sämtliche Chancen nahm.

[sc:adsense]

Nativer MSSQL Treiber

Auf der Suche nach einer neuen Verbindungsmöglichkeit war mein nächster Schritt natürlich der Aufruf von php.net/mssql. Hier wird auch ein entsprechender Treiber angeboten, allerdings bereits in der Einleitung darauf hingewiesen, dass es von Microsoft selbst eine Alternative gibt, was den Schluss nahe legt, dass hier wohl keine weiteren Updates zu erwarten sind. Für ein neu startendes Projekt ein No-Go.

Microsoft Sqlsrv Library

Der Microsoft SQL Server Driver for PHP ist schlussendlich also der Ankerhaken.

Einziger Wermutstropfen ist der fehlende Support für *nix Maschinen. Da sowohl das Entwicklungs– als auch Produktivsystem auf Windows laufen, kann im Gegenzug zu den anderen Vorteilen aber darüber hinweg sehen werden.

Leider scheint die Chance für eine *nix Umsetzung eher schwach. Allerdings dürfte die Kombination eines heterogenen Servernetzes in dem Ausmaß auch eher selten sein.

Die Dokumentation ist aktuell und im offiziellen Blog kann man die Entwicklung sehr gut mitverfolgen. Im Forum bekommt man konstruktive Antworten und das Feedback der User wird offen von den Microsoft Entwicklern aufgenommen.

Der eingesetzte Webserver ist hierbei egal, da sich der Treiber direkt in PHP einklinkt. Sowohl mit Apache als auch mit IIS ist die Performance bestens und auch bei hohen Lastzahlen erhöht sich zwar die Antwortzeit, aber der Server läuft stabil.

[sc:adsense]

Installation

In der aktuellen V1.1 ist die Installation leider nicht ganz so trivial, da statt einer DLL gleich 6 Stück angeboten werden. Folgende Tabelle soll dabei helfen die richtige DLL-Datei für sein System zu finden:

Neben seiner PHP Version (was noch recht einfach sein dürfte) muss man also auch wissen, ob man die Thread-sichere Variante nutzt und mit welchem Compiler die PHP Binärdateien kompiliert wurden.

Im Zweifelsfall hilft hier aber auch ausprobieren weiter, sofern man dies nicht im Produktivsystem macht. Unbedingt dabei das error.log beim neu starten von PHP beachten.

Hat man die DLL also gefunden kopiert man diese am besten in das PHP extension Verzeichnis und fügt folgende Zeile in die php.ini hinzu:

extension=php_sqlsrv_53_nts_vc6.dll

PHP nach php.ini Änderungen neu starten nicht vergessen.

Alternativ kann der Treiber auch mit folgenden Code direkt im Skript dynamisch geladen werden:

$conn = sqlsrv_connect(
          $serverName,
          array(
            'UID' => $username,
            'PWD' => $password
          )
        );

Als Rückgabewert erhalten wir eine PHP-Verbindungsressource. Im Fehlerfall ein boolsches false.

Ein aufgetretener Fehler kann in diesem Fall (und auch in allen anderen: sqlsrv_query …) folgendermaßen abgefragt werden:

$errors = sqlsrv_errors();

Dies liefert detaillierte Informationen zu dem zuletzt aufgetretenen Fehler zurück.

In dem 2. Parameter können auch noch viele weitere Einstellungen wie Timeouts, Transaktionsebene, Failover, Verschlüsselung, ConnectionPooling usw. eingestellt werden.

Der in der Regel wichtigste Parameter dürfte hier das CharacterSet sein. Wer ein internationales Projekt plant und mit Unicode Strings in MSSQL arbeiten möchte sollte hier unbedingt den Wert ‘UTF-8′ übergeben.

Hat alles geklappt, kann eine Query dann simpler weise mit folgender Zeile ausgeführt werden:

$res = sqlsrv_query( $conn, 'SELECT myColumn FROM myTable' );

Um Variablen zu übergeben empfiehlt es sich diese als dritten Parameter anzuhängen:

$res = sqlsrv_query(
         $conn,
         'SELECT myColumn FROM myTable WHERE id = ?',
         array(
           $id
         )
);

Dies verhindert nicht nur mögliche SQL Injections, sondern hilft auch dem SQL Analyzer ähnliche Queries nicht mehrfach planen zu müssen.

Zum Lesen der Daten gibt es wie von anderen Datenbanken gewohnt verschiedene Funktionen, von denen folgende 2 Varianten zu empfehlen sind:

sqlsrv_fetch_array($res);

Ruft die nächste Datenzeile als Array ab. Als zweiter Parameter kann hier optional angegeben werden, ob das Array numerisch, assoziativ oder beide Varianten enthalten soll.

sqlsrv_fetch_object($res);

Wie der Name erahnen lässt kann man hiermit ein PHP-Objekt aus einer Zeile erzeugen. Dies bietet sich u.a. für Active Record Pattern Umsetzungen an, da als zweiter Parameter direkt der Klassenname übergeben werden kann und so nicht setter Methoden noch mal manuell aufgerufen werden müssten.

Ein Verhalten welches häufig zur Verwunderung führt ist zudem, dass der Treiber Datums und Zeitangaben standardmäßig als Objekt zurück liefert. Dies ist zunächst etwas ungewohnt, wenn man sich aber erst einmal daran gewöhnt hat, und die Applikation damit arbeitet, aber auch ein riesenVorteil, da man das Datum direkt in dem Format ausgeben kann wie man es braucht, statt erst wiederum das ganze mühsam in einen Timestamp, d.m.Y, o.ä. zu wandeln oder evtl. sogar Applikations-Logik (z.B. DATE_FORMAT) in die Datenbank packen zu müssen.

Dennoch gibt es seit V1.1 des Treibers die Möglichkeit dieses Verhalten mit der Option ReturnDatesAsStrings im Connect Aufruf zu deaktivieren. Ein true als übergebener Wert bewirkt hierbei die Deaktivierung. False (Default) liefert ein DateTime Objekt zurück.

Besonderheiten von MSSQL

Datentypen NVARCHAR und NCHAR

Neben den üblichen CHAR und VARCHAR Datentypen gibt es in MSSQL die Varianten mit vorangestelltem großen N. Der Unterschied ist die Unicode Unterstützung die bei den neuen Alternativen gegeben ist. Für ein internationales Projekt ist also prinzipiell zu empfehlen immer NVARCHAR statt VARCHAR und NCHAR statt CHAR einzusetzen.

NTEXT ist dagegen obsolete und wird in einer der nächsten SQL Server Versionen nicht mehr existieren. Als Alternative kann NVARCHAR(MAX) genutzt werden, welches die selbe Speichergröße wie ein NTEXT Feld belegen kann.

Wer zudem plant eine Quote Funktion zu schreiben (es gibt leider kein mysql_real_escape_string äquivalent), statt die Parameter getrennt zu übergeben, muss beachten dass auch bei Strings das große N vorangestellt werden muss, da der Server die Strings sonst nicht als Unicode erkennt.

Das ganze sieht also dann folgendermaßen aus:

SELECT myColumn FROM myTable WHERE name LIKE N'unicode string';

LIMIT

Das bekannte LIMIT gibt es in MSSQL leider nicht. Dafür die Alternative TOP. Um sich die ersten Zeilen eines Results abzuholen setzt man dies vor die Spaltendefinition.

SELECT TOP 10 myColumn FROM myTable;

Etwas schwerer wird es, wenn man ein Offset haben möchte, da dies von TOP in der Art nicht unterstützt wird muss man hier leider mit Subselects arbeiten:

SELECT TOP 10 myColum FROM (SELECT TOP 20 FROM myTable ORDER BY myColumn DESC) ORDER BY myColumn ASC;

Hierbei ist darauf zu achten, dass die Sortierung im ersten Select zum Subselect negiert werden muss, um auch wirklich an die Menge ab dem Offset zu kommen.

GROUP BY

Keine Angst, GROUP BY ist vorhanden, wer allerdings dies wie ich gerne missbraucht hat um nicht nur zu gruppieren, sondern auch einen Wert einer beliebigen Zeile in der Gruppe auszugeben wird in die Röhre schauen.

Folgende Query liefert nämlich leider einen Fehler:

SELECT myColumn1, myColumn2 FROM myTable GROUP BY myColum1:

Prinzipiell müssen nämlich alle Spalten, die zurückgeliefert werden auch in der GROUP BY Klausel vorkommen. Aggregarfunktionen (COUNT, SUM …) bilden hier natürlich eine Ausnahme.

Je nach Anwendungsfall kann dies am besten über Counter oder auch wiederum Subselects gelöst werden.

Zend_Db_Adapter_Sqlsrv

Von Zend wird bereits schon ein fertig entwickelter Adapter für den hier besprochenen Sqlsrv Treiber bereitgestellt.

Der Aufbau einer Verbindung erfolgt dabei wie üblich:

Zend_Db::factory(
              'Sqlsrv',
              array(
                            'username' => $username,
                            'password' => $password
              )
);

Neben Username und Password empfiehlt es sich auch den Key ‘driver_options’ zu übergeben und hier die bereits vorhin angesprochenen CharacterSet und bei Bedarf ReturnDatesAsStrings zu übergeben. Natürlich kann auch jede andere Treiberoption, die von Microsoft bereitgestellt wird übergeben werden.

Beachtet werden muss hier allerdings, dass es offensichtlich noch keinen Support für die angesprochenen Unicode Strings gibt. So können die Spalten zwar gelesen werden, beim Schreiben ergibt sich allerdings das Problem, dass Zend intern des öfteren statt einer direkten Parameter-Übergabe die eigene _quote() Methode nutzt, die den String leider nicht als Unicode String quotet.

Neben einem Bug zu diesem Problem habe ich auch einen Patch bereitgestellt, der aktuell allerdings noch nicht offiziell akzeptiert wurde.

Statt eines normalen Quotes wird im Patch entsprechend das große N vorangestellt. Unicode Strings können hierbei auch in nicht Unicode Spalten zum Abgleich oder Speicherung genutzt werden. Microsoft SQL Server erkennt dies und castet automatisch in das richtige Format.

Fazit

Mit dem Microsoft SQL Server Driver for PHP ist eine guter Treiber auf dem Markt, der nicht nur sehr stabil und performant läuft, sondern sich mit der API auch gut an die vorhandenen Varianten von MySQL und Konsorten angliedert.

Nachdem man sich mit den Eigenheiten von MSSQL vertraut gemacht hat lernt man auch sehr schnell die zusätzlichen Features (auf die hier noch gar nicht eingegangen wurde) zu lieben. Insgesamt stellt MSSQL für mich eine vollwertige Datenbanklösung dar, die nun auch mit PHP sehr gut harmoniert und bei neuen Projekten genauso als mögliche Lösung herangezogen werden kann wie MySQL.