2014-05-30
Want to access a MSSQL or Sybase server from a CentOS server with PHP? Ouch...
But there is hope!
I'm going to use ODBC (Open Database Connectivity) as the database driver, and for that we need to install a couple of things. First of, we need FreeTDS to be able to talk to the MSSQL/Sybase database. FreeTDS is an open source implementation of the Tabular Data Stream format which is used by these databases. Second, we need the unixODBC driver, which gives other programs an API to talk to. And finally, we need to install the php-odbc module in order for PHP to be able to talk to unixODBC.
Installing is easy, just run the following commands:
yum install unixODBC yum install freetds yum install php-odbc service httpd restart
After installation you should have the following files on your filesystem:
We'll edit these files with information on our data source. Our PHP programs will then be able to talk to the MSSQL/Sybase database via the data source.
First off, we need to tell FreeTDS where our database server is located. We'll give the server the descriptive name "my_server".
/etc/freetds.conf:
[global]
tds version = 8
timeout = 30
connect timeout = 30
text size = 64512
[my_server]
host = example.com
port = 2028
client charset = UTF8
Note that the [global] config is applied to all servers. I have taken the opportunity to set a reasonable timeout value here. If this is not set, FreeTDS will never time out.
Next, we configure FreeTDS as the driver to use with unixODBC.
/etc/odbcinst.ini:
[FreeTDS]
Description = ODBC for Sybase and MSSQL
Driver = /usr/lib64/libtdsodbc.so.0
Setup = /usr/lib64/libtdsS.so.2
Driver64 = /usr/lib64/libtdsodbc.so.0
Setup64 = /usr/lib64/libtdsS.so.2
FileUsage = 1
Note: My machine is 64 bit. If you run a 32 bit machine, you probably need to point to the 32 bit drivers.
Finally, we configure the data source. The data source should use the server we configured in freetds.conf, and the FreeTDS driver we configured in odbcinst.ini.
/etc/odbc.init:
[my_data_source]
Driver = FreeTDS
Description = My one and only data source
Servername = my_server
Database = my_db
Before we move on to PHP, it's handy to check that the installation works properly. To test that FreeTDS can access the database, run the following command:
tsql -S my_server -U <your-username>
If that works fine, check that the data source is usable:
isql my_data_source <your-username> <your-password>
If you're unlucky, like me, unixODBC won't work as it should. To see what's up, you can enable logging. Add the following to /etc/odbcinst.ini:
[ODBC] Trace=yes TraceFile=/tmp/unixodbc.log
In my installation, it turned out that unixODBC was looking for an older driver in /usr/lib64/libodbccr.so.1. The solution was to make a symlink to the newer driver:
ln -s libodbccr.so.2 libodbccr.so.1
Now that we have a working data source, let's try to access it from PHP. You have a couple of options here, but the best is probably via PHP PDO. However, in my installation, PDP PDO ends up with a segfault every time a fetch function is called. I was not able to fix this, so I ended up using the freestanding odbc functions instead. And to make it worse, I wasn't able to use prepared statements, as they also caused segfaults. Weirdly enough, everything works fine on my Windows installation, so this is probably something with the combination of unixODBC and PHP.
Anyway, here is an example of how to use the odbc functions with our data source:
class WinDB{
private $odbc_conn;
function __construct($data_source_name, $user, $password){
$this->odbc_conn = odbc_connect($data_source_name, $user, $password);
if($this->odbc_conn === false){
throw new Exception("Couldn't connect odbc: " . odbc_errormsg($this->odbc_conn));
}
}
function __destruct(){
if($this->odbc_conn !== false){
odbc_close($this->odbc_conn);
}
}
public function query($query){
$resource = odbc_exec($this->odbc_conn, $query);
if($resource === false){
throw new Exception("Couldn't execute odbc query: " . odbc_errormsg($this->odbc_conn));
}
// Hacky time: Need to get the entire result set to free the cursor.
$res = array();
while($row = odbc_fetch_array($resource)){
$res[] = $row;
}
return $res;
}
}
One thing to note here is that I advance the cursor all the way to the end before I return the result set. This is because you can only have one cursor open at a time with odbc, and it is not possible to fetch results from any other queries before the cursor is freed.
The second thing to note is that the example does not use prepared statements, nor does it escape any variables. As such, use with caution!