Microsoft SQL Server from PHP
+
Par Remi le mardi 20 septembre 2016, 09:15 - HowTo - Lien permanent
Here is a small comparison of the various solutions to use a Microsoft SQL Server database from PHP, on Linux.
All the tests have be run on Fedora 23 but should work on RHEL or CentOS version 7.
Tested extensions:
1. Using PDO, ODBC and FreeTDS
Needed components:
- freetds library and extension pdo_odbc extension
- PHP version 5 or 7
- RPM packages: freetds (EPEL), unixODBC, php-pdo, php-odbc
ODBC driver configuration
The driver must de defined in the /etc/odbcinst.ini file:
[FreeTDS] Description=FreeTDS version 0.95 Driver=/usr/lib64/libtdsodbc.so.0.0.0
Data source configuration
The used server must be defined in the /etc/odbc.ini file (system wide) or in the ~/.odbc.ini file (user):
[sqlsrv_freetds] Driver=FreeTDS Description=SQL via FreeTds Server=sqlserver.domain.tld Port=1433
Connection check from the command line
$ isql sqlsrv_freetds user secret SQL> SELECT @@version Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64) Nov 1 2016 23:24:39 Copyright (c) Microsoft Corporation on Linux (Red Hat Enterprise Linux) SQLRowCount returns 1 1 rows fetched SQL> quit
Connection from PHP
$ php -r '
echo "+ Connection\n";
$pdo = new PDO("odbc:sqlsrv_freetds", "user", "secret");
echo "+ Query\n";
$query = $pdo->query("SELECT @@version");
if ($query) {
echo "+ Result\n";
$row = $query->fetch(PDO::FETCH_NUM);
if ($row) {
print_r($row);
}
}
'
+ Connection
+ Query
+ Result
Array
(
[0] => Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
on Linux (Red Hat Enterprise Linux)
)
This solution is often the simplest, as all the dependencies are free and available in the Linux distributions.
2. Using PDO, mssql and FreeTDS
Needed components:
- freetds library and mssql extension
- PHP version 5 (the extension is deprecated and removed from PHP 7)
- RPM packages: freetds (EPEL), php-mssql
Connection from PHP
$ php -r '
echo"+ Connection:\n";
$conn = mssql_connect("sqlserver.domain.tld", "user", "secret");
if ($conn) {
echo"+ Query:\n";
$query = mssql_query("SELECT @@version", $conn);
if ($query) {
echo"+ Result:\n";
print_r($row = mssql_fetch_array($query, MSSQL_NUM));
}
}
'
+ Connection
+ Query
+ Result
Array
(
[0] => Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
on Linux (Red Hat Enterprise Linux)
)
This solution is also simple as all the dependencies are also free and available in Linux distributions. However, it uses a deprecated extension, and without using the PDO abstraction layer.
3. Using PDO, ODBC and Microsoft® ODBC Driver
Needed components:
- Microsoft ODBC Driver for SQL Server and the pdo_odbc extension
- PHP version 5 or 7
- RPM packages: mssqlodbc (remi), unixODBC, php-pdo, php-odbc
ODBC driver configuration
the driver must be defined in the /etc/odbcinst.ini file (automatically added installation) :
[ODBC Driver 13 for SQL Server] Description=Microsoft ODBC Driver for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0 Threading=1
Data source configuration
The used server must be defined in the /etc/odbc.ini file (system wide) or the ~/.odbc.ini fle (per user):
[sqlsrv_msodbc] Driver=ODBC Driver 13 for SQL Server Description=SQL via Microsoft Drivers Server=sqlserver.domain.tld
Connection check from the command line
$ isql sqlsrv_msodbc user secret SQL> SELECT @@version Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64) Nov 1 2016 23:24:39 Copyright (c) Microsoft Corporation on Linux (Red Hat Enterprise Linux) SQLRowCount returns 1 1 rows fetched SQL> quit
Connection from PHP
$ php -r '
echo "+ Connection\n";
$pdo = new PDO("odbc:sqlsrv_msodbc", "user", "secret");
echo "+ Query\n";
$query = $pdo->query("SELECT @@version");
if ($query) {
echo "+ Result\n";
$row = $query->fetch(PDO::FETCH_NUM);
if ($row) {
print_r($row);
}
}
'
+ Connection
+ Query
+ Result
Array
(
[0] => Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
on Linux (Red Hat Enterprise Linux)
)
This solution, close to #1 also requires the proprietary drivers.
4. Using the Microsoft® Driver
Needed components:
- Microsoft ODBC Driver for SQL Server and the sqlsrv extension
- PHP version 7 (version for PHP 5 doesn't exists for Linux)
- RPM packages: mssqlodbc (remi), unixODBC, php-sqlsrv (remi)
Connection check from the command line
$ sqlcmd -S sqlserver.domain.tld -U user -P secret -Q "SELECT @@version" Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64) Nov 1 2016 23:24:39 Copyright (c) Microsoft Corporation on Linux (Red Hat Enterprise Linux) (1 rows affected)
Connection from PHP
$ php -r '
echo"+ Connection:\n";
$conn = sqlsrv_connect("sqlserver.domain.tld", array("UID" => "user", "PWD" => "secret"));
if ($conn) {
echo"+ Query: \n";
$query = sqlsrv_query($conn, "SELECT @@version");
if ($query) {
echo"+ Result:\n";
print_r($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_NUMERIC));
}
}
'
+ Connection:
+ Query:
+ Result:
Array
(
[0] => Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
on Linux (Red Hat Enterprise Linux)
)
This solution, close to #2 also requires the proprietary drivers, and doesn't use the PDO abstraction layer.
5. Using PDO and the Microsoft® Driver
Needed components:
- Microsoft ODBC Driver for SQL Server and the pdo_sqlsrv extension
- PHP version 7 (version for PHP 5 doesn't exists for Linux)
- RPM packages: mssqlodbc (remi), unixODBC, php-pdo, php-sqlsrv (remi)
Connection from PHP
$ php -r '
echo "+ Connection\n";
$pdo = new PDO("sqlsrv:Server=sqlserver.domain.tld", "user", "secret");
echo "+ Query\n";
$query = $pdo->query("SELECT @@version");
if ($query) {
echo "+ Result\n";
$row = $query->fetch(PDO::FETCH_NUM);
if ($row) {
print_r($row);
}
}
'
+ Connection
+ Query
+ Result
Array
(
[0] => Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
on Linux (Red Hat Enterprise Linux)
)
This solution, close to #1 and #3 also requires the proprietary drivers.
6. Using PDO and the FreeTDS library
Needed components:
- freetds library and extension pdo_dblib extension
- PHP version 5 or 7
- RPM packages: freetds (EPEL), php-pdo, php-pdo_dblib
Connection from PHP
$ php -r '
echo "+ Connection\n";
$pdo = new PDO("dblib:host=sqlserver.domain.tld", "user", "secret");
echo "+ Query\n";
$query = $pdo->query("SELECT @@version");
if ($query) {
echo "+ Result\n";
$row = $query->fetch(PDO::FETCH_NUM);
if ($row) {
print_r($row);
}
}
'
+ Connection
+ Query
+ Result
Array
(
[0] => Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)
Nov 1 2016 23:24:39
Copyright (c) Microsoft Corporation
on Linux (Red Hat Enterprise Linux)
)
This solution, close to #1, #3 and #5 use the open source FreeTDS library and take benefit of the PDO abstraction layer.
7. Conclusion
I think that using PDO should be preferred, to avoid the lock in a specific database server.
FreeTDS usage have filled a lot of needs in the past, as it was the only solution available for PHP 5. Using the sqlsrv or pdo_sqlsrv extension seems now more pertinent for PHP 7, but sadly requires to use the proprietary drivers (well, if you use Microsoft SQL server, you have already leave the free world).
Up to you for a choice.
Commentaires
Source code of the PHP Driver is available on https://github.com/Microsoft/msphpsql/tree/PHP-7.0-Linux
Add the pdo_dblib usage.
Small update: test are now run with "Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64) on Linux (Red Hat Enterprise Linux)"
See: How to run SQL Server v.Next Public Preview on Fedora