2016-06-01

PHP Linux calling MS SQL server via PDO

Many seem to have problems accessing MS SQLServer databases from Linux via PHP/PDO.
This is how I connect.


I use freeTDS to interface with PHP/PDO.

Setup freeTDS

First I I downloaded latest stable freetds release from http://www.freetds.org/software.html, which happened to be freetds-0.95.95 in my case.

Then the compile pirouette:
  1. ./configure --enable-msdblib --disable-debug --with-tdsver=8.0 --enable-msdblib
  2. make
  3. make install (as root)
This can be checked with tsql -C:


I tried to ./configure TDS version 8.0 which is MS SQL, but I got version 5.0 which is Sybase!
The configuration directory is /usr/local/etc, the configuration file is freetds.conf, (you find an example file in the directory), I added an entry for my MS SQL Server instance:


[rdc01]
       host=nn.nn.nn.nn.nn
       port = 1433
       tds version=8.0
The server is called rdc01, port is MSSQL default 1433 and tds version=8.0.
I tested this with the tsql command:


[tooljn@toossedwvetl3 pgm]$ tsql -S rdc01 -U userid -P 'password' -L database
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> select 'hej'
2> go

hej
(1 row affected)
1> exit


As you see I specify rdc01 as MS SQL server (rdc01 points back to the entry in the configuration file.
I run a select ‘hej’ (terminated by go on the next line) just to make sure the server responds, and it did with ‘hej (1 row affected)
And I ended the session with exit.
So far so good freeTDS is installed and working, now to PHP.

Compile PHP

The compile pirouette:
  1. ./configure
  2. make
  3. make install (as root)
I have a lot of ./configure parms for the PDO interface I use:
--with-pdo-mysql=shared \
--with-pdo-dblib=shared \
--with-pdo-odbc=shared,unixODBC,/usr \
--with-unixODBC=/usr \


I use the following PHP code to display  the PDO interfaces installed:
print_r(PDO::getAvailableDrivers());


dblib is what we want.
Now we only have to do a pdo connect:
$pdoHandle = new PDO (dblib:host=rdc01;dbname=database, userid, password);


dblib:host=rdc01 points back to the configuration file.

And that’s it folks.

No comments:

Post a Comment