In this article, I will explain how it is possible to connect and query an
MS SQL server (running under a Windows operating system) from php
installed on a unix box. The same problem, in the case of php installed
under Windows is not covered here because it is well explained in php FAQ;
sadly I cannot say the same thing for the unix case.
In fact, there is a multitude of incorrect or outdated sources of
information which will cause new people trying to
solve this problem to drop it after some bad tries.
This article can also be of help to people who don't want to use php as a client.
Two paths
There are fundamentally two techniques to remotely access from unix to an MS SQL
server. Both technics are supported under php.
It is possible to use a library that implements the TDS (Tabular Data Stream)
protocol, which is the application-level protocol used by Sybase databases and
by MS SQL. It is also possible to establish a way of communication by using ODBC
(Open Database Connectivity). This is a database independent communication API
which allows applications to communicate, via standard-based function calls to a
back end database management system.
TDS
There are two implementations of the TDS protocol. The first implementation is Sybase Open Client
Library, which Sybase released as publicly available in binary form. (You must register to
Sybase site, for free). The second is FreeTDS, an opensource
project to document and implement the Tabular Data Stream protocol.
We are interested in the 4.2 , 5.0 and 7.0 versions of tds ; freetds
covers all of them, while sybase ct library supports up to 5.0. (This is the
default one and the one used by the latest sybase dbms).
Microsoft officially supports tds 4.2 and tds7.0 (in SQL Server 7 only).
TDS 7.0 supports some new features introduced in SQL Server 7 as Unicode data types.
In MS SQL versions prior to 7, tds5.0 was unofficially supported. However, since SQL
Server version 7, the client requests in tds5.0 are refused by the server, but
Microsoft has reopened access to tds5.0 in SQL Server 7, Service Pack 2.
I'm explaining all of this because I noticed that sybase ct library didn't succeed to connect to MS SQL 7 through
protocol tds4.2, while freetds with tds4.2 behaved perfectly. So, if you have
MS SQL 7 and want to use sybase libraries (and you experience the same problem that
happened to me) you must apply the patch and connect through protocol tds5.0 !
MS SQL 7 Service Pack 2 can be downloaded from http://www.microsoft.com/sql/downloads/sp2.htm
In order to connect to MS SQL from a remote machine through tds, it is necessary to
activate tcp/ip connectivity in MS SQL, using "Server Network Utility".
Sybase Client Library
Now, forget about the outdated libraries or the ones linked
in php faq, unless you still have a libc5 system. (If you have a libc5 system you can download the libraries at
http://www.php.net/extra).
The client library is distributed with Sybase Adaptive Server Enterprise, which
can be downloaded for free from http://linux.sybase.com. Currently, Sybase Adaptive Server Enterprise for Linux version 11.9.2 is offered as a free release for development; support can be purchased
separately. Download at least the file named sybase-common-11.9.2-1.i386.rpm, which contains the only files you need to compile php. It is just 5 Mb of download.
Next, unpack the files. I will assume the installation directory is
/opt/sybase.
Go to the php source directory and do a ./configure adding "--with-sybase-ct=/opt/sybase" to the usual configure options.
After having compiled and installed php, you need to add the location of the libraries to your library path.
You can copy the shared libraries to
/usr/local/lib; add the path to /etc/ld.so.conf; or simplyadd the line below to apachectl:
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/sybase/lib
The next thing you need to do is to declare the environment variable SYBASE to point to the
sybase installation directory. If Sybase Open Client Library is loaded without this
variable correctly set, it segfaults (apache segfaults) !!
So you can add to apachectl:
export SYBASE=/opt/sybase
Now you must edit the "interfaces" file, which must be located in the installation dir. The
interfaces file contains information about remote servers. A typical configuration to access
a Windows machine with MS SQL installed with ip 192.168.1.4 and listening on port 1433
connected to the local lan (eth0) is this:
relaxsql query tcp eth0 192.168.1.4 1433 master tcp eth0 192.168.1.4 1433
The name "
relaxsql" is the hostname which will be used by php functions to establish
a connection to MS SQL on that machine. Be sure to set the correct permissions to the sybase
installation directory and to this file; apache must have read access to this file.
Here is a sample php code to test everything. It connects as user "
utente" with password "parola" and makes two queries to the default database for that user, as defined in MS SQL Server.
<?php
$numero= mssql_connect('relaxsql' , 'utente' , 'parola' );
echo ("ConnectID: $numero<br>\n");
$result=mssql_query('select * from tavoladiprova',$numero );
while ($row=mssql_fetch_array($result)) {
$counter++;
$c1=$row['colonna1'];
$c2=$row['colonna2'];
echo ("$counter c1: $c1 c2: $c2<br>\n");
}
$result=mssql_query('INSERT into tavoladiprova values('kooooo','ooooook')',$numero);
mssql_close($numero);
?>
The default TDS protocol version used by sybase client library is TDS 5.0. In order to convince
php to use TDS4.2 I've made a quick and dirty patch to the php code. This patch is against
php-4.0.1pl2; you can use it if you want TDS 4.2.
FREETDS
FreeTDS can be downloaded from www.freetds.org. I suggest downloading the latest snapshot,
which gave me better results and seems to have less bugs. (At the time of this article, the latest
snapshot is dated Jul 6 2000). The direct link is:
ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/current/freetds-current.tgz. Alternatively, you can download the latest CVS. The information needed to download is in the FAQ on the main site.
ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/current/freetds-current.tgz. Alternatively, you can download the latest CVS. The information needed to download is in the FAQ on the main site.
As previously stated, freeTDS supports TDS 7.0 and will let you take advantage of its features.
Therefore, it could be a better choice if you need to connect to MS SQL Server 7.
Also, freetds is an open source project under constant development, with all the advantages
this implies. Personally, it is the solution I prefer among all.
Unpack the files and do a configure:
./configure --with-tdsver=4.2 --enable-msdblib --enable-dbmfix --with-gnu-ld --enable-shared --enable-static
(The tdsver options simply sets the default protocol, this can be changed anytime at runtime)
The default installation directory is
/usr/local/freetds.
After you have patched a clean php-4.0.1pl2 package ("patch -p0 < patch.freetds" from the
parent dir of the source package), you can build php with the configure option
"--with-sybase-ct=/usr/local/freetds"
As for the sybase library, the environment variable SYBASE pointing to the freetds installation
directory needs to be declared. If the variable isn't declared, apache starts correctly and only the php sybase/mssql functions
won't work. You can do this in apachectl or directly in your php code. It is also possible to set the tds protocol version from an environment variable
and to set a debug logfile (warning: it grows rapidly):
<?php
//putenv('TDSDUMP=/tmp/tds.log');
putenv('SYBASE=/usr/local/freetds');
putenv('TDSVER=70');
?>
Finally, you must correctly edit the file /usr/local/freetds/interfaces, as explained in
the previous section regarding sybase libraries. One interesting difference is that the
third field of a configuration row can be used to specify a default protocol for that
specific host:
myserver query tcp tds4.2 127.0.0.1 1433 master tcp tds4.2 127.0.0.1 1433
You can test your installation/configuration with the simple php code reported above.
ODBC
ODBC introduces an overhead to connection time because the layers of communication
are more. The client machine needs to have an ODBC driver manager which is directly
interrogated by the application. The driver manager establishes a connection
with a remote ODBC server by using a specific ODBC driver resident on the client machine;
the ODBC server on the server machine translates ODBC calls and speaks to the database.
The driver manager and the driver itself are independent. Therefore, it is to the driver manager
that we must link php if we want odbc connectivity.
ODBC has the advantage of allowing applications to be written which theoretically are independent from the dbms. If you need to change the dbms, you just have to change the driver, not the code.
ODBC is a programming API and doesn't cover a client/server protocol. Therefore, if you want
to communicate from a client machine (unix) to a remote machine (windows) using odbc you
need a driver which handles the client/server connection. In other words, a bridged driver
which resides both on the client machine and on the server machine.
The client side of the driver will keep the requests from the local odbc driver manager,
and will handle the connection to the server side driver. Depending on its implementation,
the server side driver could speak directly to the database or use a local odbc driver.
An alternative implementation to establish a client/server connection is that the local
ODBC driver will translate ODBC calls in to the language of the destination database. This will
connect to the dbms directly. (This is the case of Inline's driver which you will read about later).
Here are listed the packages that allow an odbc solution. It is strange that php FAQ
report only the openlink's driver..
ODBC driver managers for linux:
- iODBC (www.iodbc.org) is an opensource odbc driver manager and SDK mantained by Openlink Software (www.openlinksw.com) which also has a similar odbc driver manager distributed only in binary form.
- unixODBC (www.unixodbc.org) The unixODBC Project goals are to develop and promote unixODBC to be
the definitive standard for ODBC on the Linux platform. It is an opensource project.
This project is supported by Easysoft (www.easysoft.com)
Each of these driver managers are supported under php. You must choose one odbc driver manager and build php linking against its libraries:
| To link against iodbc: | --with-iodbc= |
| openlink: | --with-openlink= |
| unixodbc: | --with-unixodbc= |
Warning: I had a problem building php as a Dynamic Shared Object for apache (--with-apxs option) and
linking it to a driver manager. This occurs if you build apache with DSO option.
Apache doesn't build with libpthreads, while driver managers do this by default. This conflict
leads to a segmentation fault when apache starts. The solution is to rebuild apache with libpthreads
(
rm config.cache and do "LDFLAGS=lpthreads ./configure --prefix=/www --with-apxs etc..") or build
the driver manager without libpthreads ("./configure --enable-threads=no etc.."). Thanks to Nick Gorham of Easysoft for the help.
The driver manager reads a configuration file called DSN (usually
/usr/local/etc/odbc.ini)
which lists Data Sources. This is a combination of several options as ipaddress, tcp port, default
db, user pass, but the most important is the path to the specific odbc driver which the
connection relies upon.
ODBC drivers for linux which connect to MS SQL:
Openlink Universal Data Access Drivers Multi-Tier Edition
This is a commericial bridged solution; the software is split in pieces.
On the client side (php+linux), you need to have installed php with openlink's
driver manager (l3kozzzz.taz) and the odbc driver (l3brzzzz.taz).
On the server machine (the one with ms sql) you must install a "request broker"
and a "database agent". The request broker will accept tcp connections and will
forward the requests to the database agent which will query directly into the
SQLServer database without going through the native SQLServer driver. This will
speed things up a little. However, an ODBC bridged solutions is constituted of
more layers of communication.
There is also the disadvantage in installing and configuring part of the
software on the Windows machine, which is not always desiderable.
Finally the most important thing is that this is a commercial solution!
The binaries are downloadable with a free, not expiring license, but with a
limit of 10 concurrent connections and two different concurrent client hosts.
Easysoft ODBC-ODBC Bridge 2000
This is another commercial bridged solution. The features are somewhat similar
to that of Openlink. It is suggested to use this driver with unixODBC
as driver manager. The software can be downloaded for free, with a not expiring license,
with a limit of only 1 connection !
Inline TDS ODBC driver
Inline TDS ODBC driver is developed by Inline Internet Systems (www.inline.net)
and can be downloaded from http://library.freeodbc.org download page.
This odbc driver is different from the others because it communicates directly
with the dbms engine, and is not a bridge of any sort.
This is a faster and lighter solution compared to the other two, and it is totally free.
The driver is currently released only in binary form, but Inline has the
intention to release the source too. The freetds library will be updated then.
I've tested the driver with unixODBC and it works perfectly, it even lets you
choose what tds version to use in the DSN file.
Installation instructions are covered in a text distributed with the driver.
An alternative partially-ODBC solution:
Finally, I should mention another software which will make you able to access a remote MS SQL
server from php: ODBC Socket Server by Team FXML downloadable from http://odbc.linuxave.net.
This solution is not totally odbc because it doesn't require installation of any odbc driver or driver
manager on the machine running php.
ODBC Socket Server is constituted of server-side software installed under Windows NT (or Win98,
but it is not considered the optimal operating environment) and client-side software
for COM, C++, Perl, Python and PHP.
The server-side software is a Windows NT service which uses an XML protocol to transfer data from the Windows
NT ODBC Socket Service to TCP/IP clients. It opens a tcp/ip socket and listens for incoming connections
on a specific port. The queries and the results will be exchanged using the XML protocol.
The client-side software in the case of PHP is a very small php class to connect to the socket server,
which basically defines a function ExecSQL() to establish a connection, send an SQL query and get the
result. However, you will need to parse the output result, which is XML encoded. To parse the output result, you could
use the PHP XML Parser module included in the latest versions of PHP.
ODBC Socket Server is an open source project licensed under the GPL. The software is well documented
and the installation very easy, including example code.
The End...
Several solutions have been introduced. The ones implementing the tds protocol have
been shown in more detail. In fact, these solutions are faster and easier to set up. Also, all
ODBC solutions are considered well documented, once you know the simple things explained here.
ODBC offers some advantages as code portability, but it shouldn't be the first choice for
someone who has to solve this problem rapidly and has no experience of odbc (especially
under unix).
--Alberto