CODEWITHSUNDEEP
phpsqlsql-serverwindowspdo
Dominick
Dominick

Reputation: 448

How can I use PDO with MSSQL in PHP from a Windows dev envirenment?

I am developing an application that needs to get data from an outside MSSQL database. I spent a lot of time trying to get various methods of connection to MSSQL with PHP, but there were several routes which were depreciated.

On my production environment running Debian, I was able to make a connection with PDO_DLIB and FreeTDS with something like this:

$this->db = new \PDO('dblib:host='.$thedb_host_prod.';dbname='.$thedb_database_name_prod,     $thedb_database_user, $thedb_database_pass);

On Windows, MSSQL is depreciated. I believe I'm using the Microsoft SQL Server Driver and was only able to get it to work with ODBC, which looks something like this:

$dsn = "Driver={SQL Server};Server=".$thedb_host_dev.";Database=".$thedb_database_name_dev;
$this->odbc = odbc_connect($dsn, $thedb_database_user, $thedb_database_pass);

Then, the problem becomes, in each method I need to do something differently for ODBC than I do for DLIB.

public function exampleMethod(){

    // logic and create the query in $query

    if($this->dev == false){
        // PRODUCTION
        try {
            $stmt = $this->db->prepare($query);
            $stmt->execute();
            $result = $stmt->fetchAll(PDO::FETCH_OBJ);
        } catch (PDOException $e) {
            echo 'Connection failed: ' . $e->getMessage();
        }
    } else {
        // DEVELOPMENT
        $query = $query;
        $stmt = odbc_exec($this->odbc, ($query));
        $result =  array();
        while($currentRow = odbc_fetch_object( $stmt )){
            $jobNumber = $currentRow->Code; // Set object key to jobNumber
            array_push($result, $currentRow);
        }
    }
}

This actually works, but the problem is, with how the query for ODBC needs to be prepared vs how the DBLIB query should be prepared, means that if I don't want to write the query twice in each method, I have to create it before each action. This is really bad because it means I'm not putting my variables into the query with PDO's bindValue.

So, has anyone been able to get PDO work with PHP 5.4 and MSSQL in a Windows environment? Does anyone see a way of securing the query in a way that doesn't make me duplicate the query in each method, once for ODBC and once for DBLIB?

My plan currently is to develop the application out and then remove all of the ODBC stuff which will allow me to put the query in the $stmt properly, avoiding this problem. But until then, it's making development a huge pain.

Upvotes: 1

Views: 3525

Answers (3)

Balmipour
Balmipour

Reputation: 3055

Oooook, so I finally got it working !

Here are the libraries / drivers for Sql-server connection via C/C++/java/PHP/etc.

Precisely, here are the Windows drivers for PHP.


Check out this page to know which version you need to get


They all exist in have 32 and x64, as well as "Thread safe" (ts) and "non-thread-safe" (nts) versions.
From what I read, nts versions are to be used if you work with IIS.

Usage :

  • Download and extract the package you need.
    in my case, package 3.2, for php 5.6

  • Take necessary drivers
    in my case, php_sqlsrv_56_ts.dll and php_pdo_sqlsrv_56_ts.dll

  • Put them in your php extensions directory
    in my case, [...]\php5630vc11x86x170623162800\ext

    If you wonder where this dir is inside your PHP, it should be quite easy to find, since you have plenty of other dlls here.
    Likely, php_pdo_mysql.dll, php_pdo_pgsql.dll, etc.

  • Modify your php.ini
    Be careful with Wamp, it seems to have one in the php directory AND another one in the apache dir.

    Add lines to load the two extensions.
    You can add them at the end of your file, or with the other extensions loading.
    In my case, here's what I added :

    ;SQL-srever extensions
extension=php_sqlsrv_56_ts.dll
extension=php_pdo_sqlsrv_56_ts.dll


This part was for the PHP/PDO side.
For the driver to actualy work, you also need your (Windows) machine to have the ODBC drivers installed.

Feel free to try your connection already, but if it whines that you need the ODBC driver, go get it here :
Microsoft® ODBC Driver 11 for SQL Server®

And last, but not least, be sure to use the right format for your PDO connection.
$conn = new PDO ("sqlsrv:Server=$srv_host;database=$srv_dbname";
For comparison, here's what I wsa using on my linux server :
$conn = new PDO ("dblib:host=$srv_host:$srv_port;dbname=$srv_dbname", "$srv_username", "$srv_password");

I thought this syntax was common to all PDO drivers, but it appears I was wrong.
Here is the PHP PDO driver documentation.

Upvotes: 2

Chris
Chris

Reputation: 137183

I have worked with PHP 5.4 and SQL Server with PDO on Windows.

I strongly recommend using Microsoft's Web Platform Installer to set everything up. You can use it to install PHP, a local version of SQL Server Express to develop on, the official PHP driver for SQL Server, and IIS, all set up to work together.

One note of caution: The last release of the SQL Server PDO driver was in April, 2012. I reported a bug against it last year and was told that it's in "limited support", which apparently translates to "you're on your own". In any case, it worked reasonably well.

Upvotes: 1

Tomas
Tomas

Reputation: 1422

I just actually had to do some work in php connecting to a MSSQL server. I did have to downgrade to php 5.4 due to the fact that the php_pdo_sqlsrv.dll is not updated for 5.5. For the dll files check here. But now down to the code I used to connect once you have the .dll files in the right place.

try {
    $db = new PDO("sqlsrv:Server={$host};Database={$database}", $userName, $password);
}catch(PDOException $e){
    die("failed to connect");
}

Just a standard PDO connection. Just in order to get it to work you must make sure that the .dll files are in the php directory.

I hope that answers at least part of the question.

Upvotes: 1

Related Questions