php/PDO access to MS SQL Server from Ubuntu 16.04 LTS Server
The replacement of a Ubuntu 10.04 LTS server with one running Ubuntu 16.04 LTS necessitated moving from the php mssql_* functions to the PDO functions. This required the installation of the “dblib” PDO driver and FreeTDS. It took a fair bit of ferreting around to get it to work, so here’s a summary.
Installation of php 7.0 and php 5.6
See http://lornajane.net/posts/2016/php-7-0-and-5-6-on-ubuntu
Summary:
|
1 2 3 |
sudo add-apt-repository ppa:ondrej/php sudo apt-get update sudo apt-get install php5.6 php7.0 |
Installation of modules
|
1 |
sudo apt-get install php5.6-mysql php-gettext php5.6-mbstring php-xdebug libapache2-mod-php5.6 libapache2-mod-php7.0 php5.6-sybase freetds-common libsybdb5 |
Changing php versions
From php5.6 to php7.0:
Apache:
|
1 |
sudo a2dismod php5.6 ; sudo a2enmod php7.0 ; sudo service apache2 restart |
CLI:
|
1 |
sudo ln -sfn /usr/bin/php7.0 /etc/alternatives/php |
from php7.0 to php5.6:
Apache:
|
1 |
sudo a2dismod php7.0 ; sudo a2enmod php5.6 ; sudo service apache2 restart |
CLI:
|
1 |
sudo ln -sfn /usr/bin/php5.6 /etc/alternatives/php |
FreeTDS config
|
1 2 3 4 5 6 7 |
[global] # TDS protocol version tds version = 8.0 [MyServer] host = 192.168.0.x port = 1433 tds version = 8.0 |
Example connection, retrieve SQL Server version, execute SELECT query, and display results
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
<?php $host = 'MyServer'; $user = 'MyUser'; $pass = 'MyPassword'; $dbname = 'MyDatabase'; try // Connect to server with try/catch error reporting { $DBH = new PDO('dblib:host='.$host.';dbname='.$dbname, $user, $pass); $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); } catch(PDOException $e) { echo "Couldn't connect to $host/$dbname: ".$e->getMessage(); exit(); } echo "Connected to the $host/$dbname server OK:<br>\n"; // Simple SELECT query with no error reporting $sql='SELECT @@VERSION as Version'; $STH = $DBH->query($sql); $STH->setFetchMode(PDO::FETCH_ASSOC); $row=$STH->fetch(); echo $row['Version']."<hr>\n"; // Simple SELECT query with try/catch error reporting $sql='SELECT * FROM User'; try { $STH = $DBH->query($sql); $STH->setFetchMode(PDO::FETCH_ASSOC); } catch(PDOException $e) { echo "Error: ".$e->getMessage(); exit(); } // Display query results if ($row = $STH->fetch()) // Get first row and if there is one . . . { $bgcolor='white'; echo "<table border=1>\n<tr bgcolor=$bgcolor>"; foreach ($row as $key => $value) // Output column names echo "<td><font size=-2><b>$key</b></font></td>"; $bgcolor='lightgray'; echo "</tr>\n<tr bgcolor=$bgcolor>"; foreach ($row as $key => $value) // Output first row echo "<td valign=top><font size=-1>".str_replace(' ',' ',$value)."</font></td>"; echo "</tr>\n"; } while ($row = $STH->fetch()) // Get subsequent rows if any and . . . { if ($bgcolor=='white') $bgcolor='lightgray'; else $bgcolor='white'; echo "<tr bgcolor=$bgcolor>"; foreach ($row as $key => $value) // Output subsequent rows echo "<td valign=top><font size=-1>".str_replace(' ',' ',$value)."</font></td>"; echo "</tr>\n"; } echo "</table>\n"; ?> |