Connecting to MS SQL from PHP on IIS using PEAR MDB2

Sorry to have to ask this here; php.net just seems to be full of exceptions and excuses regarding this.

I'm running IIS 6.0 with PHP 5.3.6. I've got MDB2 installed and working (even with a custom-writte开发者_运维百科n driver for an off-brand RDBMS). That's all working great. But now I need to have PHP connect to a bit of standard technology: MS SQL.

The problem is the mssql driver for MDB2 requires PHP to have been compiled with special flags. Hard to do with php binaries :).

I could go down the road of getting a compiler, downloading the source and recompiling, but I'm just starting to wonder if I'm out in the weeds when there's actually a better, more standard way of getting the job done.

So, my question is: For IIS 6 + PHP 5.3.6, is there a different, easier, more commonly-tread route to connecting to MS SQL?

As you've rightly pointed out, support for the community mssql driver is not compiled into the latest Windows PHP binaries.

Presently the current stable release of MDB2 (2.4.1) does not support the official Microsoft sqlsrv native driver.

However if you're willing to live on the edge a bit with the beta release of MDB2 then all is not lost. There is a Microsoft native sqlsrv driver that is part of the 2.5.0b3 release:

http://pear.php.net/package/MDB2_Driver_sqlsrv

First of all ensure that you've installed the Microsoft native driver as I described in my answer here:

Connection between MSSQL and PHP 5.3.5 on IIS is not working

Then to install the PEAR MDB2 sqlsrv driver do the following:

1. If you've already installed MDB2 then uninstall it:

   pear uninstall mdb2

   If you already have any drivers installed then you'll need to uninstall these first:

   pear uninstall mdb2#mysql

2. Next tell PEAR that you want to allow non-stable beta packages:

   pear config-set preferred_state beta

3. Install MDB2_Driver_sqlsrv

   pear install MDB2_Driver_sqlsrv

   This will install the latest MDB2 beta and the MS sqlsrv driver:

   downloading MDB2_Driver_sqlsrv-1.5.0b3.tgz ...
   Starting to download MDB2_Driver_sqlsrv-1.5.0b3.tgz (29,468 bytes)
   .........done: 29,468 bytes
   downloading MDB2-2.5.0b3.tgz ...
   Starting to download MDB2-2.5.0b3.tgz (130,865 bytes)
   ...done: 130,865 bytes
   install ok: channel://pear.php.net/MDB2_Driver_sqlsrv-1.5.0b3
   install ok: channel://pear.php.net/MDB2-2.5.0b3
   MDB2: Optional feature fbsql available (Frontbase SQL driver for MDB2)
   MDB2: Optional feature ibase available (Interbase/Firebird driver for MDB2)
   MDB2: Optional feature mssql available (MS SQL Server driver for MDB2)
   MDB2: Optional feature mysql available (MySQL driver for MDB2)
   MDB2: Optional feature mysqli available (MySQLi driver for MDB2)
   MDB2: Optional feature oci8 available (Oracle driver for MDB2)
   MDB2: Optional feature odbc available (ODBC driver for MDB2)
   MDB2: Optional feature pgsql available (PostgreSQL driver for MDB2)
   MDB2: Optional feature querysim available (Querysim driver for MDB2)
   MDB2: Optional feature sqlite available (SQLite2 driver for MDB2)
   MDB2: Optional feature sqlsrv available (MS SQL Server driver for MDB2)
   MDB2: To install optional features use "pear install pear/MDB2#featurename"
   

4. It's probably advisable to knock PEAR back to only allowing stable packages again

   pear config-set preferred_state stable

I just tried this with the following test script and I was able to connect to my local MS SQL Server and retrieve some data:

<?php
require_once 'MDB2.php';

$dsn = array(
    'phptype'  => 'sqlsrv',
    'username' => 'test',
    'password' => 'testpass',
    'hostspec' => 'localhost',
    'database' => 'PEARMDBTEST',
);

$mdb2 =& MDB2::connect($dsn);

if(PEAR::isError($mdb2)) 
{
    die($mdb2->getMessage());
}

$res =& $mdb2->query('SELECT * FROM TestData');

while (($row = $res->fetchRow())) {
    echo $row['TestDataRow'] . "<br/>";
}

?>

MS provides a PHP driver for MSSQL: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20098