More on PHP and MSSQL

So, I finally implemented by own Zend_Db_Adapter (I subclassed Zend_Db_Adapter_Sqlsrv) that contained additional methods to make it backwards compatible with our legacy database class. While we will utilize the Zend_Db_Adapter methods going forward, there is just way too much legacy code to attempt any sort of PHP 5.3 upgrade without having the backwards compatible piece in place. So, after I get everything set up, the first issue I encounter is related to the code that sets up various session attributes when a user logs in.

The value that would get returned from the database query was 0 (zero). In the 5.2 environment, the $id == DUMMY check would resolve to false, and go into the “do that” block. However, in the 5.3 environment, that same code resolved to true, and went into the “do this” block. I looked everywhere to see if I could find something indicating a change in comparison logic between 5.2 and 5.3. I thought maybe something changed in relation to type juggling. Nothing. I checked on IRC, where it was confirmed that nothing had changed. I looked for a possible INI setting that had it’s default value changed. Nothing. I ran the code through the debugger multiple times, and while it confirmed the behavior I was seeing, I couldn’t figure it out. Finally I realized that in the 5.2 environment, $id was “0” while on the 5.3 environment, $id was 0.

In PHP, “0” == “string” resolves to false, while 0 == “string” resolves to true. So, why was $id a string in the 5.2 environment and an integer in the 5.3 environment? Database libraries always return values as strings, so I figured it had to be something built into Zend_Db_Adapter to “help out” and convert to proper data types. Didn’t find anything. Finally I found where the sqlsrv libraries actually do the type conversion. While you can specify which type to return when fetching a single value, there is no way to disable the type conversions in any of the other fetch methods.

Not knowing how often code like that above might exist, the first thing I attempted was to update the legacy methods for fetching data (sql_fetchrow and sql_fetchrowset) to convert everything back to a string. This totally killed performance. I wasn’t surprised as a good deal of our legacy code contains “select *” style queries, which then fetch the entire result set (both numeric and associative keys) and then only use a single value from the first row returned. If a little more discipline had been practiced when that code was originally written, the “convert back to string” idea might have worked. But I digress…

Finally, I decided to just update the above method to use a === compare. The data type of returned data will just remain at the top of the list of things to check if we encounter additional errors. So far, we haven’t found anything else.

My next post will deal with the retrieval of identity column values when using the legacy methods for inserting data.

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.