php - PDO To Connect to MSSQL Over MSSQL_* Functions

Question

There is a range of mssql_* Which are not in the depreciation process.

They work the same as mysql_* functions; they need to me manually escaped, please find the link to the manual below:

http://uk1.php.net/manual/en/book.mssql.php

MSSQL_* Functions was apart of php5-mssql but have now been moved into php5-sybase

Furthermore, using PDO for your Database Construct, is available but is experimental http://php.net/manual/en/ref.pdo-dblib.php

But my overall question, from the fact that PDO/MySQLI is being pushed as main database communication solution, should I stop using the functions mssql_*

Or is it possible for:

PDO Connection:

$dsn = 'mssql:host=localhost;dbname=testdb';
$user = 'dbuser';
$password = 'dbpass';

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

But if this process is still listed as experimental, should developers using Microsoft SQL Server for their databases, wait till this extension is stable for MSSQL Servers

So at the end of the day, PDO Extension or MSSQL_* Functions even though they are not depreciated.. If so, why?

Answer 1

My Own Opinion

I have been using PDO to connect to a MSSQL database for over a year now and so far I have found absolutely no issues.

In fact, I looked into using the mssql_* functions before migrating to PDO, and came to the conclusion that they were a much less reliable, not to mention, insecure way of connecting to a MSSQL Database.

Logically

From a logical point of view, PDO is also the better option as it only takes a few tweaks to the code in order to change from MSSQL to MySQL.

I wrote a wrapper class for the PDO class that makes connecting to these databases very easy.

Consider this as an example:

<?php

// +------------------------------------------------------------------------+
// | class.mssql.php                                                        |
// +------------------------------------------------------------------------+
// | Copyright (c) Company Ltd 2013. All rights reserved.                   |
// | Version       1.0                                                      |
// | Last modified 30/01/2013                                               |
// | Email         [email protected]                                      |
// | Web           http://www.company.co.uk                                 |
// +------------------------------------------------------------------------+

// Make sure the SQL class is included
require_once("class.sql.php");

/*
 * Class mssql
 *
 * @version   1.0
 * @author    Ben Carey <[email protected]>
 * @copyright Company Ltd
 *
*/

class mssql extends sql{

    /**
     * Initialize the object and set/reset all variables
     *
     * This function is called when the object is constructed
     *
     * @access private
     */
    function __construct(&$memcache){

        // Call the sql construct
        parent::__construct($memcache);

        // Global MsSQL defaults
        $this->query_escaper_left               = "[";
        $this->query_escaper_right          = "]";
        $this->connection_engine                = "sqlsrv";
        $this->connection_parameter_host        = "server";
        $this->connection_parameter_database    = "Database";
        $this->select_db_function               = "db_name()";
    }
}

?>

Anything that is unique to MSSQL is defined in this extension and then passed up to the parent class class.sql.php. The beauty of PDO is that the code in the file class.sql.php does not have to be altered in any way to work with any database (or, all the databases that I have tried thus far).

So all that is needed here is a small extension for each database type and it will work.

Whereas, with the native mssql_* functions, if you were to decide to change database for any particular reason, you would have to rewrite everything. Not to mention, you would have to use PDO for MySQL anyway given that the mysql_* functions are now deprecated.

My Testing with PDO

I have been running complex stored procedures, with INPUT PARAMETERS, OUTPUT PARAMETERS, INOUT PARAMETERS, on databases with 100,000,000+ records in them. These have worked absolutely flawlessly, and continue to do so!

References

Another reason not to use the mssql_* functions is that they are no longer supported on Windows with PHP version 5.3 or later:

See Here

The SyBase Extension falls under the same category as the mssql_* functions. They are procedural, impractical and not portable at all!

Functionality

At a glance, I have noticed that none of these extensions have a function equivalent to the mysql_real_escape_string() function. Whereas, in PDO, there is no need for this

Conclusion

It goes without saying that I am a moral PDO supporter (and this has only come after using it for 1 year!). That is not to say I will not listen to other peoples opinions on the mssql_* functions, it will just be very hard to persuade me, and I think most people, that these functions can even compete the PDO.

So to conclude, in my opinion, PDO is the way forward for the following key reasons:

1. It is very portable, easy to switch to different databases with minimal code
2. It is secure without the need of functions like mysql_real_escape_string()
3. It is fast becoming the norm for developers
4. If you do not have experience with Object Oriented Programming, then it is an excellent introduction
5. It comes pre-installed with most PHP Packages
6. It can execute comples queries with ease, including stored procedures
7. After benchmarking it with a MySQL database against the old deprecated mysql_* functions, it has proved to be faster in a lot of cases, if not all cases. - See Here

I asked a similar question a while back, and the same conclusion was drawn:

See here