The sorry saga of varchar(Max) and ODBC in PHP

A project I’m currently working on uses SQL Server 2005 with PHP, the database being accessed via PHP’s ODBC library.

I have to admit that I love my Stored Procedures, and rather than use ‘inline’ SQL SELECT statements in my data classes I’ve usually used Stored Proceduers when talking to SQL databases in the past.

However….believe it or not this is the first PHP / SQL Server project I’ve ever done that used ODBC and also featured VARCHAR(Max) or Text fields….

I found that no matter what I tried in terms of code, I couldn’t actually retrieve any data from Text or Varchar(Max) fields.  Varchar(n) fields were fine – however the application concerned requires the ability to store potentially open ended text data in database records.

After trying numerous approaches, I did find that inline SELECT statements passed to the ODBC functions worked perfectly.  So, my data classes for tables and views that use Text or Varchar(Max) fields now use SELECT statements for returning data.

I’m not 100% happy with this, but it works, and Google hasn’t revealed any suggestions that I haven’t yet tried.  INSERT, UPDATE and DELETE still work fine through Stored Procedures, so data integrity won’t be an issue.

I’ll just keep an eye open for solutions – anyone has one, please drop me a line!

Leave a Reply

Your email address will not be published. Required fields are marked *