Doc Bug #75970 original note text

ref.pdo-sqlsrv.connection.php.md

I finally discovered that sqlsrv and pdo_sqlsrv are not the same thing, and work differently, and have different requirements. As near as I can tell, sqlsrv requires the odbc driver from Microsoft. pdo_sqlsrv does not, it uses pdo. This caused me a lot of grief until I figured this out, as most of the discussion for pdo_sqlsrv seems to relate to sqlsrv, not pdo_sqlsrv. They are not the same.

pecl install sqlsrv

This will download and build the module for sqlsrv-4.3.0.tgz. This will not work without the microsoft ODBC driver, which apparently needs to be downloaded and installed separately. I was never able to get this to work as my distro is not one of the supported distros the Micorosoft builds the ODBC driver for.

pecl install pdo_sqlsrv

This downloads and installs the module for pdo_sqlsrv-4.3.0.tgz. This does NOT require the Microsoft ODBC driver. Why? Because it does not use ODBC, it uses PDO to connect to mssql server.

To make a connection using pdo_sqlsrv (without the Microsoft ODBC driver):

$dbh = new PDO ("dblib:host=<ip address>;dbname=db","user_id","password");

Note that we are using "dblib:host", NOT "sqlsrv:host".

So to clarify: In my case, I'm using PHP 7.2 and I want to connect to a Microsoft sql server database. I'm using Slackware 14.2 64 bit, and Microsoft does not build an ODBC driver that works (at least I could not get it to work) for this distro.

1. Install unixODBC. I had to download the source and ./configure|make|make install, as the version that came with the package manager did not work. Don't be afraid to go outside of your package manager and install unixODBC from scratch.
2. When you configure PHP, be sure to include --with-pdo-odbc=unixODBC
3. After you install PHP, use PECL to download and install pdo_sqlsrv. Do NOT install sqlsrv. And be sure to add extension-pdo_sqlsrv to php.ini if necessary, the PECL installer doesn't always do this.

Now you should be able to to do this:

$dbh = new PDO("dblib:host=<ip address>;dbname=db","user_id","password");

When I do the steps above, it works great. YMMV. I welcome comments from anyone that knows more about this process.

ref.pdo-sqlsrv.php.md

Disclaimer: this is how I was able to connect to a Microsoft sql server via PDO using the PECL pdo_sqlsrv extension and unixodbc. PHP 7.2. Linux – Slackware 14.2. These comments represent my understanding at the time I wrote this, what I did to get this working, and may contain errors. I am not responsible for any problems or damage etc. that might occur if you follow these instructions. YMMV. This worked for me, I hope it helps you.

This is an extension to the comment I posted yesterday that can be found here (http://php.net/manual/en/ref.pdo-sqlsrv.connection.php). This version is more complete and includes expanded instructions.

I compiled and installed PHP from source. If you use your package manager to do this, then it is up to you to figure out how to get --with-pdo-dblib --with-pdo-odbc=unixODBC working, as well as how to install the PECL pdo_sqlsrv extension.

Do NOT install freetds. pdo_sqlsrv does not require freetds. Do NOT install the Microsoft ODBC driver. pdo_sqlsrv does not use it. Do NOT install the PECL "sqlsrv" extension. It is not needed.

Install unixODBC. I had to download and install from source because the package manager version did not work. How you do this is up to you, but if you get compile errors when compiling PHP, your unixODBC install is a likely culprit.

Install PHP 7.2 (or whatever version you want). When you configure PHP, be sure to include:

--with-pdo-dblib --with-pdo-odbc=unixODBC

If you get compile errors, try it without --with-pdo-odbc=unixODBC to make sure it isn't something else causing the problem.

After you get PHP installed and working, install the PECL pdo_sqlsrv extension:

pecl install pdo_sqlsrv.

This is all the setup that is required to get this to work.

This is how I successfully connected to and queried a mssql table. In this case, I used the ip address of my sql server, not host or server name. Notice the variable names $pdo_object and $pdo_statement_object. I used them to indicate what data types those variables actually represent. $dsn is simply a text string containing the ip address and default database for the connection to your mssql server. Also note that in $dsn we use “dblib:host”, NOT “sqlsrv:host”. I believe this to be a critical distinction, as many examples of how to use pdo seem to use “sqlsrv:host”, which IIAC utilizes the sqlsrv extension and the MS ODBC driver, not pdo. This caused me a lot of grief until I figured this out.

$dsn = 'dblib:host=<ip address>;dbname=<database name>';
$user = 'user id';
$password = 'password';

try {
    $pdo_object = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sql = "SELECT * from <some table>";
$pdo_statement_object = $pdo_object->prepare($sql);
$pdo_statement_object->execute();
// $result = $pdo_statement_object->fetch(PDO::FETCH_ASSOC);
$result = $pdo_statement_object->fetchAll();
print_r($result);