[freetds] Re: PHP & FreeTDS
joe at mail.paravisions.com
Mon Aug 16 07:40:00 EDT 2004
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
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
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:
host = baloney.xxdev.com
port = 1433
tds version = 7.0
host = salami.xxdev.com
port = 48661
tds version = 7.0
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 at gorganzola joe]# /usr/local/freetds/bin/tsql -S devserver -U sa
locale is "en_US.UTF-8"
locale charset is "UTF-8"
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
[root at 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))
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)
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
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)
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.
More information about the FreeTDS