My Notes

Adding mssql to Filemaker PHP

posted 29 Jul 2010, 20:05 by Leonard Wee   [ updated 22 Feb 2011, 14:43 ]

At work, I had to get data from a Microsoft SQL Server 2005 database to a Filemaker CWP website running on OSX. For once, Google did not help much as it was a relatively obscure scenario. I ended up compiling FreeTDS with the mssql PHP extension from whatever notes I could find online. I post this as a note for myself and to save time for anyone who happens to be in a similar situation.

The versions I worked with are:
OSX: 10.6.1
Xcode: 3.2.1
PHP: 5.2.9 (installed by Filemaker Server 10)
FreeTDS: 0.82

The difficulties I had was because the Filemaker CWP website was not running off the default OSX install of PHP but Filemaker's so I had to tweak locations and I am not even 100% sure I got it right. But the mssql functions seem to be working OK so YMMV :-)

BTW, the correct solution is to buy a solution from these guys. But if you're feeling masochistic, carry on reading.



Install xcode (to get gcc to compile sources)

Download FreeTDS and install it:
./configure --prefix=/usr/local/freetds --disable-odbc --disable-libiconv --enable-msdblib
make clean
make
make install


Download the PHP sourcecode and compile it (LIBS=-lresolv needed to compile v5.2.9). 
cd php-5.2.9
LIBS=-lresolv ./configure --with-iconv=shared,/opt/local
make


Prepare environment for the mssql.so extension
replace the php folder (make a backup of the old one) in
/usr/include
with
/Library/FileMaker Server/Web Publishing/publishing-engine/php/include

Make a copy of the folder:
/Library/FileMaker Server/Web Publishing/publishing-engine/php
to a location like
/temp/php
This is because the phpize script does not work with spaces

Copy "/usr/bin/sed" to "/temp/php/bin/sed"

In usr/bin/phpize, change the following lines near the top (bascially replace "usr" with the copy above):

prefix='/usr'
to
prefix='/temp/php'


Build the mssql.so extension
From the php directory made above
cd ext/mssql
phpize
./configure --with-mssql=/usr/local/freetds
make


Install the compiled extension
cp modules/mssql.so /usr/lib/php/extensions/no-debug-non-zts-20060613

Edit the php.ini file in "/Library/FileMaker Server/Web Publishing/publishing-engine/php/lib/php.ini"
Add the line "extension=mssql.so"


Add the connection details to /usr/local/freetds/etc/freetds.conf
[example]
example.com
port = 1433
tds version = 7.0

Undo environment Prep
replace "/usr/include/php" back to the original
You can delete /temp/php as well

Restart Apache
sudo /usr/sbin/apachectl restart


Troubleshooting Tips
You can check errors by adding (uncomment) this also in the freetds.conf
dump file = /tmp/freetds.log
dump file append = yes

Check for PHP and errors (while trying to load module etc) in the apache logs:
/var/log/apache2/error_log

"Application > Utilities > Console" is the easiest place to monitor the above logs. You can add a link to the tds logs there:
cd /Library/logs
ln -s /tmp/freetds.log
(Instead of linking, you could dump the tds logs in /Library/logs to being with)

Another log to check is:
Filemaker Server Admin Console > Log Viewer


1-1 of 1