Facebook
Twitter
Google+
Kommentare
17

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.

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.

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:

Treiber Datei

PHP Version

PHP Compiler

Thread safe

php_sqlsrv_53_nts_vc6.dll

V5.3

Visual C++ 6.0

Nein

php_sqlsrv_53_nts_vc9.dll

V5.3

Visual C++ 9.0

Nein

php_sqlsrv_53_ts_vc6.dll

V5.3

Visual C++ 6.0

Ja

php_sqlsrv_53_ts_vc9.dll

V5.3

Visual C++ 9.0

Ja

php_sqlsrv_52_nts_vc6.dll

V5.2

Visual C++ 6.0

Nein

php_sqlsrv_52_ts_vc6.dll

V5.2

Visual C++ 6.0

Ja

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.

Über den Autor

Tobias Zander

Tobias Zander arbeitet seit 1999 im Bereich der Web-Entwicklung als Programmierer und Berater. Seit 2007 ist er selbstständig als „Gun for hire“ unterwegs und liebt dabei vor allem die Herausforderung der ständig wachsenden Anforderungen und neuen Aufgaben. Dementsprechend ist er ständig auf der Suche nach neuen anspruchsvollen und wegbereitenden Projekten.
Kommentare

17 Comments

  1. Das erklärt also unsere Abstürze des Apache? Die ODBC-Verbindung?!

    Ist ja interessant. Wir suchten schon stundenlang nach dem Fehler und konnten nichts passendes finden…

    Danke für die Alternative! 😀

    Reply
  2. Ich glaube in der Tabelle bei PHP Compiler hat der Copy-Paste Teufel zugeschlagen 😉 Da müsste doch bei den vc9 Zeilen kein Visual C++ 6.0 stehen oder?

    Ansonsten ein sehr interessanter Beitrag. Er hat mir vor allem gezeigt, dass ein DB-Layer welcher nur die DB-Funktionen abstrahiert und natives SQL in der Applikation zulässt keine transparente Migration von MySQL zu MSSQL zulässt. Da ja zum Beispiel kein LIMIT verfügbar ist.

    Reply
  3. Gibt es Erfahrungen wie es ist mit MySQL und MSSQL parallel zu arbeiten? Für ein Projekt muss ich Auftragsdaten aus einer MSSQL Tabelle lesen. Aus diesen werden dann die Adressdaten extrahiert und per Google Maps API um die Geodaten angereichert. Um den Datenbestand im MSSQL unangetastet zu lassen, sollen die Daten anschl. in einer MySQL gespeichert werden.

    Geht das in einem Script?

    Reply
  4. Das geht prinzipiell in einem Skript. Sofern die Datentypen unterschiedlich sind müssen diese natürlich konvertiert machen, aber ansonsten spricht da nichts dagegen und habe ich auch selber schon ein paar mal vorgenommen.

    Reply
  5. Also wenn es von Prinzip her geht, bin ich schonmal froh. Ich habe gestern einige Stunden damit verbracht, eine Verbindung von meinem Windows Rechner aus über VPN auf den MsSQL (2008) zu bekommen. Leider erfolglos. Genauso wenig ging es von den Debian Rechner innerhalb des Firmennetzes. Ich hoffe, dass es dort an Firewall Einstellungen gescheitert ist und nicht an meinen Einstellungen.

    Hast du vielleicht noch nen Ratschlag, welchen Treiber ich am Besten verwenden sollte? Ist unter Linux die FreeTDS Variante zu empfehlen und unter Windows die Extenstion „php_sqlsrv_53_ts_vc9.dll“?

    Kann ich unter Unix die Befehle wie „mssql_connect“ etc. verwenden oder geht das nur unter Windows?

    Ich bin für jeden Tip und Link sehr, sehr dankbar :)

    Reply
  6. Ja unter Windows nutzt du am besten sqlsrv. Welche DLL du genau brauchst kannst du anhand der Tabelle oben ermitteln. Unter Linux habe ich mit Verbindungen zu MSSQL leider kaum Erfahrungen gemacht und kann hier nicht wirklich eine Empfehlung abgeben.
    Die kompletten mssql_ Befehle sind aber nicht mehr wirklich aktuell und sollten nicht eingesetzt werden.

    Reply
  7. Sind jetzt auch soweit doch ne Windows VM dafür ranzuziehen. Das scheint doch wesentlich einfacher zu sein.

    Wenn man die mssql_* Befehle nicht verwenden soll, kann ich stattdessen dieselben Befehle nutzen nur mit „sqlsrv_“ davor? Oder gibts dafür ne separate Beschreibung?

    Reply
  8. Sehr netter Post, vielen Dank dafür!

    Für einige Import- und Auswertungsarbeiten habe ich mich dazu entschlossen, unsere vorhandene MSSQL via PHP auszulesen und ergebnisse in einem kleinen CMS auszuspucken – da fing der ganze Ärger an…

    Ich hab einen Apache Server aufgesetzt mit der PHP 5.5 Version – leider gibt es offiziell von Microsoft nur einen Treiber für maximal php 5.4 … aber ein User war so freundlich und hat den Treiber für 5.5 kompiliert und siehe da – er funktioniert (Win 7 64Bit) – falls ihr den Treiber sucht, schaut mal in den offiziellen Support-Threads von Microsoft zu diesem Treiber.

    Lustig wurden dann die Anfragen an den Server – bisher hatte ich mit MySQL Datenbanken gearbeitet, die Umstellung auf sqlsrv_BEFEHLE war jedoch nicht so umständlich, eigentlich sogar ziemlich easy – PHP hat die Doku ziemlich gut geschrieben, so kann man via Google sehr schnell die Möglichkeiten abgrasen.

    Die Performance
    Überraschenderweise laufen die Abfragen über PHP schneller als die Abfragen mit unserem CRM-System… für den Import von Daten schreibe ich gerade Skripte – sobald ich damit mal durch bin, melde ich mich wieder und teile meine Erfahrungen auch hier mal mit.

    Viele Grüße

    Reply
    • Wie Versprochen der Review zu den Importem

      Ich habe mich dazu entschlossen, csv-Listen in unsere Datenbank einfliessen zu lassen (Dies ist für uns die beste Möglichkeit, um Abteilungsübergreifend Daten in Massen im selben Format zu erfassen).

      Bei der Kodierung muss man ganz stark aufpassen, standartmäßig wird Latin in der MSSQL angelegt, falls ihr also nur mit vorhandener MSSQL arbeiten könnt, empfehle ich die Kodierung in den Connectparametern mit anzugeben, da sonst ziemlich schnell der Überblick verloren geht.

      Im Vergleich zu unserem CRM sind die Importe recht zügig, das dürfte aber an der Aktualität des sqlsrvTreibers liegen.

      Reply

Leave a Comment.

Link erfolgreich vorgeschlagen.

Vielen Dank, dass du einen Link vorgeschlagen hast. Wir werden ihn sobald wie möglich prüfen. Schließen