FreeTDS Development Group

["Joseph Geiser" <joe AT mail.paravisions.com>]

Just an FYI, we use both FreeTDS and PHP here, both under Solaris 8 (Intel 
version), as well as Linux.  The procedures is essentially the same for both 
(with Linux, we use the SRPM for both, and configure accordingly)

It's important to do this in the following order -- we found this out the 
hard way:

1.  Compile Apache first.  These are the configure options we use for both 
Apache 1.3 (Solaris) and Apache 2 (Linux/Fedora)

./configure --enable-module=so --disable-module=autoindex 
--enable-module=speling

make
make install

Then update the httpd.conf as needed and test the server itself.

Note that a LoadModule and/or AddType entry will be required for PHP, but 
added after PHP is up and compiled.  

2.  Compile FreeTDS, so that the protocol already exists when PHP is built.

./configure --prefix=/usr/local/freetds --enable-msdblib

make
make test
make install

Note that make test will show some errors - this is normal.

Update /usr/local/freetds/etc/freetds.conf file with the servers you need to 
access.  An example is:

[devserver]
        host = baloney.xxdev.com
        port = 1433
        tds version = 7.0

[phl_master1]
        host = salami.xxdev.com
        port = 48661
        tds version = 7.0

[phl_master2]
        host = brie.xxdev.com
        port = 48661
        tds version = 8.0

(devserver, phl_master1 and phl_master2 are the servernames to be used in 
connections, not the host name.  Also note that the default port for SQL 
Server is 1433 - for production servers, we use a different port number (and 
not the one above - that was changed for example only).

To test with tsql, after freetds.conf is updated, we use a somewhat simple 
query like this:

[root@gorganzola joe]# /usr/local/freetds/bin/tsql -S devserver -U sa
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Password:
1> use devclient
2> select orderstat as stat, count(orderstat) as numorders from order_master
3>   where orderstat in ('3','4','6','8')
4>   group by orderstat
5>   order by orderstat
6> go
stat    numorders
3       6695
4       32280
6       1150
1> exit
[root@gorganzola joe]#

(NOTE:  Yes, I know we can link tsql to /bin - this was done for clarity :)


3.  Last, compile PHP

If you are using a version of PHP prior to 4.3, you need to change some of 
the PHP code (This need not be done for PHP 4.3.7, which we use):

- Copy php_mssql.c to a backup file (to preserve the original)
#cp php_mssql.c php_mssql.c.bak

On line 2018, comment out the following:

/* datalen=Z_STRLEN_PP(var); */

And right after this line, add the following:

/* Char and Varchar limited to 255 characters */

If ((maxlen>0) && (maxlen<256))
   maxlen=maxlen;
else
{
   maxlen=255;
   datalen=255;
}

And a few lines after that, comment out this line:

/* datalen=-1; */

And add this one:

datalen = tds_get_size_by_type(type);

Save the file as php_mssql.c

Now time to compile:

- For APACHE 1.3 and assuming Apache lives in /usr/local/apache/:

./configure --with-apxs=/usr/local/apache/bin/apxs 
--with-config-file-path=/etc --enable-trans-sid 
--with-mssql=/usr/local/freetds 

- For APACHE 2

Same as above, except change the --with-apxs to 
--with-apxs2=/usr/local/apache2/bin/apxs

(add --enable-pgsql if you wish if using PostgreSQL, or other options as 
needed)

make
make install


Once PHP is compiled, open the httpd.conf file, and add the following in the 
appropriate spots for PHP support (if it's not there already):

AddType application/x-http-php .php

LoadModule php4-module

Note that the module may be in a different directory if using SRPMs for 
Apache2 - just ensure that Apache2's httpd.conf points to the appropriate 
directory and has rights to it.

4.  Restart httpd

You should now have PHP support, and MS-SQL support, and your calls should 
work just fine.  It at least has worked for us, and we're doing all sorts of 
things with it.

Note that if you are using Stored Procedures -- they DO work, but it requires 
PHP 4.3.7 or above, Apache2 is strongly recommended (Apache 1.3 - YMMV), and 
the latest FreeTDS (we use nightly snapshots).

VERSIONS BEING USED HERE QUITE SUCCESSFULLY (So I know they play nicely 
together:

Apache 2 (2.0.50)
FreeTDS (0.62.4)
PHP (4.3.7)

On the SQL Server box:

SQL Server 7.0 (Use TDS Version 7.0 in freetds.conf)
SQL Server 2000 (Use TDS Version 7.0 or 8.0 in freetds.conf - we stick with 
7.0 and have been safe).


Hope this helps the folks trying to get it working.  PHP and MSSQL have 
worked well together for us -- the above configuration seemed optimal for us 
and it should work for you as well.  The above is based on Solaris 8 (Intel 
version), RH8 and 9, and Fedora Core 2.  YMMV with other OSes.

Cheers,
Joe