As pear (pun intended) usual if I have what I believe is a useful solution to something I’ve spent an eternity trying to fix in my code I will try put it up on the old blog for the benefit of others. The problem I was having for a large part of yesterday and earlier on this morning (Tuesday the 12th) related to the use of a PEAR based Database Abstraction Layer (DAL) module called DB. For those that don’t know a DAL allows a developer to call generic database access code, which then in turn calls DB provider specific code. The advantage of this is that the developer can change the underlying database system by changing a single line of code in a connection string as opposed to all DB access logic in his or her code.
The Database Abstraction Layer I was using: DB is actually depreciated but I was using it for legacy purposes as most of our existing PHP apps use it. The thing to remember with DALs is that ‘under the hood’ they will eventually call DB provider specific code so problems with DB, any other PEAR DAL or indeed any DAL in general may not be caused by the DAL itself but in fact caused by the underlying DB provider specific code.
My problem in this case was that I just seemed to keep getting connection errors when I called the ‘connect’ function in DB. The code I used was similar to:
require_once(’DB.php’); //makes the DB extension available to my code
//connection string for SQL Server database
$db = DB::connect(”mssql://username:PaSsWoRd@dbhost/dbname”);
if(DB::iserror($db))
{
die($db->getMessage());
}
and the error I got was DB Error: connect failed. As you can see my underlying database is SQL Server as indicated by ‘mssql’ in the above code.
I’m not too sure about the more recent releases of DB (I was using one from 2003) but it seemed DB was not very useful from an error debugging point of view as it didn’t ‘bubble up’ all error messages and only spit out something very generic and very useless.
The thought struck me that perhaps the underlying PHP MS SQL function library was not enabled in the PHP configuration file. This turned out to be the case. Enabling it was as simple as adding a line or two into the PHP.ini file:
[PHP_MSSQL]
extension=php_mssql.dll
If you had of called
$db = mssql_connect(”dbhost”,”username”,”PaSsWoRd”);
directly in your code, which the above PEAR DB code eventually did you would have got a much more helpful error like the following:
PHP Fatal error: Call to undefined function mssql_connect() in…
which you would (hopefully) immediately diagnose as being related to the availability of the PHP mssql library itself and not DAL related.
After enabling the extension, I was still getting the DB Error: connect failed generic error from PEAR DB so I decided to work directly with the mssql_connect function to see if again it was a SQL Server issue. When I called mssql_connect I still couldn’t get a connection and got the error below so it was obviously not PEAR DB playing up.
PHP Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: servername
It turns out that my connection string was 100% correct however the version of the ntwdblib.dll file on my PHP box was not compatibile with certain recent versions of SQL Server. According to the PHP website ntwdblib.dll is required for the PHP MSSQL extension to work:
The extension requires the MS SQL Client Tools to be installed on the system where PHP is installed. The Client Tools can be installed from the MS SQL Server CD or by copying ntwdblib.dll from \winnt\system32 on the server to \winnt\system32 on the PHP box. Copying ntwdblib.dll will only provide access through named pipes. Configuration of the client will require installation of all the tools.
It seems that for whatever reason the very latest installs of PHP include a version of ntwdblib.dll that will not work with SQL Server 2003, SQL Server 2005 and as far as I’m aware SQL Server Express. The version of ntwdblib installed is likely to be 2000.2.8.0 when what you need to have to talk to recent versions of SQL Server is 2000.80.194.0. This file can actually be present in locations other than winnt\system32 depending on your platform and installation setup so I suggest you do a search for it, check the version and if it doesn’t end it 80.194.0 download the latest version from the UserScape.com site and use it to overwrite the existing version. In my case I installed PHP as a CGI on Windows Server 2003 so the file was present directly in the PHP folder. When I updated it and tried my code again everything worked fine, including the original PHP DAL DB:connect call.
In the end it turned out my problems were nothing to do with the PEAR DB module but were related to PHPs SQL Server functions. If your still having problems connecting to SQL Server from PHP I suggest you visit the relevant PHP page located at http://ie2.php.net/function.mssql-connect which contains a lot of user contributed information about ntwdblib.dll and other issues which may be causing your problems and associated pain.