Open Source and Closed Source (SOLUTION!)

In my previous entry, I stated that I had an issue with PHP connecting to and talking to MSSQL (specifically SQL Server 2000). And after reading countless blog entry's, articles, white papers and just general comments and notes, taking in and absorbing all that I read, I finally managed to get it working!

I originally started with the 'ADOdb Database Abstraction Library for PHP', and ended up playing with that for about four hours, and the errors I received where quite cryptic and didn't really help much. Part way into this 'adventure', I came across some 'built in' PHP function, specifically for MSSQL (they mostly started with mssql_, more info at php.net), but PHP has to be compiled with the switch --with-mssql. Since I'm running PHP on the work server which is Fedora Core 5, the switch was enabled at compile/install time. I could however re-compile PHP with the switch, but since this is a production server, and I don't have a cluster of servers I can slowly deploy this onto one at a time, I can't really afford to take the server off-line, even if it's just for a few minutes, so I'm SOL.

Then the next day I decided to refine my 'search' (at good ol' google.com), and after some reading, I found an RPM package that can be installed AFTER PHP has already been installed, and it will add the MSSQL functionality to PHP, without having to recompile PHP! So after searching for that, I find that the last version they have of the RPM is for Fedora Core 4. Instead, I'm pointed to what's known as unixODBC, which is installed in Fedora Core 5 by default. This is beginning to look promising!

I then did a search for 'how to configure unixODBC' and came up with this result. Yes it's for a different version of MSSQL Server, a different operating system AND a completely different language! But the section on setting up unixODBC is the same no matter the OS, language or version of MSSQL.

The guide required that I install freeTDS, so a quick yum install freetds and freeTDS is installed, simple as that. Then the guide had me configure the freeTDS conf files (for me, located at /etc/freetds.conf, it most likely wont vary OS to OS), then the guide had me "test" my freeTDS conf file, and it worked. This means progress! FreeTDS can now talk to (and retrieve data from the MSSQL server). The guide then had me configure the ODBC connection (again, for me located at /etc/odbc.ini and /etc/odbcinst.ini). After that, the guide had me "test" the ODBC connection, and after fixing a type-o in my odbc.ini file, everything worked!

Now to figure out how to get PHP to use the ODBC connection to talk to the MSSQL Database. After following the documentation at the ADOdb site, I had a basic script with those cryptic error messages. And even after getting FreeTDS and unixODBC working with the MSSQL Database (across the network even!), I was dumbfounded as to why the ADOdb wasn't working. So after doing some more searching at google, I came across this site. Again, not the same OS, but problem it was address was EXACTLY the same as mine. So I just skipped to the code section and voilà! I have a working PHP script that talks to an MSSQL Database!

And for those of you who don't care about reading and just want to see what I did, and/or have, here:
/etc/odbc.ini

[MSSQLDSN1]
Driver = FreeTDS
Description = ODBC Connection via FreeTDS
Trace = no
Server =
Port = 1433
TDS Version = 8.0
Database = < database_name >


/etc/odbcinst.ini

[FreeTDS]
Description = ODBC Connection via FreeTDS
Driver = /usr/lib/libtdsodbc.so.0
FileUsage = 1


/etc/freetds.conf

[MSSQL]
host = < ip_address_of_your_mssql_server >
port = 1433
tds version = 8.0


PHP script

<?php
$mssql_server = '< ip_address_of_your_mssql_server >';
$mssql_user = '< your_database_username >';
$mssql_password = '< your_database_password >';
$mssql_database = '< database_name >';
$mssql_dbdriver = "odbc";
$mssql_dsn = 'MSSQLDSN1';
include('adodb.inc.php');
$mssqldb =& ADONewConnection($mssql_dbdriver); # eg 'mysql' or 'postgres'
$mssqldb->debug = false;
$mssqldb->SetFetchMode(ADODB_FETCH_ASSOC);
$mssqldb->Connect($mssql_dsn, $mssql_user, $mssql_password);
$mssqlrs = $mssqldb->GetArray('SELECT TOP 10 FROM ');
//do what you want with the data here
$mssqldb->close();
?>


Now I have a PHP script which remotely grabs all the data I require, and stores it in a MySQL Database (so I can use the existing PHP code to run all the stats), and this script is now called nightly via a cron job.

I hope this will help someone NOT go through as big of a headache as I did.