Currently testing a migration to PHP7 (from PHP5.5), I noticed that the management of connections to SQL Server via the native functions (mssql_) disappeared. Using connections to MySQL and SQL Server through our PHP applications, I therefore had to look into the question to see what were the possible solutions to replace these native functions.
PHP7 migration
PHP7 has been released for several months now, and is integrated by default for example in Ubuntu 16.04. So we decided to test a migration from Ubuntu 14.04 to Ubuntu 16.04, with a migration to PHP7. We were convinced, among other things, by the very flattering benchmarks for PHP7 and also by the very interesting new features of PHP7 in terms of typing possibilities .
Another point also, the migration to PHP7 is normally quite simple. The main development concerns the overhaul of the engine in depth to improve performance.
Disappearance of native SQL Server functions
Few things change with this migration to PHP7, but looking at the official documentation we see that a number of extensions are disappearing, including the mssql functions. For the others, like ereg and mysql, they were already deprecated for several versions.
These mssql functions allow you to connect and launch queries or stored procedures on a SQL Server. In our case, it is very useful because we have MySQL databases and also SQL Server databases. It is therefore a point which can be blocking for us if ever we do not find an equivalent.
Replacement tracks
If we look at the official documentation, for example that of the mssql_connect function , we see that 3 options are possible:
- Go through PDO
- Use the sqlsrv_connect function
- Go through ODBC
In any case, it is more restrictive than native functions. Because with PDO it will be necessary to pass by an ODBC pilot (thus that joins the solution 3). Finally, sqlsrv_connect is installed in Windows environment (DLL under IIS).
The conclusion is therefore that whatever solution is chosen, it will in any case be more restrictive than a purely native solution. Afterwards, the idea now is to make it as transparent as possible. And as stable as possible too, because it’s a production environment.
Continuing my research, I came across a package, maintained by Meet Bhagdev , which provides an all-in-one that looks interesting. It’s available on a github .
The installation is not very complicated. On a functional installation, with Apache and PHP already, it comes down to getting the right packages, the ODBC manager, and the extensions for PHP.
In summary, here are the commands I had to run (on Ubuntu 16.04 therefore):
wget https://github.com/Microsoft/msphpsql/blob/PHP-7.0-Linux/ODBC%20install%20scripts/installodbc_ubuntu.sh sudo sh installodbc_ubuntu.sh sudo apt-get install php libapache2-mod-php sudo apt-get install php-pear php-dev sudo pecl search sqlsrv sudo pecl install sqlsrv-4.0.5 sudo pecl install pdo_sqlsrv-4.0.5
This has the effect of setting up a PHP extension, with everything needed to communicate with a SQL Server.
The last thing to do was to activate the extension in the php.ini by adding these lines:
[SQL Server] extension=/usr/lib/php/20151012/sqlsrv.so
And the trick was done.
This then gives access, under Linux, to the various sqlsrv_ functions which are described in the documentation . It will work the same as on an IIS.
For example, for a connection to a SQL Server server:
$connectionInfo = array( "Database"=>$this->dbname, "UID"=>$this->dbuser, "PWD"=>$this->dbpass); $this->sqlLink = sqlsrv_connect( $this->dbhost, $connectionInfo);
Continuation of the tests
For now, I’ve gone with this solution.
It seems very good, but I haven’t finished my various tests yet. In any case, I was able to call queries, update data, launch stored procedures without problems. I have not yet tested, for example, queries with OpenQuery to see what it looks like.
I will update this post according to the result of my tests.
On your side, if you have other ideas or better leads, do not hesitate to react in the comments below.
