NOTE: There's a very nice and detailed FreeTDS Users Guide on installing, configuring and running FreeTDS provided on the FreeTDS Homepage.
This tutorial was written for a combination PHP 4.0.1pl2 and a pre-0.5.1 CVS version of FreeTDS. There are more recent versions of both PHP and FreeTDS - as far as I know, the steps in this tutorial should work for those, too.
Please send any comments, updates, suggestions, errata etc. to this page to michael@peceny.de, I would be glad to hear about them.
The native communications protocol of Sybase and Microsoft database servers is TDS (Tabular Data Stream) - for example, the MS SQL Server ODBC driver uses this protocol to communicate with the database server. FreeTDS is a free implementation of TDS, with the project's homepage being www.freetds.org.
Using the FreeTDS libraries, PHP's mssql_xxx() functions can be used to directly access an MS SQL Server or Sybase database server from a Linux machine.
If you use Windows as your PHP platform, you will not need FreeTDS. PHP 4.0.1pl2 for Windows already comes with out-of-the-box working mssql_xxx() functions. Read the annotations to the PHP manual section on Microsoft SQL Server functions to find out how to enable it.
Please note that FreeTDS is a project under development, and as such still has its fair share of bugs and problems. It should be stable enough for use in quite many production/application environments already, though. An alternative might be using the Sybase libraries from www.sybase.com/products/databaseservers/linux/ / linux.sybase.com (which I haven't tried).
Important: The following step-by-step instructions describe how I got PHP w/ FreeTDS to work for me. You might run into problems that I did not encounter (and vice versa, I could have experienced trouble where none might show up for you). A good resource for knowledge is the FreeTDS mailing list (see the FreeTDS Homepage Contact section on how to subscribe).
I assume that PHP has been already been installed & configured and runs fine. The following step-by-step howto describes how to add FreeTDS support to PHP.
Setup for the example installation (many other versions, especially newer ones, should work fine, too):
SuSE Linux 6.3 (glibc2.1/libc6.1, Kernel 2.2.13 or was that 2.2.9?) Apache 1.3.9 PHP 4.0.1pl2 (loaded as mod_php4 by the Apache)
You also might have to state the location of freetds: --prefix=/usr/local/freetds
Note: The FreeTDS FAQ seems to suggest that --with-tdsver=7.0 might be a good choice
for connecting to an MS SQL Server 7. Actually, many people report problems for TDSVER=70,
and I didn't get it to work, either. Using TDSVER=42 should work just fine under most
circumstances.
Other configure options would be --enable-msdblib (it appears to work fine for
me without that one - I would be interested if anyone could enlighten me as to when this is
actually needed) and --enable-dbmfix (read the
FAQ on that one! It didn't make
any difference for me, either).
The SYBASE environment variable will need to be set as well as LD_LIBRARY_PATH extended.
For a bash shell, this looks as follows:
> SYBASE=/usr/local/freetds
> LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib
> export SYBASE LD_LIBRARY_PATH
Example: for PHP 4.0.1pl2, this might look like this:
> tar -xzvf php-4.0.1.tar.gz
> patch -p0 < php-4.0.1-4.0.1pl2.diff
For example, my options are as follows: --with-mysql --with-apxs --disable-debug --with-sybase=/usr/local/freetds
You also might have to state the location of php, e.g. --prefix=/usr/local/php
If apxs cannot be found (this usually happens when trying to compile as another user
than root), the location of the apxs executable has to be given, e.g.
Notes:
--with-sybase-ct=/usr/local/freetds. I could compile and
run it that way (note that compilation might require you to remove references to libraries
you don't have from config_vars.mk and Zend/Makefile, otherwise the linker will complain.
For me, those were -lsybtcl, -lintl, -lcomn and -lcs). Accessing the database server didn't
work, though (this might be different with the newest FreeTDS CVS version and PHP development
snapshot), so I stuck with --with-sybase (anyone enlighten me about the
difference?).
Note: Some people, including me, have had trouble with PHP 4.0.2. The file ext/sybase/php_sybase_db.c (in the PHP source code) references DBSETLCHARSET, which is part of Sybase's library but not FreeTDS (as of Sep 21, 2000), resulting in an unreferenced symbol error when trying to start Apache with lib_php4 loaded (compilation works fine). As a workaround, the line in question in above file can be commented out. This should work out fine for most, with the resulting loss of functionality being relevant to only a few rare applications.
Note: Installing PHP as an Apache module will attempt to modify your httpd.conf. If you already have PHP 4 configured in your httpd.conf and don't want the PHP installation process to mess around with it, back it up before installation and restore the backup afterwards.
On a SuSE system, this will look like this:
root@mymachine > rcapache restart
Note: The environment of the Apache server needs the variables SYBASE and LD_LIBRARY_PATH set as above for FreeTDS to function. If you startup Apache automatically at system bootup you will have to do so in some appropriate place. On a SuSE distribution, for example, you could do so in /sbin/init.d/apache (which is, btw, also the script executed by the above Apache restart command).
You will now be able to use PHP's mssql_xxx() functions (which actually are just aliases to the sybase_xxx() functions, so those work as well) to access an MS SQL Server.
To access an MS SQL Server (or a Sybase server), you will have to enter its basic connection parameters in /usr/local/freetds/interfaces. For example, we shall assume a database server at IP 127.0.0.2 listening on port 1433 (which is the default port used by MS SQL Servers, so if in doubt, use that one) which we will call MYSERVER:
MYSERVER
query tcp ether 127.0.0.2 1433
master tcp ether 127.0.0.2 1433
Note: MS SQL Server 2000 seems to use port 1067, at least if there is an MS SQL Server 7 running simultaneously on the same machine. So, if you seem to be unable to connect to MS SQL Server 2000, you might want to check the port [snatched from the FreeTDS mailing list].
The following PHP code now can be used to query MYSERVER (also, a more complete example can be found in an annotation by wruslan@dectra.com.my from 16-Jul-2000 to the PHP Manual section on Microsoft SQL Server functions):
// connect to database server
$db_conn = mssql_connect("MYSERVER","<user>","<password>")
or die( "<strong>ERROR: Connection to MYSERVER failed</strong>" );
// select database - only if we want to query another database than the default one
mssql_select_db( "mydatabase", $db_conn )
or die( "<strong>ERROR: Selecting database failed</strong>" );
// query the database
$query_result = mssql_query( "SELECT * FROM foo", $db_conn )
or die( "<strong>ERROR: Query failed</strong>" );
/*
* You can now access the result data by using the appropriate mssql_xxx() functions
* on $query_result. See the PHP manual section on Microsoft SQL Server functions
* for documentation on how to do this.
*/
mssql_free_result( $query_result ); // unnecessary in PHP 4
mssql_close( $db_conn ); // unnecessary in PHP 4
You might also consider using some wrapper around the database access code (example MS SQL wrapper, see here for motivation and usage instructions.
Michael Peceny
Apr 27, 2002