2

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-written 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?

Kev
  • 118,037
  • 53
  • 300
  • 385
Jonathan M
  • 17,145
  • 9
  • 58
  • 91
  • Have a look at my answer, it's fairly fool proof. – Kev Jul 19 '11 at 20:17
  • Good info. You mention in your answer "if you loaded the PDO driver..." Is there another method/extension these drivers work with other than PDO; maybe even MDB2 perhaps? – Jonathan M Jul 19 '11 at 20:31
  • Also, if you'll put your comment in an answer, I'll accept it. Thanks for your help. – Jonathan M Jul 19 '11 at 20:34
  • My apologies for not quite getting the PEAR MDB2 bit about this question, I shouldn't have closed this as a duplicate. I've added an answer which should help. – Kev Jul 19 '11 at 23:55
  • It would be great if you could contribute your custom driver to PEAR - assuming it's ok to do so. – kguest Jul 20 '11 at 09:21
  • Agreed, they seem to be fairly open to adding new drivers. I believe the MS SQL native driver started off as a drive-by issue logged in the bug tracker by some bod. – Kev Jul 20 '11 at 13:58
  • If the company I developed the driver for gives permission, I'll do so. Thanks. – Jonathan M Jul 20 '11 at 15:32

2 Answers2

3

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

?>
Community
  • 1
  • 1
Kev
  • 118,037
  • 53
  • 300
  • 385
  • I'm getting strict standards warnings, any idea? `Strict Standards: Only variables should be assigned by reference in E:\Websites\Dev\mdb2_test.php on line 16'` – Tony M Dec 09 '14 at 17:29
  • @TonyM - just hide them by adjusting your error reporting, not ideal but sometimes it's the only way. Check out this answer: http://stackoverflow.com/a/9984309/419 covers all the bases based on what type of access you have to the server. – Kev Dec 09 '14 at 18:36
  • @TonyM also tacking on a `& ~E_DEPRECATED` doesn't hurt. Kinda depends on what version of PHP you're using. – Kev Dec 09 '14 at 18:37
  • @TonyM ...and finally see: http://stackoverflow.com/q/11777908/419 might be related, but would need to see what line 16 in your script looks like. – Kev Dec 09 '14 at 18:43
1

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

Marc B
  • 356,200
  • 43
  • 426
  • 500