PHP MSSQL varchar / nvarchar Limitation

PHP and MSSQL database API has some major issues. One of them is the very outdated PHP MSSQL driver. Because of this, one of the problem with MSSQL integration into PHP are long varchar / nvarchar fields.

If you have a field called Remarks, and has maximum character of 400, PHP would save the correct amount of data. However, when you retrieve the data again, it is truncated into 255 characters for ordinary text, and some 127 characters for foreign (especially asian) characters.

According t the PHP manual:

Note: Note to Windows Users
Due to a limitation in the underlying API used by PHP (MS DBLib C API), the length of VARCHAR fields is limited to 255. If you need to store more data, use a TEXT field instead.

You have to choose, whether to degrade performance by using text field type, or do some trick to handle such scenario.

According to this forum, you can actually retrieve the long data by splitting your select into several small substring and combine them in PHP.

$sql = "SELECT SUBSTRING(msg, 1, 255) AS msg_1, SUBSTRING(msg, 256, 255) AS msg_2, ....";

$msg = $row['msg_1'] . $row['msg_2'] . $row['msg_3'] ...

However, if performance is not an issue, you can change the field type in MSSQL to text.

Note: You can’t use text field in order by clause.

Leave a reply

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