Wednesday, October 15, 2008

Microsoft SQL server and PHP

I'm a big fan of Pear::DB. (And, as an aside, I think the Pear decision to deprecate it was a bit strange.) But it didn't work for me for Microsoft SQL server (aka mssql), for one particular project (I'm fairly sure it has worked on other projects, so I think it was something specific to this environment). What did work was using the COM object.

COM objects from PHP are a bit of a black art, perhaps as most code examples seem to be in Visual Basic, which has a very different syntax. I found the following page useful, as it shows all three ways of running the same query for a mssql database:

I think I also used the user comments in the online PHP manual.

One thing the above URL does not show is error reporting. Here is the code I developed, which also takes care of not treating 5701 and 5703 as errors (congrats to Microsoft on another poorly thought out API!):

//$db is the return from the new COM() call.
// some Open or Execute command on $db...
if($errors->Count==0)return ""; //It worked
foreach($errors as $ix=>$error){
if($error->NativeError==5701 || $error->NativeError==5703)continue; //Information messages, not errors
$e.="Number={$error->Number}, Source={$error->Source}, SQLState={$error->SQLState}, NativeError={$error->NativeError}, Description={$error->Description}\n";
return $e;

No comments: