PHP Tutorial: Pear DB, Microsoft SQL Server, and the nvarchar Data Type

Sorry I've been absent a few days. Stuff got hectic. Before I start, I want to give a shout out to Ian Maddox (a.k.a. @isnoop) for a great presentation to kick off the "Summer of OOP" at the Seattle PHP Meetup last week. And I wanted to let you know that I'll be running another design pattern short story tomorrow: "King Floyd and the Seventeen Princes: A Tale of the Observer Pattern".

Nopw to today's tip...

One of the projects making my life hectic was that project to redirect API calls into a database during some scheduled downtime and then pull them all out and run them against the API when it was back up. What one manager had predicted would be 20-40 API calls turned out to be 708. But when it came time to pull them out of the database, some were failing. Turned out that the column holding the data I would pass to the API was being truncated at 256 characters.

Back up to my earliest days of database design where I was taught that using a varchar data type was better than using a text data type because it used less memory and was more indexable. I should probably double check if that's still true, but that's what I read. So though I knew I might store more than 256 chars in that field, I was using Microsoft Sequel Server and I'd read that the nvarchar type could take up to 8,000 characters. So I went with a nvarchar data type with a max size of 2,048 characters for this column.

If I ran my stored process to retrieve data using Server Management Studio, everything came back in full. But in PHP, the results were getting truncated to 256 characters. I pinged the director of web development to see if he knew why and he said "no, but our database class is just a wrapper for Pear DB. Maybe you can start there." I did a little googling and found out a fun fact.

Pear DB treats nvarchar like varchar, and though Microsoft lets you store up to 8,000 characters, Pear DB limits what it returns to 256 characters. Now, with 708 rows already stored and this being a production server that I don't have any sort of admin access to, restructuring a live table was not going to be an efficient solution. But I kept googling and found out a fun trick... I could rewrite my SELECT query to recast the nvarchar data type as a text data type in the output.

So I went from:

SELECT id, method, data, processed FROM [table]

to...

SELECT id, method, CONVERT(TEXT,data) as data, processed FROM [table]

That simple. I updated my retrieval routine with the altered query and no more truncation.

Lesson of the day: Be careful with your MSSQL data types when working with Pear DB.