The woes of PHP and MSSQL

My company is still using PHP 5.2. The reason we can’t upgrade to 5.3 is that the old MSSQL drivers are supported in 5.3. The reason we need the old MSSQL drivers is because the database class we use uses the methods provided by the old drivers.

I decided the best way to allow us to move to the newer drivers without massive code changes would be to create a new database class that utilizes the new drivers but also exposes methods with the same names as those in our current database class. The new class would emulate the functionality of the old class when these methods are invoked. Should be simple, right?

It started out being pretty simple. I decided to create a custom adapter Zend_Db_Adapter which extends Zend_Db_Adapter_Sqlsrv, overrides a few methods to add some additional functionality, and then implements the methods needed for backwards compatibility. I was able to knock this out in less than an hour. Our application would not work with the new class. I finally tracked down the source of the issues to the backwards compatible method sql_numrows() which returns the number of rows returned from the last select statement. The Zend adapter didn’t provide a method for this, so I had actually implemented this by directly invoking methods provided by the sqlsrv extension. After some digging, I found out that I have have to define some additional options when the query is made. I made the updates, and it worked!

However, our application was still having issues. After further investigation, I discovered that the 2.x version of the sqlsrv extension would throw an exception whenever the result set contained a column name that was more than 30 characters. It doesn’t just return the data with the column name truncated, but throws an exception and does not return any data. This isn’t an issue in the 3.x version of the extension, but the 3.x version only works with PHP >= 5.3. I have to verify that everything is working correctly with the new drivers against 5.2 before I can attempt an upgrade to 5.2. However, I also found out at that pdo_sqlsrv drivers don’t have this issue.

So, I created another custom adapter that uses the pdo_sqlsrv extension. This extension, it ends up, doesn’t provide any method for determining the number of rows returned. My first attempt to circumvent this limitation was to have my query method just do a “select @@rowcount” after any select query, and store the results. I found that sometimes that query would return 0, even when there were results. I wasn’t ever able to determine exactly what would cause that issue. Next, I tried creating a stored procedure that would take in a select statement, execute it, and return the number of rows in an out parameter. This doesn’t work because the entire result set must be consumed before the variable bound to the out parameter is updated.

So, at this point I really can only think of two options. The first is to build in some sort of parsing mechanism to identify select statements that contain column names that are more than 30 characters long. When encountered, it will alias these columns to a shorter name. The methods that return the results would then catch any of these aliased columns and replace the alias with the original column name before returning the result set. This would be used with the sqlsrv adapter, so I’d have access to the built-in sqlsrv function for determining the number of rows.

The problem with this approach is determining exactly when I need to make the substitution. If the statement contains any CTEs or inline-views, updating those column names will most likely break the outer queries. I also have to make sure that I don’t alias columns that have already been aliased.

The other option is to go ahead and load all of the results from a query into memory, so that I can determine the number of results and use that as the row count. I’d use this with the pdo_sqlsrv adapter, so I wouldn’t have to deal with the column name issue. This might be acceptable if our application had well written queries. However, it doesn’t. There is a good chance that a query is executed that has a result set of thousands of rows, and we only care about how many rows are returned, or we only need a couple of rows from the entire result set.

It seems like my best option is to use the pdo_sqlsrv adapter and load all of the results into memory. If we find scripts that are running out of a memory as a result, we can tackle those as needed. In the end, this is just a short term solution to allow us to upgrade to 5.3, where we can then switch over to the sqlsrv adapter that uses the 3.x drivers.