sqlsrv - How to install sqlsrv and fix MSSQL/php 255 multibyte issue

Tuesday, 3 March 2009

For anyone looking for a solution to multi-byte characters and getting more than 255 characters from MSSQL and php and you don't want to read the background details, just skip down to the line that starts with "OK now on to the implementation..."

Well it's been a long time in between posts. Especially with our tight release cycle and family commitments.

Since I started working here we've had a whole bunch of issues trying to get multi-byte characters working with MSSQL and php.

The main issues with php and MSSQL are:
* Getting more than 255 characters from a varchar / nvarchar field.
* Getting nvarchar(MAX) and ntext fields to work at all
* Storing and retrieving multi-byte characters in combination with the above. (Using CAST or CONVERT to string seems to kill the multi-byte characters)
* Having to specify a collate when retrieving multi-byte characters
* When specifying a collate, can only display one type of characters e.g. can only show Thai characters and not Thai + Chinese characters in one field (believe me there are practical uses for this esp. in places like Malaysia & Singapore).

I won't go through all the dramas we've been having in detail as I'm sure if you are interested in this post you more than likely have gone through some of them yourself.

It has been suggested that http://www.freetds.org/ works. I am only mentioning it so that others who have had success with it won't say "Why don't you use freeTDS". Also note that we are using Windows servers here and I couldn't find any good documentation or solid examples of getting it to work on a Windows server. If you have any useful links then please add them in the comments section for others to try.

Our application is designed to run around the world and we have localized versions in many different countries. Part of our software is entering in details that are often long, descriptive and more than your average 255 characters so it was important that we got this working.

If you've read my other posts you will know that I am working on converting this application from asp to php. As asp and MSSQL play nice together there was no hassles retrieving these fields before, but since changing over to php we've had this issue and I don't want to keep half of the app in php and half in asp.

I'm hoping this solution is easy to implement so others that are having the same frustrations can get this solved in a matter of minutes. If you search around the web the most common answer is to use CAST or CONVERT which is an easier solution than mine, but will work fine only if you are using English.

OK now on to the implementation...

Firstly, before we go any further, I mention in this post installing software and drivers that come from third party sites. Just a disclaimer: Read all licenses, back up everything and don't install anything without consideration, don't do it just because I said so. Even in the best of conditions your machine may decide it's time to stop working. Do everything at your own risk.

My solution is using Microsoft's SQL Server 2005 Driver for PHP. The current link to the documentation is here. The link to download the driver is here.

The very first step is changing you php ini, so that this will allow you to retrieve the larger data from the database. It has been suggested that if these are left as the default they may result in the result being truncated:
mssql.textlimit = 65536
mssql.textsize = 65536
There is no recommended size here so you can play around with values, but just make sure they are not truncating by default.

Next follow the instructions provided to load the sqlsrv dll driver which is in the documentation under "Loading the SQL Server 2005 Driver for PHP".

It doesn't mention it anywhere, but if the driver has loaded correctly you should be able to do a search on your phpinfo page for the text sqlsrv and find something, then you will know that it has loaded.

Another thing to mention is that you may have troubles with connecting to the MSSQL database even after all this has done. The error I'm referring to is:
    [0] => Array
[0] => IM002
[1] => 0
[code] => 0
[2] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
[message] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

It took me a lot of digging round on Google to find out how to fix this. Especially as it already worked for me on our dev server and only decided to drive me insane on the day we pushed our BETA release <sigh>.

Here is a link to the post where someone else was having similar problems and after some painstaking reading through I managed to find the solution. The main problem is this: "The SQL Server Driver for PHP requires the SQL Server 2005 version of the SQL Native Client"

To fix IM002, follow this link here. If that link is no longer current search for something similar to "Feature Pack for Microsoft SQL Server 2005", there is also one for 2008. Search for the download link on the page for sqlncli.msi. Download and (at your own risk! back everything up!) install. That should fix this problem and you should be able to connect without problems if IIS and you MSSQL server are configured correctly.

Ok, now all the major dramas should be out of the way. You should now be able to, using the API provided in the documentation do everything you need to without issue. If you are still having issues let me know in the comments and I will see if I can point you in the right direction. Although I won't be able to help you with configuring your Windows or MSSQL database servers, these should be configured correctly before going anywhere with the sqlsrv driver.

Next I would like to go over my implementation of sqlsrv and the abstraction class I built to make things easier to use in php. I haven't benchmarked this against anything so please do your own testing for suitability before just plugging it in to your system, I'm providing this as an example only. I appreciate all feedback and positive criticism. Please let me know if there are any improvements you would make so we can all learn.

You can view / download the source here. Just remember to replace the <pre> tags with php tags before use.

One thing to note about the sqlsrv driver is that it retrieves dates as objects when calling sqlsrv_fetch_array which makes it incredibly slow. The way that I have gotten around this is retrieving each of the fields for every row as sqlsrv_get_field($stmt, $key, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR)), this seems to work a hell of a lot faster.

The two main functions are fetchrows and fetchutf8. The fetchutf8 function differs in that it takes the $fields variable which are the variables that are multibyte characters that need to be retrieved as utf-8, this requires an extra check so I split the functions for speed purposes.

I hope this is useful and saves people a lot of time and frustration. If you have ideas for improvements please share with everyone.
blog comments powered by Disqus